Formula HELp


D

DP7

Why does the below formula not work? Any help would be greatly appreciated.
Thanks
=IF(AND(AN10="AIR",AX10="Fri"),AH10+3,IF(AND(AN10="AIR",AX10="Sat"),AH10+2,IF(AND(AN10="AIR",OR(AX10="Mon",AX10="Tue",AX10="Wed",AX10="Thu",AX10="Sun")),AH10+1,IF(AX10="Mon",AH10+4,IF(AX10="Tue",AH10+6,IF(AX10="thu",AH10+5,IF(AX10="fri",AH10+5,IF(AX10="sun",AH10+5,IF(AX10="Will advise",AX10,"")))))))))
 
Ad

Advertisements

D

David Biddulph

Which version of Excel? If before 2007, I guess your problem is too many
layers of nesting.

Try to rearrange to reduce the nesting depth, such as
=IF(AN10="AIR",IF(AX10="Fri",AH10+3,IF(AX10="Sat",AH10+2,IF(OR(AX10="Mon",AX10="Tue",AX10="Wed",AX10="Thu",AX10="Sun"),AH10+1,""))),IF(AX10="Mon",AH10+4,IF(AX10="Tue",AH10+6,IF(AX10="thu",AH10+5,IF(AX10="fri",AH10+5,IF(AX10="sun",AH10+5,IF(AX10="Will
advise",AX10,"")))))))
or go another stage to
=IF(AN10="AIR",IF(AX10="Fri",AH10+3,IF(AX10="Sat",AH10+2,IF(OR(AX10="Mon",AX10="Tue",AX10="Wed",AX10="Thu",AX10="Sun"),AH10+1,""))),IF(AX10="Mon",AH10+4,IF(AX10="Tue",AH10+6,IF(OR(AX10="thu",AX10="fri",AX10="sun"),AH10+5,IF(AX10="Will
advise",AX10,"")))))

[With either of these, as in your original, it doesn't specifically cover
"Wed" or "Sat" if AN10 is not "AIR", so presumably this is intended to
return a blank result?]

An alternative approach for reducing nesting depth is to use a LOOKUP
instead of nested IFs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top