Seamus2626
asked on
Big IF
=IF(ED2="SCC","SCC",IF(LAR GE(V2:$AG2 ,3)>Analys is!$D$32," High",IF(L ARGE(V2:AG 2,3)<Analy sis!$E$32, "Low","Med ium")))
Hi,
this IF works nicely for me but i have just realised a flaw, i can only check against the large value of D32 + E32 if column D2 = 2
And to add, if column D2 = 1,3,4,5 or 6, i must check against D31 + E31
if column D2 <> 2,1,3,4,5 OR 6 then i must check against D33 + E33
---------------------
So, my formula must first check to see the client type in column D, then get the large number from the array, then based on client type, check the large number against
D31/E31
D32/E32
D33/E33
Thanks
Seamus
Hi,
this IF works nicely for me but i have just realised a flaw, i can only check against the large value of D32 + E32 if column D2 = 2
And to add, if column D2 = 1,3,4,5 or 6, i must check against D31 + E31
if column D2 <> 2,1,3,4,5 OR 6 then i must check against D33 + E33
---------------------
So, my formula must first check to see the client type in column D, then get the large number from the array, then based on client type, check the large number against
D31/E31
D32/E32
D33/E33
Thanks
Seamus
so why not create an extra column which produces the correct sum ...
e.g. az2 =iif(d2 = 2 , d32+e32,iif(d2=1 or d2=3 or d2=4 or d2=5 or d2=6 , d31+e31,d33+e33))
and then just use the az2 value in your existing formula...
e.g. az2 =iif(d2 = 2 , d32+e32,iif(d2=1 or d2=3 or d2=4 or d2=5 or d2=6 , d31+e31,d33+e33))
and then just use the az2 value in your existing formula...
Do you mean?
=IF(ED2="SCC","SCC",IF(LAR GE(V2:$AG2 ,3)>IF(ISN UMBER(MATC H(D2,{1,3, 4,5,6},0)) ,Analysis! $D$31,IF(D 2=2,Analys is!$D$32,A nalysis!$D $33)),"Hig h",IF(LARG E(V2:AG2,3 )<>IF(ISNU MBER(MATCH (D2,{1,3,4 ,5,6},0)), Analysis!$ E$31,IF(D2 =2,Analysi s!$E$32,An alysis!$E$ 33)),"Low" ,"Medium") ))
=IF(ED2="SCC","SCC",IF(LAR
ASKER
Hi NB_VC, the first column produces a score of Low, which is incorrect, it should be medium, i must go now but will look through monday morning, thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
poiffect, many thanks :-)
ASKER
Analysis-Template-EE.xlsm