And statement - forcing an outocme to take preference

**Mountain99** · 08-22-2013, 11:49 AM

=+IF(AND(R5="< $1.0m potential P&L impact"),"No escalation",IF(AND(R5="> $1.0m potential P&L impact"),"Regional CFO/Head Fin Ops",IF(AND(T5="< $1.0m potential P&L impact"),"Regional CFO/Head Fin Ops",IF(AND(T5="> $1.0m potential P&L impact"),"Regional CFO/Head Fin Ops and Reinsurance CFO",IF(AND(AB5="< $250k potential P&L impact"),"Regional CFO/Head Fin Ops",IF(AND(AB5="> $250k potential P&L impact"),"Regional CFO/Head Fin Ops and Reinsurance CFO",IF(AND(Z5="< $250k potential P&L impact"),"No escalation",IF(AND(Z5="> $250k potential P&L impact"),"Regional CFO/Head Fin Ops",IF(AND(Z5="<Enter amount>",R5="<Enter amount>"),"",""))))))))))

Consider the above formula.

IF R5 = True the result is "No escalation". However, if further along row 5 for example, T5 is True then the outcome will still be "No escalation" when it should be Regional CFO/Head Fin Ops i.e the formula only works if the results are all consistent.

Is there anyway that I can order the result so that "Regional CFO/Head Fin Ops and Reinsurance CFO" takes precedence over Regional CFO/Head Fin Ops and No escalation and Regional CFO/Head Fin Ops takes precedence over no escalation?

**amy_d2** · 08-22-2013, 12:29 PM

Hi

if statement will always give you a result of first true condition, it will only check next condition in case earlier is false.

hence amend your formula accordingly , if you want all conditions to be tru then use AND(R5="< $1.0m potential P&L impact",T5="< $1.0m potential P&L impact") etc.

**Jonmo1** · 08-22-2013, 12:32 PM

The IF's are read one at a time from Left to Right.
The FIRST one to have a TRUE result is used, all IF's further to the right are ignored.

So your order of preference should be highest priority on the left, to the lowest priority on the right.