J
JP
A B C D
E F
1 Time Price Group High Low
Last
2 8:30:00 9 8:30 9 8
8
3 8:30:07 8 8:31 8 3
3
4 8:31:03 7 8:32 5 3
5
5 8:31:22 3 8:33 5 5
5
6 8:32:38 4
7 8:32:55 5
Ranges:
Time=A2:A7
Price=B2:B7
The array formula at D2 copied down:
=MAX(IF((HOUR(Time)=HOUR(C2))*(MINUTE(Time)=MINUTE(C2)),Price,F1))
The arrary formula at E2 copied down:
=MIN(IF((HOUR(Time)=HOUR(C2))*(MINUTE(Time)=MINUTE(C2)),Price,F1))
The array formula at F2 copied down:
=IF(ISERROR(INDIRECT("B"&MAX(IF((HOUR(Time)=HOUR(C2))*(MINUTE(Time)=MINUTE(C
2)),ROW(Price))))),F1,INDIRECT("B"&MAX(IF((HOUR(Time)=HOUR(C2))*(MINUTE(Time
)=MINUTE(C2)),ROW(Price)))))
The problem is the result at D3 should be 7, not 8 (the highest Price during
the 8:31 Time interval);
and the result at E4 should be 4, not 3 (the lowest Price during the 8:32
Time interval).
The results at D5:F5 are correct, each yielding the previous Last Price when
there is no matching Time for the 8:33 group (or Time interval).
And that's where the above two errors are taking place, incorrectly getting
the previous period's Last Price rather than the 8:31 Time interval's High
at D3 or the 8:32 Time interval's Low at E4.
All of the results in column F are correct yielding the last posted Time at
each Time interval.
I can see what's wrong, but all of my attempts at fixing it have failed.
Thank you in advance for your help,
JP
E F
1 Time Price Group High Low
Last
2 8:30:00 9 8:30 9 8
8
3 8:30:07 8 8:31 8 3
3
4 8:31:03 7 8:32 5 3
5
5 8:31:22 3 8:33 5 5
5
6 8:32:38 4
7 8:32:55 5
Ranges:
Time=A2:A7
Price=B2:B7
The array formula at D2 copied down:
=MAX(IF((HOUR(Time)=HOUR(C2))*(MINUTE(Time)=MINUTE(C2)),Price,F1))
The arrary formula at E2 copied down:
=MIN(IF((HOUR(Time)=HOUR(C2))*(MINUTE(Time)=MINUTE(C2)),Price,F1))
The array formula at F2 copied down:
=IF(ISERROR(INDIRECT("B"&MAX(IF((HOUR(Time)=HOUR(C2))*(MINUTE(Time)=MINUTE(C
2)),ROW(Price))))),F1,INDIRECT("B"&MAX(IF((HOUR(Time)=HOUR(C2))*(MINUTE(Time
)=MINUTE(C2)),ROW(Price)))))
The problem is the result at D3 should be 7, not 8 (the highest Price during
the 8:31 Time interval);
and the result at E4 should be 4, not 3 (the lowest Price during the 8:32
Time interval).
The results at D5:F5 are correct, each yielding the previous Last Price when
there is no matching Time for the 8:33 group (or Time interval).
And that's where the above two errors are taking place, incorrectly getting
the previous period's Last Price rather than the 8:31 Time interval's High
at D3 or the 8:32 Time interval's Low at E4.
All of the results in column F are correct yielding the last posted Time at
each Time interval.
I can see what's wrong, but all of my attempts at fixing it have failed.
Thank you in advance for your help,
JP