If Statement

  • Thread starter Thread starter patrickbirch
  • Start date Start date
P

patrickbirch

I have a formula in a spreadsheet at work which requires more than seven
nested if statements to give me a true answer, is there a way for excel
to do this or is there some other function which could be used instead
of if.

This is a problem i have had for a while so any suggestions would be
appreciated.

Patrick
 
Patrick,

There are many options, for instance this recent reply and responses shows a
couple

IF(G13=0,IF(I13=1,"A1-7",IF(G13=0,IF(I13=2,"A1-8",IF
(G13=0,IF(I13=3,"B1-7",IF(G13=0,IF(I13=4,"B1-8",IF(G13=1,IF
(I13=1,"A1-9",IF(G13=1,IF(I13=2,"A1-10",IF(G13=1,IF
(I13=3,"B1-9",IF(G13=1,IF(I13=4,"B1-10",IF(G13=2,IF
(I13=1,"A1-11",IF(G13=2,IF(I13=2,"A1-12",IF(G13=2,IF
(I13=3,"B1-11",IF(G13=2,IF(I13=4,"B1-12",IF(G13=3,IF
(I13=1,"A1-13",IF(G13=3,IF(I13=2,"A1-14",IF(G13=3,IF
(I13=3,"B1-13",IF(G13=3,IF(I13=4,"B1-14",IF(G13=4,IF
(I13=1,"A1-16",IF(G13=4,IF(I13=2,"A1-17",IF(G13=4,IF
(I13=3,"B1-16",IF(G13=4,IF(I13=4,"B1-17","blank
cell"))))))))))))))))))))))))))))))))))))))))


One way:
=CHOOSE(I13,"A1-","A1-","B1-","B1-")&IF(ISODD(I13),1,2)+(G13+1)*2+4

Another:
=IF(G13>4, "", CHAR(65+INT(I13/3))&"1-" & 7+G13*2+ISEVEN(I13))

Post your problem for detailed help.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Patrick,

You can have more than 7 nested functions. It just won't work.

You can break it up. Put the 8th and above nested functions in a formula in
a separate cell, and refer to that cell in the first formula. Hide the
second if need be.

If this is a redundant test of a value, you may find a VLOOKUP a bit tidier
than a string of nested IF functions, once you're comfortable with it. Post
back with your actual formula.
 
This is the formula that is causing me so much trouble, I want to extend
this to look at approximately 16 cells instead of the 5 which it
currently looks at, the fifth cell being an average.

Basically this formula is being used to calculate the weeks cover on
inventory after subtracting weekly sales and addition of weekly
production, it works ok apart from when there is a large forecast on
sales further out than the five cells it currently looks at(obviously
this is because it doesn't reach that far), this is the reason i need
more than the seven if statements that excel allows

IF(E91-F89<1,E91/F89*F$3,IF(E91-F89-G89<2,F$3+(E91-F89)/G89*G$3,IF(E91-F89-G89-H89<3,F$3+G$3+(E91-F89-G89)/H89*H$3,IF(E91-F89-G89-H89-I89<4,F$3+G$3+H$3+(E91-F89-G89-H89)/I89*I$3,IF(E91-F89-G89-H89-I89-J89<5,F$3+G$3+H$3+I$3+(E91-F89-G89-H89-I89)/J89*J$3,F$3+G$3+H$3+I$3+(E91-F89-G89-H89-I89)/J89*J$3))))))

Any ideas?

Thanks
Patrick
 
Back
Top