Repeating max
Posted by J. Glasgow on December 20, 2000 9:21 AM
I have hourly data and want to take a max of the hours by day. I can take the max of the first 24 cells no problem. The trouble happens when i try an auto fill for the next 30 days. Is there a way to get the formula to read MAX(C25:C48) rather than MAX(C2:C26)
Posted by cpod on December 20, 2000 10:29 AM
in an empty column put a list of the day - no times - that are in your other column. then enter this formula in the next column and copy down.
{=MAX($B$2:$B$204*N(($A$2:$A$204>=E2)*($A$2:$A$204<E3)))} array formula
I have the date/time column as A and the corresponding number to max in column B.
This will give you a list of max amount by day.
Posted by Aladin Akyurek on December 20, 2000 10:41 AM
I assume column A contains nothing but your hour readings and the first reading in A1 (you can adapt the formulas to your actual data).
B1 =IF(COUNT(A:A)>0,MAX(A1:A24),"")
B2 =IF(COUNT(A:A)<>COUNT($B$1:B1)*24,MAX(INDIRECT(CONCATENATE("A",COUNT($B$1:B1)*24+1,":A",ROW()*24))),"")
Copy down the one in B2 as far as needed.
You can use the above formulas until someone has something better on offer.
Aladin
