Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Use Of If Function
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
  
Posts: 1
Default Use Of If Function
I am trying to use this formula but am unable to get the result as excel gives a message "too many functions" can some one help me with this


=IF(D21180000,D21*0.5%,IF(D21250000,D21*0.75%,IF (D21350000,D21*1.5%,IF(D21400000,D21*2.5%,IF(D21 450000,D21*3.5%,IF(D21550000,D21*4.5%,IF(D21650 000,D21*6%,IF(D21750000,D21*7.5%,IF(D21900000,D2 1*9%,IF(D211050000,D21*10%,IF(D211200000,D21*11% ,IF(D211450000,D21*12.5%,IF(D211700000,D21*14%,I F(D211950000,D21*15%,IF(D212250000,D21*16%,IF(D2 12850000,D21*17.5%,IF(D213550000,D21*18.5%,IF(D2 14550000,D21*19%,IF(D218650000,D21*20%)))))))))) )))))))))
Reply With QuoteReply With Quote
  #2   Report Post  
Junior Member
  
Posts: 6
Default
I am not sure why it is giving you that error. Another way you can do this is through the use of a macro. While I usually never recommend a macro where a formula could be used in this scenario it would work easily. Furthermore a macro would make it a lot easier to change values in the future if necessary.

Here is the start of the code

Code:
Option Explicit

Sub Macro1()
Dim Lastrow As Integer
Dim a As Integer

Lastrow = Range("D" & Rows.Count).End(xlUp).Row

For a = 1 To Lastrow
    Select Case Range("D" & a).Value
        Case 180000 To 249999
            Range("E" & a) = Range("D" & a) * 0.005
        Case 250000 To 349999
            Range("E" & a) = Range("D" & a) * 0.0075
        'etc etc etc
    End Select
Next a

End Sub
I made a couple of assumptions here. First is that all the values you are looking through are in column D (ex. D21, D22, D23, etc). Second your IF statement was in column E(ex. E21, E22, E23, etc respectively).

You will need to finish adding the cases for all the different percentages but it shouldnt take you too long. TEST THIS CODE ON A COPY OF YOUR SHEET.
It will overwrite stuff. Let me know how it works.
Reply With QuoteReply With Quote
  #3   Report Post  
Junior Member
  
Posts: 18
Default
Quote:
Originally Posted by Kazi View Post
I am trying to use this formula but am unable to get the result as excel gives a message "too many functions" can some one help me with this


=IF(D21180000,D21*0.5%,IF(D21250000,D21*0.75%,IF (D21350000,D21*1.5%,IF(D21400000,D21*2.5%,IF(D21 450000,D21*3.5%,IF(D21550000,D21*4.5%,IF(D21650 000,D21*6%,IF(D21750000,D21*7.5%,IF(D21900000,D2 1*9%,IF(D211050000,D21*10%,IF(D211200000,D21*11% ,IF(D211450000,D21*12.5%,IF(D211700000,D21*14%,I F(D211950000,D21*15%,IF(D212250000,D21*16%,IF(D2 12850000,D21*17.5%,IF(D213550000,D21*18.5%,IF(D2 14550000,D21*19%,IF(D218650000,D21*20%)))))))))) )))))))))
Hi,

I suppose that you use Excel 2003 or earlier version.
On those versions of Excel the limitation of nested functions is max 9
functions and you try with 19 nested IF-function on your
formula. On Excel 2007 or 2010 you can put functions inside other
functions max 64 times.
Reply With QuoteReply With Quote
  #4   Report Post  
Senior Member
  
Location: Belo Horizonte, Brazil
Posts: 170
Thumbs up
Quote:
Originally Posted by Kazi View Post
I am trying to use this formula but am unable to get the result as excel gives a message "too many functions" can some one help me with this

=IF(D21180000,D21*0.5%,IF(D21250000,D21*0.75%,IF (D21350000,D21*1.5%,IF(D21400000,D21*2.5%,IF(D21 450000,D21*3.5%,IF(D21550000,D21*4.5%,IF(D21650 000,D21*6%,IF(D21750000,D21*7.5%,IF(D21900000,D2 1*9%,IF(D211050000,D21*10%,IF(D211200000,D21*11% ,IF(D211450000,D21*12.5%,IF(D211700000,D21*14%,I F(D211950000,D21*15%,IF(D212250000,D21*16%,IF(D2 12850000,D21*17.5%,IF(D213550000,D21*18.5%,IF(D2 14550000,D21*19%,IF(D218650000,D21*20%)))))))))) )))))))))
--
Dear Kazi, Good Morning.

