Finding the Max, Min values based on certain criteria for an array

  • Thread starter Thread starter swalk88
  • Start date Start date
S

swalk88

10:06 AM 9.56
10:06 AM 9.56
10:06 AM 9.56
10:06 AM 9.55
10:05 AM 9.55
10:04 AM 9.55
10:03 AM 9.55
10:03 AM 9.55
10:03 AM 9.55
10:03 AM 9.55
10:02 AM 9.53
10:02 AM 9.53
10:02 AM 9.53
10:01 AM 9.53
10:01 AM 9.53
10:01 AM 9.52
10:01 AM 9.52
10:00 AM 9.51
10:00 AM 9.51
10:00 AM 9.51
10:00 AM 9.5
10:00 AM 9.51
10:00 AM 9.51
10:00 AM 9.51
10:00 AM 9.51
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.5
10:00 AM 9.51
10:00 AM 9.51

I would like to know how to get the Max Value of say 10:00 AM, Max value of
10:01 AM etc. I would also like a formula to give the Min value and also the
start value and end value. There is a seconds component so for 10:00 AM the
first value is 9.51 and the last value is 9.51 too, for 10:01 AM the first
value is 9.52 and the last is 9.53 etc.
 
Try these...

E1:H1 = column headers = Max, Min, First, Last
D1 = 10:00Am
D2 = 10:01 AM

Array entered** in E2 and copied down to E3:

=MAX(IF(A$2:A$50=D2,B$2:B$50))

Array entered** in F2 and copied down to F3:

=MIN(IF(A$2:A$50=D2,B$2:B$50))

Entered in G2 and copied down to G3:

=LOOKUP(2,1/(A$2:A$50=D2),B$2:B$50)

Entered in H2 and copied down to H3:

=VLOOKUP(D2,A$2:B$50,2,0)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Thanks for that, for some strange reason when I copy it down it eventually
goes to 0 and #NA. I think the problem may lie with the times. Originally the
times were in 10:01:23 etc but I rounded it to the nearest minute with this
formula =FLOOR(A2,1/1440) and it worked but only for a few
 
For your lookup times:

10:00 AM
10:01 AM

How did you enter these times? Did you manually type them in or did you do a
fill series or use a formula?
 
I just typed them manually, thanks for helping

T. Valko said:
For your lookup times:

10:00 AM
10:01 AM

How did you enter these times? Did you manually type them in or did you do a
fill series or use a formula?
Click to expand...
 
Ok, let's try this...

Let's reference the *original time values*.

If your original time values are like: 10:01:23...

A2:A50 = Times
B2:B50 = Values

D2 = 10:00 AM
D3 = 10:01 AM

For the max (array entered**):

=MAX(IF((HOUR(Times)=HOUR(D2))*(MINUTE(Times)=MINUTE(D2)),Values))

For the min (array entered**):

=MIN(IF((HOUR(Times)=HOUR(D2))*(MINUTE(Times)=MINUTE(D2)),Values))

For the first:

=LOOKUP(2,1/((HOUR(Times)=HOUR(D2))*(MINUTE(Times)=MINUTE(D2))),Values)

For the last (array entered**):

=INDEX(Values,MATCH(1,(HOUR(Times)=HOUR(D2))*(MINUTE(Times)=MINUTE(D2)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Hi again,

Thanks for that, that part is working now, just have a few more questions.
There is also a volume for a certain time, would I just use a Sumif formula
to find the volume at the time?

Also, if I want to group the numbers in 5 minute intevals. i.e. 10:00-10:05
how would I go about that?

Lastly, How do I plot it on a chart and get it to ignore that 0's that may
appear in the data.

Thank you so much for the help.
 
There is also a volume for a certain time, would I
just use a Sumif formula to find the volume at the time?
Click to expand...

No, you'd need something like this:

For the total volume at 10:00 AM...

=SUMPRODUCT(--(HOUR(Times)=HOUR(D2)),--(MINUTE(Times)=MINUTE(D2)),Volume))
if I want to group the numbers in 5 minute intevals.
i.e. 10:00-10:05 how would I go about that?
Click to expand...

Do you mean find the max from 10:00 to 10:05 ? Since the time values
included minutes and seconds you probably need to adjust the interval to
10:00 AM to 10:06 AM. The reason is that there might be a time of something
like 10:05:59

Like this (array entered):

D2 = 10:00 AM
E2 = 10:06 AM

=MAX(IF((Times>=D2)*(Times<=E2),Values))
How do I plot it on a chart and get it to
ignore that 0's that may appear in the data.
Click to expand...

I rarely use charts but I think you need to replace 0 values with an NA().
 
Hi,

One last thing, its all good for most of it now but how would I find the
first and last for the five minute intervals, also the total volume.

I've worked all the rest out and I have tried inserting the if function into
the brackets but that didn't work.

Thanks
 
One last thing, its all good for most of it now but
how would I find the first and last for the five minute
intervals, also the total volume.
Click to expand...

Since it's a multiple time interval the first and last would be:

First would use the same formula as if you were finding the first for 10:00.

Last would use the same formula as if you were finding the last for 10:05.

For the 5 min volume:

D2 = 10:00 AM
E2 = 10:05 AM

=SUMPRODUCT(--(HOUR(Times)=HOUR(D2)),--(MINUTE(Times)<=MINUTE(D2)),Volume))

Those seconds are really making this more complicated than need be. I don't
know why you had trouble trying to convert to just h:mm.
 
Back
Top