More then 7 nested If functions.

[Guest]

This is my current formula:

=((Z6+(IF(C6="P",(AB$41+AB$27),IF(C6="B",(AB$48+AB$118),IF(C6="T",(AB$43+AB$18),IF(C6="DT",(AB$8+AB$18),IF(C6="ST",(AB$49+AB$119),IF(C6="AT1",(AB$30+AB$6),IF(C6="AT2",(AB$28+AB$11),IF(C6="DC",(AB$30+AB$11+AB$119),0)))))))))))

I need to add one more if function in this formula. What is the recommended
formula i should use. I would love to put this in a macro but not sure how i
should go about doing this.

Please advise

 

[T. Valko]

Will C6 *ALWAYS* match one of the letter codes?

One way:

=Z6+CHOOSE(MATCH(C6,{"P","B","T","DT","ST","AT1","AT2","DC","xx"},0),
AB$41+AB$27,AB$48+AB$118,AB$43+AB$18,AB$8+AB$18,AB$49+AB$119,
AB$30+AB$6,AB$28+AB$11,AB$30+AB$11+AB$119,range_for_xx)

Or, make a list of the letter codes in a range of cells, say, A1:A9, then:

=Z6+CHOOSE(MATCH(C6,A1:A9,0),AB$41+AB$27,AB$48+AB$118,
AB$43+AB$18,AB$8+AB$18,AB$49+AB$119,AB$30+AB$6,
AB$28+AB$11,AB$30+AB$11+AB$119,range_for_A9)

Biff

 

[T. Valko]

Another option:

Make a 2 column list with the letter codes in the left column and the
corresponding addition formulas in the right column:

Then:

=Z6+VLOOKUP(C6,A1:B9,2,0)

Biff