Array---I know what's wrong, but I can't fix it

[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

 

[JP]

Reposted below with, hopefully, better formatting

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

 

[Aladin Akyurek]

D2:

=MAX(IF(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7))

Confirm with control+shift+enter & copy down.

E2:

=MIN(IF(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7))

Confirm with control+shift+enter & copy down.

F2:

=LOOKUP(2,1/(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440))),$B$2:$B$7)

Confirm with just enter.

Note the differences between the results calculated by the above formulas
and the results you expect.

 

[JP]

Wow, thank you, I'm going to have to think about how this works.

But now we have a problem that wasn't there before; that is:
D5:F5 were previously correct.
They yielded the previous period's Last price
when no Time (8:33) was found.

The results I need for D5:F5 are:
5 5 5 (The result from F4.)

Thank you very much once again.

 

[Aladin Akyurek]

Try the following lookup formula instead:

=LOOKUP(2,1/($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)

 

[JP]

That corrects the problem at Column F;
however, D5 and E5 still each need to evaluate to 5,
(the result from F4).
That was basically my original problem,
I could not get that third condition,
that if the time in Column C
could not be found in Column A
then get the result in the previous row's Column F.

 

[Aladin Akyurek]

I see. But, I don't want you to suffer performance problems. Hence:

D2:

=IF(SETV(MAX(IF(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7))),GETV(),
LOOKUP(9.99999999999999E+307,$D$11))

Confirm with control+shift+enter and copy down.

E2:

=IF(SETV(MIN(IF(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7))),GETV(),
LOOKUP(9.99999999999999E+307,$E$1:E1))

Confirm with control+shift+enter and copy down.

F2:

=LOOKUP(2,1/($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)

Confirm with enter.

Note that SETV() and GETV() are part of the morefunc.xll add-in, which you
can download from:

http://longre.free.fr/english

If you can't install this add-in, replace SETV and GETV with V, whose code
in VBA follows:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

To add this to your workbook:

Activate Tools|Macro|Visual Basic Editor;
Activate Insert|Module;
Copy the UDF above and paste it in the pane entitled "...(code)".
Activate File|Close and Return to Microsoft Excel.

 

[JP]

I'm having problems utilizing both methods.
Disregarding any performance problems,
how else can this be done?

Thank you for your continued patience.

 

[Aladin Akyurek]

D2:

{=IF(MAX(IF(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)),MAX(IF(($A$2
:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)),LOOKUP(9.99999999999999E+307,
$D$11))}

E2:

{=IF(MIN(IF(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)),MIN(IF(($A$2
:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)),LOOKUP(9.99999999999999E+307,
$E$1:E1))}

F2 same as before.

 

[JP]

We are close, but E5 is still incorrect, yielding 4 instead of 5.

 

[Aladin Akyurek]

Since Low for

We are close, but E5 is still incorrect, yielding 4 instead of 5.

How come since the Low value for 8:32 is 4, given the sample (see below)
I've been using?

Time Price Group
8:30:00 9 8:30
8:30:07 8 8:31
8:31:03 7 8:32
8:31:22 3 8:33
8:32:38 4
8:32:55 5

 

[JP]

E5 referrs to 8:33 not 8:32. Thanks

Since Low for


How come since the Low value for 8:32 is 4, given the sample (see below)
I've been using?

Time Price Group
8:30:00 9 8:30
8:30:07 8 8:31
8:31:03 7 8:32
8:31:22 3 8:33
8:32:38 4
8:32:55 5

 

[Aladin Akyurek]

Maybe I've got it all wrong. Since there is no Low value for 8:33, the
LOOKUP() fetches the one for 8:32.

 

[JP]

Right, but I don't want it to do that.
When there is no matching value in Column A,
High or Low (8:33 in this caes),
I want it to simply fetch the value from
Column F (Last) of the preceeding row,
in the case of E5 it would fetch F4.

 

[Aladin Akyurek]

D2:

{=IF(MAX(IF(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)),MAX(IF(($A$2
:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)),LOOKUP(9.99999999999999E+307,
F$1:$F1))}

E2:

{=IF(MIN(IF(($A$2:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)),MIN(IF(($A$2
:$A$7>=C2)*($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)),LOOKUP(9.99999999999999E+307,
$F$1:F1))}

F2:

=LOOKUP(2,1/($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)

 

[JP]

Halleluiah! At last. Thank you, thank you, thank you.

Regards,
JP