Solved: Max Value and the date it occurred?

[Chinny]

Hi all,

Im hoping someone can help.. I have a table similar to the following

Date A B C
1/1/08 2 5 4
3/1/08 2 8 1
5/1/08 4 1 2
8/1/08 1 2 5

Where "Date" is cell A1

what I want to do is first of all find the Maximum value of the values in cells B2:C5 which is easy enough... =Max(B2:C5) result = 8

The part that I cant get my head around is how would I work out what date that the Max value occurred on i.e 3/1/08.

Any help would be greatly appreciated.

[Cyberdude]

Chinny, do you know how to write VBA macros? Your problem seems to need a two step solution:
1. Determine the max value, and
2. Determine which row the max value is on
I'm not sure you can do everything in one formula, but a
macro could be written to do it.

[Bob Phillips]

=INDEX(A2:A5,MATCH(TRUE,MMULT(--(B2:D5=MAX(B2:D5)),TRANSPOSE(COLUMN(B2:D5)^0))>0,0))

as an array formula.

[Chinny]

Excellent, Many Thanks xld!

-Chinny

[Aussiebear]

Any chance of breaking down this formula Bob? Baby steps for us baby VBA'ers

[Bob Phillips]

Okay Ted, I will give it a go.

First, the data is effectively two regions, the first A2:A5 is the associated dates, B2:B5 is the values over some sort of criteria I presume (by columns).

The OP wanted the date associated with the max value, so the first part is to identify the max value MAX(B2:D5). Useful, but only partially as we cannot match it using MATCH as MATCH is a single dimension function, the data is in two dimensions.

So we create an array of TRUE/FALSE of values that meet the MAX, B2:D5=MAX(B2:D5). We then do our usual trick to coerce this to an array of 1/0, --(B2:D5=MAX(B2:D5)).

From this we want to create a single dimension array of 1/0 that we can MATCH against, and we need it to be the column of values that the matched value is in, so we can use INDEX to get the date value associated. So for this we use MMULT, a very useful function in array formulae, which returns the matrix product of two arrays, an array with the same number of rows as array1 and the same number of columns as array2. We don't want any particular values from array2, just to preserve our 1/0 in array1 and garb the correct column, so we just use COLUMN(B2:B5)^0 to get an array of 1s. Because we want a single column, our array2 must be a single column, so we TRANSPOSE it. TRANSPOSE(COLUMN(B2:B5)^0).

MMULT generates our single column array of 1/0, so we can now just check for non-zero values,
MMULT(--(B2:D5=MAX(B2:D5)),TRANSPOSE(COLUMN(B2:D5)^0))>0
which again create a TRUE/FALSE array, but as it is single column, we can MATCH this with TRUE to get the matching row
MATCH(TRUE,MMULT(--(B2:D5=MAX(B2:D5)),TRANSPOSE(COLUMN(B2:D5)^0))>0,0)
and pass this to INDEX to get the associated date
=INDEX(A2:A5,MATCH(TRUE,MMULT(--(B2:D5=MAX(B2:D5)),TRANSPOSE(COLUMN(B2:D5)^0))>0,0))

As it happens, I think I overdid it here, I think I could simplify it a bit to
=INDEX(A2:A5,MATCH(1,MMULT(--(B2:D5=MAX(B2:D5)),TRANSPOSE(COLUMN(B2:D5)^0)),0))
because the first condition --(B2:D5=MAX(B2:D5)) will only return an array of 1/0, and the second array is only 1s, so the MMULT will only be an array of 1/0, so no need to check for greater than 0, just MATCH with 1 instead.

Clear as mud?

[Bob Phillips]

I have just realised why I did what I did.

The second variation works fine if there are multiple instances of the same max value in different rows, but if they are in the same row, it fails. The first variation, matching TRUE and testing for >0, works fine in either case.

[Aussiebear]

I had a crack at it earlier today by tryng to use either Match or Index but both errored out

=Index(Max(B2:C5),A2:A5,0)
and
=Match(Max(B2:C5),A2:A5,0)

One gave me a #N/A and the other a #Value, just from memory.

I then zipped over to Debra's site, where she talked about the #N/A result was because I was trying to match a numerical and a text type values. I'll have to go back to see exactly what she was trying to tell me.

[Bob Phillips]

The Max value isn't in A2:A5, so it will never give the answer.

[Bob Phillips]

I had a crack at it earlier today by tryng to use either Match or Index but both errored out

=Index(Max(B2:C5),A2:A5,0)
and
=Match(Max(B2:C5),A2:A5,0)

This is a particulalrly tricky problem Ted, which is why my answer goes all around the houses just to get the result.

[Aussiebear]

The Max value isn't in A2:A5, so it will never give the answer.

That's true Bob, but I was trying to either match or Index the max value from within the range B2:C5. Anyway it doesn't matter as I was betting on donkeys in a grey hound race.

I'm closer to the solution now that I've read your "manuscrpt" on Post # 6. I'm not at all confident that I'm going to remember it tommorrow though.....

[Shazam]

Another way...


=INDEX(A1:A5,MIN(IF(B2:D5=MAX(B2:D5),ROW(A3:A5)-ROW($A2)+1)))


Its also an-array.

[Aussiebear]

(Sigh)..... and I as just starting to see some daylight:banghead:

[Aussiebear]

(Sigh)..... and I was just starting to see some daylight:banghead: