too many nested IF functions

[Amanda]

I have created the below IF formula but it will not let me go any further.

=IF(D3="Especial",(((H3-0.12)*1.4)+0.3),IF(D3="Nacional",(((H3-0.12)*1.4)+0.3),IF(D3="Llam.
Interna",(((H3-0.12)*1.4)+0.3),IF(D3="Internacional",(((H3-0.3)*1.4)+0.3),IF(D3="Núm.
Vodafone",(((H3-0.12)*1.4)+3),IF(D3="Mensaje
Corto",(H3*104),IF(D3="Realiz.Vodafone World",(H3*1.4),IF(D3="Recib.
Extranj",(H3*1.4)))))))))

I still need to add in:

IF(D3="Provincial",(((H3-0.12)*1.4)+0.3),IF(D3="Sms
enviado.roaming",(H3*1.4), IF(D3="Realiz.Roaming",(H3*1.4),IF(D3="Sms
extranj-España",(H3*1.4),

and maybe a couple more



Please can somebody help?

Many thanks
Mand

 

[PCLIVE]

On way would be to combine the applicable IFs using OR.

=IF(OR(D3="Especial",D3="Nacional",D3="Llam.
Interna",D3="Internacional",D3="Provincial"),((H3-0.12)*1.4)+0.3,IF(D3="Núm.
Vodafone",(((H3-0.12)*1.4)+3),IF(D3="Mensaje
Corto",(H3*104),IF(OR(D3="Realiz.Vodafone World",D3="Recib. Extranj",D3="Sms
enviado.roaming",D3="Realiz.Roaming",D3="Sms extranj-España"),(H3*1.4)))))

It's a little easier to view when broken down (below). Only using 4 IFs.
=IF(OR(D3="Especial",D3="Nacional",D3="Llam.
Interna",D3="Internacional",D3="Provincial"),((H3-0.12)*1.4)+0.3,
IF(D3="Núm. Vodafone",(((H3-0.12)*1.4)+3),
IF(D3="Mensaje Corto",(H3*104),
IF(OR(D3="Realiz.Vodafone World",D3="Recib. Extranj",D3="Sms
enviado.roaming",D3="Realiz.Roaming",D3="Sms extranj-España"),(H3*1.4)))))

HTH,
Paul

 

[MartinW]

Hi Amanda,

One way is to join them together as seperate IF functions
rather than nesting i.e.

=IF(D3="Especial",(((H3-0.12)*1.4)+0.3),"")&IF(D3="Nacional",(((H3-0.12)*1.4)+0.3),"")&IF(D3="Llam.Interna",(((H3-0.12)*1.4)+0.3),"")&IF(D3=.......
etc. etc.

HTH
Martin

 

[Dave Peterson]

It looks like this should return a number.

You may want to replace the empty strings ("") with 0's. and instead of
concatenating, I'd just add.

=IF(D3="Especial",(((H3-0.12)*1.4)+0.3),0) + IF(D3="Nacional",(.....

 

[Jim Thomlinson]

And if all else fails here is a way of getting aroung the XL limitation of 7
nested if statements...

http://www.cpearson.com/excel/nested.htm

 

[Amanda]

Thanks Paul for explaining so clearly.

All is now working perfect!