Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH with MAX formula question
I'm hoping someone can help. I have the following formula in a spreadsheet:
=IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=3,"Mo derate Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=5,"Hi gh Complexity",""))) The problem I have is that if I have the same return number in both Moderate and High cells (E22 & I22 respectively), it returns Moderate - ideally I would like it to return High when the numbers are the same. How do I accomplish this? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH with MAX formula question
Can't you turn it the other way round, put the High IF comparison first, then
the moderate then the low? "Spongie" wrote: I'm hoping someone can help. I have the following formula in a spreadsheet: =IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=3,"Mo derate Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=5,"Hi gh Complexity",""))) The problem I have is that if I have the same return number in both Moderate and High cells (E22 & I22 respectively), it returns Moderate - ideally I would like it to return High when the numbers are the same. How do I accomplish this? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH with MAX formula question
I did try that, but for some reason it's still returning Moderate.
"Smallweed" wrote: Can't you turn it the other way round, put the High IF comparison first, then the moderate then the low? "Spongie" wrote: I'm hoping someone can help. I have the following formula in a spreadsheet: =IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=3,"Mo derate Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=5,"Hi gh Complexity",""))) The problem I have is that if I have the same return number in both Moderate and High cells (E22 & I22 respectively), it returns Moderate - ideally I would like it to return High when the numbers are the same. How do I accomplish this? Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH with MAX formula question
hi,
=IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low",IF(AND(M ATCH(MAX(C22:G22),C22:G22,0)=3,E22G22),"med","Hig h")) Mike "Spongie" wrote: I'm hoping someone can help. I have the following formula in a spreadsheet: =IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=3,"Mo derate Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=5,"Hi gh Complexity",""))) The problem I have is that if I have the same return number in both Moderate and High cells (E22 & I22 respectively), it returns Moderate - ideally I would like it to return High when the numbers are the same. How do I accomplish this? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH with MAX formula question
On Fri, 13 Nov 2009 03:12:02 -0800, Spongie
wrote: I'm hoping someone can help. I have the following formula in a spreadsheet: =IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=3,"M oderate Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=5,"H igh Complexity",""))) The problem I have is that if I have the same return number in both Moderate and High cells (E22 & I22 respectively), it returns Moderate - ideally I would like it to return High when the numbers are the same. How do I accomplish this? Thanks Assuming that I22 is a typo that should be G22, try the following formula: =IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))=CO LUMN(C22),"Low Complexity",IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C2 2:G22)))=COLUMN(E22),"Medium complexity",IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C2 2:G22)))=COLUMN(G22),"High Complexity"))) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
MATCH with MAX formula question
On Fri, 13 Nov 2009 20:51:52 GMT, Lars-Åke Aspelin
wrote: On Fri, 13 Nov 2009 03:12:02 -0800, Spongie wrote: I'm hoping someone can help. I have the following formula in a spreadsheet: =IF(MATCH(MAX(C22:G22),C22:G22,0)=1,"Low Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=3," Moderate Complexity",IF(MATCH(MAX(C22:G22),C22:G22,0)=5," High Complexity",""))) The problem I have is that if I have the same return number in both Moderate and High cells (E22 & I22 respectively), it returns Moderate - ideally I would like it to return High when the numbers are the same. How do I accomplish this? Thanks Assuming that I22 is a typo that should be G22, try the following formula: =IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C22:G22)))=C OLUMN(C22),"Low Complexity",IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C 22:G22)))=COLUMN(E22),"Medium complexity",IF(MAX(COLUMN(C22:G22)*(C22:G22=MAX(C 22:G22)))=COLUMN(G22),"High Complexity"))) Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke Here is a shorter formula: =INDEX({"Low","Medium","High"},(MAX(COLUMN(C22:G22 )*(C22:G22=MAX(C22:G22)))-COLUMN(A22))/2)&" Complexity" Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER rather than just ENTER Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula help with a match question | Excel Discussion (Misc queries) | |||
Formula/Comparison/Match Question | Excel Discussion (Misc queries) | |||
match formula question | Excel Worksheet Functions | |||
match formula question | Excel Worksheet Functions | |||
Match formula question | Excel Worksheet Functions |