Search code examples
excelexcel-formulamaxpivot-tableworksheet-function

Maximum values per day from data with different dates

I have data taken at different times on different days, for example: 

dateTimeRead(YYYY-MM-DD HH-mm-ss)   rain_value(mm)  air_pressure(hPa)
1/2/2015 0:00                       0               941.5675
1/2/2015 0:15                       0               941.4625
1/2/2015 0:30                       0               941.3
1/2/2015 0:45                       0               941.2725
1/2/2015 1:00                       0.2             941.12
1/2/2015 1:15                       0               940.8625
1/2/2015 1:30                       0               940.7575
1/2/2015 1:45                       0               940.6075
1/2/2015 2:00                       0               940.545
1/2/2015 2:15                       0               940.27
1/2/2015 2:30                       0               940.2125
1/2/2015 16:15                      0               940.625
1/2/2015 16:30                      0               940.69
1/2/2015 16:45                      0               940.6175
1/2/2015 17:00                      0               940.635
1/2/2015 19:00                      0               941.9975
1/2/2015 20:45                      0               942.7925
1/2/2015 21:00                      0               942.745
1/2/2015 21:15                      0               942.6325
1/2/2015 21:30                      0               942.735
1/2/2015 21:45                      0               942.765
1/2/2015 22:00                      0               7/30/1902
1/3/2015 2:30                       0               941.1275
1/3/2015 2:45                       0               941.125
1/3/2015 3:00                       0               940.955
1/3/2015 3:15                       0               941.035

There are dates with missing time stamps.

From these readings how may I extract the maximum values by day for rain_value(mm)?

Solution

  • There is a fairly standard array formula style to provide a pseudo-MAXIF function but I prefer to use INDEX and enter it as a standard formula.

          Maximum Rainfall Daily

    With the date to be determined in F3, the formula in G3 is,

    =MAX(INDEX(($A$2:$A$999>=$F3)*($A$2:$A$999<(F3+1))*$B$2:$B$999, , ))

    A CSE array formula for the same thing would be something like,

    =MAX(IF($A$2:$A$999>=$F3, IF($A$2:$A$999<$F3+1, $B$2:$B$900)))

    Array formulas need to be finalized with Ctrl+Shift+Enter↵.