Nested If/and statement

[ScoobyDoo]

Hi, I would like to be able to return some text based on the numbers compared
in 2 cells v another 2 cells i.e.

if N16 >N15 and M16 < M15, "No change", if N16 = N15 and M16 < M15 "No
Change", if N16 = N15 and M16 = M15 "Increase", if N16 = N15 and M16 > M15
"Calsurp", if N16 < N15 and M16 < M15 "Inccal", if N16 < N15 and M16 >=M15
"Skip", if N16> n15 and m16 = m15 "incrcal", if n16 > n15 and m16 > m15
"Incrcal calsurp"

Any help with the above would be grately appreciated, thanks in advance

 

[David Biddulph]

Easiest in this sort of case to avoid the AND function and test column N as
the outer loop of the test, then for each column N condition have an inner
loop with the 3 choices for column M, hence something like:
=IF(N16>N15,IF(M16<M15,"No change",IF(M16=M15,"incrcal","Incrcal
cslsurp")),IF(N16=N15,IF(M16<M15,"No Change",IF(M16=M15,...
and complete as appropriate.

 

[Rick Rothstein \(MVP - VB\)]

Here is a different approach which should do what you want...

=IF(COUNT(N15,N16,M15,M16)=4,CHOOSE(3*SIGN(N16-N15)+SIGN(M16-M15)+5,"Incal","Skip","Skip","No
change","Increase","Calsurp","No change","Incrcal","Incral calsurp"),"")

Rick