Calculate if statement based on date

Hello,

I have a spreadsheet. In column A, are the dates. In column E, I have the precipitation values.

I need to sum certain precipitation values (of Column E) together per date, based on Latitude and Longitude, columns F and G respectively. Then, I need to designate either S1, S2, ...

For instance, I need to calculate the sum of precipitation values for S12 for each date. S12 is based on the following Latitude and Longitude values:

40.6323 -74.1923

40.5935 -74.1066

40.5804, 74.175

40.561, -74.1322

40.5478, -74.2005

40.5284, -74.1577

40.5153, -74.226

If any one could help me with this, I would greatly appreciate it.

I've attached a spreadsheet that covers about 20 days, since my file was too big (its 3,650 days).

Thanks,

Candace

[darova]

I don't understand the question. Can you explain more about condition? HOw do you want to sum?

[CMatlabWold]

Sure, for each corresponding latitude and longitude coordinates, there is a precipitation amount (in column E). I am trying to get the sum of the precipitation amounts, based on those latitude and longitude coordinates

For a location, such as S12, for January 1, 2010, there are seven longitude and latitude coordinates in the area. In column E, the precipitation amounts:

E F G

0.0580 40.6323 -74.1923

0.0743 40.5935 -74.1066

0.0881 40.5804, 74.175

0.0660 40.561, -74.1322

0.0579 40.5478, -74.2005

0.103 40.5284, -74.1577

0.0269 40.5153, -74.226

I need a daily sum of those amounts: 0.0580 + 0.0743 + ... 0.0269.

Then, I have to do the same for another location. For instance, I have S5, where the latitude and longitude coordinates:

40.626 -74.0809

40.6129 -74.1494

Daily sums of the corresponding precipitation amounts of column E...

Thanks,

C

[CMatlabWold]

The longitude latitude coordinates for each Sn: S1, S2, etc... remain the same and unchanged. I just need to tabulate the daily precipitation amounts accordingly for 10 years.

[darova]

What is the criteria of group? I don't understand how to separate values

Answer 1

[Star Strider]

Try this:

T1 = readtable('Precip.Sample.xlsx');
TT1 = table2timetable(T1);
TTRP = retime(TT1(:,1:4), 'daily', 'sum');
TTRL = retime(TT1(:,5:6), 'daily', 'mean');
TTR = innerjoin(TTRP,TTRL)

producing:

TTR =

  36×6 timetable

       Date        Precip (mm)    PRECIP (in)    Weights    Final PRECIP    Latitude    Longitude
    ___________    ___________    ___________    _______    ____________    ________    _________

    01-Jan-2010       846.6          33.331       55.27         23.292       40.671      -73.985 
    02-Jan-2010       184.5          7.2638       55.27         3.9852       40.671      -73.985 
    03-Jan-2010        65.8          2.5906       55.27          1.471       40.671      -73.985 
    04-Jan-2010         8.2         0.32283       55.27        0.23122       40.671      -73.985 
    05-Jan-2010           0               0       55.27              0       40.671      -73.985 
    06-Jan-2010           0               0       55.27              0       40.671      -73.985 
    07-Jan-2010           0               0       55.27              0       40.671      -73.985 
    08-Jan-2010        59.1          2.3268       55.27         1.6153       40.671      -73.985 
                                              ... CONTINUED ...

.

[CMatlabWold]

Hi, thanks for the code.

But, what I am trying to do is add the final precip amounts based on latitude and longitude coordinates.

For instance, in the case of S12, which is comprised of these coordinates:

Latitude Longitude

40.6323 -74.1923

40.5935 -74.1066

40.5804, 74.175

40.561, -74.1322

40.5478, -74.2005

40.5284, -74.1577

40.5153, -74.226

The corresponding Final Precip amounts for January 1, 2010 are listed in column E (If I filter the spreadsheet tab, I get these values on column E:

E F G

0.0580 40.6323 -74.1923

0.0743 40.5935 -74.1066

0.0881 40.5804, 74.175

0.0660 40.561, -74.1322

0.0579 40.5478, -74.2005

0.103 40.5284, -74.1577

0.0269 40.5153, -74.226

And, I just want to have Matlab sum these amounts per day.

For, instance, the sum for January 1, 2010 is 0.4742 inches