too many nested IF functions
 

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

too many nested IF functions
 

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

"Amanda" wrote in message
...

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

too many nested IF functions
 

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

too many nested IF functions
 

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",(.....




MartinW wrote:

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

too many nested IF functions
 

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
--
HTH...

Jim Thomlinson


"Amanda" wrote:


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

too many nested IF functions
 

Thanks Paul for explaining so clearly.

All is now working perfect!


"PCLIVE" wrote:

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

"Amanda" wrote in message
...

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