Nested IF formula construction

[Jon Johny]

I need some help with constructing a formula. This is what I have:

=IF((AND(G22="Strong Positive",I22="Positive")),"Moved Up",(IF((AND(G22="Strong Positive",I22="New")),"New Report"),(IF((AND(G22="Positive",I22="Promising")),"Moved Up",(IF((AND(G22="Promising",I22="Caution")),"Moved Up",IF(G22="Caution","Moved Down",(IF(G22=I22,"No Movement",(IF(G22="New","New Report")))))))))))

I don't know why it doesnt work, but essentially I want to create a nested IF(AND formula which translates to the following:

• If G22 is "Strong Positive" and I22 is "Positive", then make cell "Moved Up",
• Otherwise, If G22 is "Strong Positive" and I22 is "New", then make cell "New Report",
• Otherwise, If G22 is "Positive" and I22 is "Promising", then make cell "Moved Up",
• Otherwise, If G22 is "Promising" and I22 is "Caution", then make cell "Moved Up",
• Otherwise, If G22 is "Catuion" then make cell "Moved Down",
• Otherwise, If G22=I22 then make cell "No Movement",
• Otherwise, If G22 is "New" then make cell "New Report".

Any help appreciated, thank you.

 

[Gerald Higgins]

This works for many of your combinations, although not quite all of them - see if you can fix the outstanding one yourself.

Are there other possible permutations that are not addressed by this ?

=IF(AND(G22="Strong Positive",I22="Positive"),"Moved Up",
IF(AND(G22="Strong Positive",I22="New"),"New Report",
IF(AND(G22="Positive",I22="Promising"),"Moved Up",
IF(AND(G22="Promising",I22="Caution"),"Moved Up",
IF(G22="Caution","Moved Down",IF(G22=I22,"No Movement",
IF(G22="New","New Report","")))))))

 

[Jon Johny]

Im not really sure, which condition the formula doesnt work for?

Can you tell me and I can try to work it out, thanks.

 

[Gerald Higgins]

hmm, on second thoughts, maybe it does work for all of them. I thought there was one condition that didn't match exactly what you wanted, but I can't find it now.

What do you think ? Does it do what you want ?