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

[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.

 

[T. Valko]

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.

 

[swalk88]

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

 

[T. Valko]

=FLOOR(A2,1/1440)

Try this:

=TIME(HOUR(A2),MINUTE(A2),0)

 

[swalk88]

Still nothing for the other ones, any other suggestions?

 

[T. Valko]

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?

 

[swalk88]

I just typed them manually, thanks for helping

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?

 

[Bernd P]

Hello,

I suggest to use my UDF Mfreq:
http://sulprobil.com/html/mfreq.html

Regards,
Bernd

 

[T. Valko]

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.

 

[swalk88]

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.

 

[T. Valko]

There is also a volume for a certain time, would I

just use a Sumif formula to find the volume at the time?

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?

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.

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

 

[T. Valko]

Ooops!

=MAX(IF((Times>=D2)*(Times<=E2),Values))

That should be:

=MAX(IF((Times>=D2)*(Times<E2),Values))

 

[swalk88]

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

 

[T. Valko]

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.

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.