How to setup a nested if function with 10 levels?

H

Haritt

I have a nested if functions with 10 levels. Excel says the max is 7 levels.
How can I get the 10 levels in?
=IF($J7="P7",D7*0.00004,IF($J7="P5",D7*0.00004,IF($J7="P6",D7*0.0004,IF($J7="P5",D7*0.00004,IF($J7="P4",D7*0.00006,IF($J7="P3",D7*0.00007,IF($J7="P2",D7*0.00006,IF($J7="P1",D7*0.00006,IF($J7="Q1",D7*0.00009,IF($J7="Q2",D7*.00019,IF($J7="Q3",D7*0.00019)))))))))))

Thanks,
 
M

Michael M

Hi
Have a look in Help for VLOOKUP.
This will give you a simpler method than using nested "IF's"

HTH
Michael
 
C

CLR

=IF(OR($J7="Q2",$J7="Q3"),D7*0.00019,IF(OR($J7="P7",$J7="P5"),D7*0.00004,IF(
$J7="P6",D7*0.0004,IF($J7="P4",D7*0.00006,IF($J7="P3",D7*0.00007,IF($J7="P2"
,D7*0.00006,IF($J7="P1",D7*0.00006,IF($J7="Q1",D7*0.00009,""))))))))

Vaya con Dios,
Chuck, CABGx3
 
R

Richard Buttrey

I have a nested if functions with 10 levels. Excel says the max is 7 levels.
How can I get the 10 levels in?
=IF($J7="P7",D7*0.00004,IF($J7="P5",D7*0.00004,IF($J7="P6",D7*0.0004,IF($J7="P5",D7*0.00004,IF($J7="P4",D7*0.00006,IF($J7="P3",D7*0.00007,IF($J7="P2",D7*0.00006,IF($J7="P1",D7*0.00006,IF($J7="Q1",D7*0.00009,IF($J7="Q2",D7*.00019,IF($J7="Q3",D7*0.00019)))))))))))

Thanks,

=IF(OR($J7="P7",$J7="P5",$J7="P6"),D7*0.0004,IF(OR($J7="P4",$J7="P2",$J7="P1"),D7*0.0006,IF($J7="P3",D7*0.0007,IF($J7="Q1",D7*0.00009,D7*0.00019))))

Rgds

Richard Buttrey
__
 
H

Haritt

Thanks, It worked and it let me copy it to other cells. I tried the name
and define function but it does not copy to multiple cells and adjust the
cell numbers.

Haritt
 

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