Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Converting Excel formulae into Matlab code

  1. Apr 9, 2015 #1
    Hi everyone,

    I will start off by saying that I am a complete novice in Matlab and prefer to use Excel however the data that I have requires more computing power than Excel can provide.

    I have 16 sets of data with over 70,000 rows and 9 columns. The first column is a time series separated into 15 minute intervals, the rest are moisture readings from various depths. I also have rainfall data in a separate table that I have converted to 15 minute intervals for ease of use.

    My task is to find the rainfall events that fit a certain criteria which can be used to filter the table into events and non-events for data analysis. I have used Excel and created the following formula for the rainfall data
    =IF((SUM(adjacent cell+3):(adjacent cell-48)))>=1,1,0)
    which basically checks 12 hours previous and 45 minutes forward of the adjacent cell to see if the sum of rainfall is greater than or equal to 1mm. I then use the filter function in Excel to grab the event dates and transfer this to the moisture data where I use
    =IF(COUNTIF(event dates,moisture date),1,0)
    This lets me separate the moisture data with the filter function.

    Is there a way to perform these functions in Matlab? I am thinking it would be using 'if' functions, unfortunately I am unsure about using the function and previous filtering attempts without using the function have returned single column vectors without the rest of the soil moisture results.

    Any assistance will be greatly appreciated and if any further information is required, please do not hesitate to ask.

    Kind regards,
  2. jcsd
  3. Apr 9, 2015 #2

    Simon Bridge

    User Avatar
    Science Advisor
    Homework Helper

    Welcome to PF;
    Since you have no matlab finess, your best bet is to step through each element using a for loop and just run the same algorithm.
    In matlab a cell is called an element and it is indexed by 2 numbers where excel uses a number and a letter.

    You'd store the data in a matrix D say, which will be a 16x70000, you access a specific element by D(n,m)
    So D(2,100) would be like cell B:100. The adjacent cell would be C:100 right? So that would be D(3,100).
    You'd have to use a for loop to go step-by-step through each cell, and use if-then statements to test each one.
    You'll also need the sum() function - within which you can specify a range of elements (cells) to add up.

    There is no easy way to tell you how to figure it out - you will have to use a matlab handbook and maybe practice on smaller data sets.
  4. Apr 9, 2015 #3
    Hi Simon,

    Thanks for the input and the advice, I was thinking it was along those lines and will have a go at trying smaller data sets with different codes and see what happens.

    Many thanks,
Know someone interested in this topic? Share this thread via Reddit, Google+, Twitter, or Facebook

Similar Discussions: Converting Excel formulae into Matlab code
  1. Matlab codes (Replies: 2)

  2. Matlab code (Replies: 0)

  3. Matlab Code (Replies: 2)