ExcelBanter - Use Of If Function

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Use Of If Function (https://www.excelbanter.com/excel-discussion-misc-queries/266592-use-if-function.html)

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%)))))))))) )))))))))

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.

Quote:

Originally Posted by Kazi (Post 960371)

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.

Quote:

Originally Posted by Kazi (Post 960371)

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