IF + IF?

[RoadKill]

Is there a way to combine the IF Statement. I will be using a similar formula
for six separate metrics so would be great if I didn't need to build a table
and do index matching.

Basically I want it to do this in my C6 cell:

IF A6="Cons" then =IF(B6>=90%, 5, IF(B6>=85%,4, IF(B6>=80%,3, IF(B6>=70%,2,
IF(B6<70%,1,"")))))

and

IF A6="Prem" then =IF(B6>=91%, 5, IF(B6>=86%,4, IF(B6>=83%,3, IF(B6>=75%,2,
IF(B6<75%,1,""))))).

 

[dan dungan]

see if_worksheet_function in help

 

[JLatham]

How about

=IF(A6="Cons",IF(B6>=0.9,5,IF(B6>=0.85,4,IF(B6>=0.8,3,IF(B6>=0.7,2,IF(B6<0.7,1,""))))),IF(A6="Prem",IF(B6>=0.91,5,IF(B6>=0.86,4,IF(B6>=0.83,3,IF(B6>=0.75,2,IF(B6<0.75,1,""))))),""))

 

[RoadKill]

I guess I was just missing the "")) at the end.

Thanks

 

[Pete_UK]

Put this in C6:

=IF(A6="Cons",IF(B6>=90%,5,IF(B6>=85%,4,IF(B6>=80%,3,IF(B6>=70%,
2,1)))),IF(A6="Prem",IF(B6>=91%,5,IF(B6>=86%,4,IF(B6>=83%,3,IF(B6>=75%,
2,1)))),""))

I think it does what you want.

Hope this helps.

Pete

 

[Pete_UK]

Actually, you do have 2 redundant IFs, because all values of B6 are
exhausted so you dont need the condition IF(B6<0.7,1,"") under
A6="Cons" as this will never return "", and similarly for
IF(B6<0.75,1,"") if A6="Prem".

Hope this helps.

Pete

 

[Roger Govier]

Hi

Try
=IF(A6="Cons",1+(B6>=0.7)+(B6>=0.8)+(B6>=0.85)+(B6>=0.9),
IF(A6="Prem",1+(B6>=0.75)+(B6>=0.83)+(B6>=0.86)+(B6>=0.91),""))

 

[JMB]

Just another version of what you've already posted
=1+SUM((A6="Cons")*(B6>={0.7,0.8,0.85,0.9}),(A6="Prem")*(B6>={0.75,0.83,0.86,0.91}))