Index of Max Cell
 

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,G C3,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.

Index of Max Cell
 

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.

"Nate" wrote:

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,G C3,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.

Index of Max Cell
 

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.)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nate" wrote:

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,G C3,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.

Index of Max Cell
 

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.


Nate wrote:

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,G C3,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.

--

Dave Peterson

Index of Max Cell
 

You were right. Some of my arrays weren't matching after I dragged the
formula across. Thanks!

"PhilosophersSage" wrote:

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.

"Nate" wrote:

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,G C3,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.

Index of Max Cell
 

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!

"Luke M" wrote:

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.)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nate" wrote:

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,G C3,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.

Index of Max Cell
 

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.

"Dave Peterson" wrote:

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.


Nate wrote:

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,G C3,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.

--

Dave Peterson
.

Index of Max Cell
 

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?



Nate wrote:

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.

"Dave Peterson" wrote:

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.


Nate wrote:

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,G C3,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.

--

Dave Peterson
.


--

Dave Peterson