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

  • Thread starter Thread starter JP
  • Start date Start date
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
 
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
 
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.
 
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.
 
Try the following lookup formula instead:

=LOOKUP(2,1/($A$2:$A$7<(C2+1/1440)),$B$2:$B$7)
 
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.
 
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$1:D1))

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.
 
I'm having problems utilizing both methods.
Disregarding any performance problems,
how else can this be done?

Thank you for your continued patience.
 
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$1:D1))}

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.
 
Since Low for
JP said:
We are close, but E5 is still incorrect, yielding 4 instead of 5.
Click to expand...

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
 
E5 referrs to 8:33 not 8:32. Thanks

Aladin Akyurek said:
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
Click to expand...
 
Maybe I've got it all wrong. Since there is no Low value for 8:33, the
LOOKUP() fetches the one for 8:32.
 
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.
 
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)
 
Back
Top