Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More then 7 nested If functions.
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+A B$6),IF(C6="AT2",(AB$28+AB$11),IF(C6="DC",(AB$30+A B$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 -- sarah |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More then 7 nested If functions.
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_fo r_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 "sarah" wrote in message ... 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+A B$6),IF(C6="AT2",(AB$28+AB$11),IF(C6="DC",(AB$30+A B$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 -- sarah |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More then 7 nested If functions.
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 "T. Valko" wrote in message ... 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_fo r_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 "sarah" wrote in message ... 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+A B$6),IF(C6="AT2",(AB$28+AB$11),IF(C6="DC",(AB$30+A B$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 -- sarah |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there any way around the maximum of seven nested functions? | Excel Worksheet Functions | |||
nested if functions in Excel 2002 | Excel Worksheet Functions | |||
limit of 7 nested functions? | Excel Worksheet Functions | |||
how do I use multiple nested functions? | Excel Worksheet Functions | |||
ENTER EXCEL FORMULA WITH MORE THAN 7 NESTED FUNCTIONS | Excel Worksheet Functions |