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
|