If you really want to use a IF FUNCTION to solve this problem, you can use this formula on your Excel version:

=IF(D21<=180000,0,"")&IF(AND(D21180000,D21<=25000 0),0.50%,"")&IF(AND(D21250000,D21<=350000),0.75%, "")&IF(AND(D21350000,D21<=400000),1.5%,"")&IF(AND (D21400000,D21<=450000),2.5%,"")&IF(AND(D2145000 0,D21<=550000),3.5%,"")&IF(AND(D21550000,D21<=650 000),4.5%,"")&IF(AND(D21650000,D21<=750000),6.0%, "")&IF(AND(D21750000,D21<=900000),7.5%,"")&IF(AND (D21900000,D21<=1050000),9.0%,"")&IF(AND(D211050 000,D21<=1200000),10.0%,"")&IF(AND(D211200000,D21 <=1450000),11.0%,"")&IF(AND(D211450000,D21<=17000 00),12.5%,"")&IF(AND(D211700000,D21<=1950000),14. 0%,"")&IF(AND(D211950000,D21<=2250000),15.0%,"")& IF(AND(D212250000,D21<=2850000),16.0%,"")&IF(AND( D212850000,D21<=3550000),17.5%,"")&IF(AND(D21355 0000,D21<=4550000),18.5%,"")&IF(AND(D214550000,D2 1<=8650000),19.0%,"")&IF(D218650000,20.0%,"")


The same formula JUST FOR VIEWING PURPOSES ONLY:

=IF(D21<=180000,0,"")
&IF(AND(D21 180000,D21<= 250000), 0.50%,"")
&IF(AND(D21 250000,D21<= 350000), 0.75%,"")
&IF(AND(D21 350000,D21<= 400000), 1.5%,"")
&IF(AND(D21 400000,D21<= 450000), 2.5%,"")
&IF(AND(D21 450000,D21<= 550000), 3.5%,"")
&IF(AND(D21 550000,D21<= 650000), 4.5%,"")
&IF(AND(D21 650000,D21<= 750000), 6.0%,"")
&IF(AND(D21 750000,D21<= 900000), 7.5%,"")
&IF(AND(D21 900000,D21<=1050000), 9.0%,"")
&IF(AND(D211050000,D21<=1200000),10.0%,"")
&IF(AND(D211200000,D21<=1450000),11.0%,"")
&IF(AND(D211450000,D21<=1700000),12.5%,"")
&IF(AND(D211700000,D21<=1950000),14.0%,"")
&IF(AND(D211950000,D21<=2250000),15.0%,"")
&IF(AND(D212250000,D21<=2850000),16.0%,"")
&IF(AND(D212850000,D21<=3550000),17.5%,"")
&IF(AND(D213550000,D21<=4550000),18.5%,"")
&IF(AND(D214550000,D21<=8650000),19.0%,"")
&IF(D218650000,20.0%,"")


I did this formula to you because you asked for it, BUT I STRONGLY recomend you to create a table at any column AND TO USE VLOOKUP FUNCTION.

Feel free to ask anything about this.
__________________
I hope it can help you.

Best regards,
Marcilio Lobão
---------------------------
Belo Horizonte, Brazil
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
Excel Data Validation/Lookup function does function correcty Kirkey Excel Worksheet Functions 2 May 25th 09 09:22 PM
copy of excel file not showing formulal/function in the function b oaallam Excel Discussion (Misc queries) 4 September 6th 07 01:20 PM
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) [email protected] Excel Worksheet Functions 0 September 5th 06 03:44 PM
Offset function with nested match function not finding host ss. MKunert Excel Worksheet Functions 1 March 21st 06 10:46 PM
Emulate Index/Match combo function w/ VBA custom function Spencer Hutton Excel Worksheet Functions 2 May 2nd 05 05:26 PM


All times are GMT +1. The time now is 09:42 AM.

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