Index of Max Cell

  • Thread starter Thread starter Nate
  • Start date Start date
N

Nate

I'm trying to figure out how to return the cell location of the value that is
returned by my max value formula. My max value formula is
=MAX(EE3,EO3,EY3,FI3,FS3,GC3,GM3,GW3,HG3). I couldn't do an array, because I
only wanted the max value from January for each account, even though
Jan-Sept. is displayed on the spreadsheet. My Index formula is
=INDEX($EE$1:$HP$1,MATCH(MAX(EE3,EO3,EY3,FI3,FS3,GC3,GM3,GW3,HG3),$EE3:$HO3,0)),
which seems to work for January, but when I drag it to the other months it
doesn't seem to be functioning correctly. Any suggestions would be greatly
appreciated.
 
Nate,

check to ensure that the $EE$1:$HP$1 or $EE3:$HO3 are the exact for each
section, even if it is you may want to try removing one or both $ to check if
the absolute address is interfiering with formula. Excell is very finicky
about addressing in an index only have the $ that you need, if it could be
either way but it seems like it does not matter remove it, that seems to make
things work for me.
 
This is what I got to work:

=INDEX($1:$1,SUMPRODUCT(--(MOD(COLUMN($EE6:$HO6)-COLUMN(E$1),10)=0),--($EE6:$HO6=MAX(EE6,EO6,EY6,FI6,FS6,GC6,GM6,GW6,HG6)),COLUMN($EE6:$HO6)))

The reason your first formula isn't working is because its finding the MAX
of a subset of cells within the entire set of cells (thus, if your looking
for 5 under February 2010, but March 2009 is worth 5, the wrong cell will be
returned.)
 
This worked ok for me, but I had to add a new row (row 1) where each cell
indicated the month (the strings January, February, ..., December).

Then I used this array-entered formula:

=INDEX($EE$2:$HP$2,MATCH(TRUE,
((($EE$1:$HP$1="January")*$EE4:$HP4)=MAX(IF($EE$1:$HP$1="January",$EE4:$HP4)))
,0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

This portion (an array formula if you test in a dedicated cell):

=MAX(IF($EE$1:$HP$1="January",$EE4:$HP4))
Will return the maximum in EE4:HP4 but for only those cells that have January in
row 1 of the same column.

Then this portion:
(($EE$1:$HP$1="January")*$EE4:$HP4)

Will return an array of 0's and the values in EE4:HP4. (If there is no data in
any cell, it'll be treated as 0!)

Since:
($EE$1:$HP$1="January")
will be an array of 1's and 0's. (1 if it's january, 0 if it's not.)
then it's a simple multiplication (1 or 0)*(ee4*hp4)

Then it does a comparison against each of the elements in that array against the
max(if(...)) portion.

So that piece:
((($EE$1:$HP$1="January")*$EE4:$HP4)=MAX(IF($EE$1:$HP$1="January",$EE4:$HP4)))

Will be an array of true's and false's.

So the =match(true,{an array of true/falses},0)
will return the location of the first true in that array.

Then =index() uses that.
 
You were right. Some of my arrays weren't matching after I dragged the
formula across. Thanks!
 
I'm getting a #REF error when I'm pasting this into my spreadsheet. I'm sure
it's something that I'm not adjusting correctly. These are the cell
references that I updated -
=INDEX(EE$1:HP$1,SUMPRODUCT(--(MOD(COLUMN($EE3:$HG3)-COLUMN(E$3),10)=0),--($EE3:$HG3=MAX(EE3,EO3,EY3,FI3,FS3,GC3,GM3,GW3,HG3)),COLUMN($EE3:$HG3))).
Do you see where I might have messed up? Thanks!
 
Unfortunately, I wasn't able to get this to work. It's probably something
I'm doing incorrectly, but it was returning a lot of 0's for some reason.
 
Did you remember to array enter the formulas?

Were the cells with the months really just plain old text--January, February,
....

Or were they dates that were formatted to look like month names?
 
Back
Top