Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row and Col Index of a cell with Max Value | Excel Worksheet Functions | |||
help with index to return particular cell value | Excel Discussion (Misc queries) | |||
index,match + 1 cell below? | Excel Worksheet Functions | |||
autofill according to cell index | Excel Worksheet Functions | |||
autofill according to cell index. | Excel Discussion (Misc queries) |