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

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

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

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

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

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