Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > too many nested IF functions
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 151
Default 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





Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,311
Default 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







Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 151
Default 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








Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 860
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,939
Default 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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there any way around the maximum of seven nested functions? Nakia Allen Excel Worksheet Functions 5 July 14th 06 12:49 PM
limit of 7 nested functions? Olympiad Excel Worksheet Functions 3 May 28th 05 07:47 AM
Limited IF Nested Level functions. Skyscraper Excel Discussion (Misc queries) 1 April 8th 05 12:35 PM
how do I use multiple nested functions? TeeJay Excel Worksheet Functions 3 February 20th 05 05:09 PM
ENTER EXCEL FORMULA WITH MORE THAN 7 NESTED FUNCTIONS Linda Bolton Excel Worksheet Functions 2 January 14th 05 11:58 AM


All times are GMT +1. The time now is 10:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement