CODEWITHSUNDEEP
excelms-accessexcel-formulaexcel-2010vba
Avan
Avan

Reputation: 223

Excel formula construction assistance, too many arguments

Tax Table

I'm trying to think of the proper formula for this Tax table. (See attached image) but i'm not too familiar with excels loopy format. Can you spot what's wrong with my code?

=IF(J2<10000,J2*0.05,IF(J2<30000,(J2-10000)*0.1+500),IF(J2<70000,(J2-30000)*.15+2500),IF(J2<140000,(J2-70000)*.20+8500),IF(J2<250000,(J2-140000)*.25+22500),IF(J2<500000,(J2-250000)*.30+50000),IF(J2>500000,(J2-500000)*.32+125000))

Clearer format:

IF(J2<10000,
J2*0.05,
IF(J2<30000,
(J2-10000)*0.1+500),
IF(J2<70000,
(J2-30000)*.15+2500),
IF(J2<140000,
(J2-70000)*.20+8500),
IF(J2<250000,
(J2-140000)*.25+22500),
IF(J2<500000,
(J2-250000)*.30+50000),
IF(J2>500000,
(J2-500000)*.32+125000))

It's saying I have entered too many arguments. There are exactly 7 IF statements. is there any way I can make this work? Hopefully without using names or variables because I actually want to transfer this formula as is into a C# program, so this format is preferred.

Upvotes: 0

Views: 147

Answers (4)

Maciej Lipinski
Maciej Lipinski

Reputation: 185

If you wanted to do this in VBA, it would be much more readable, maintainable, and extensible :) Here's my take on it:

Public Function TaxDue(TaxableIncome As Double) As Double

    Select Case TaxableIncome
        Case Is < 10000
            TaxDue = TaxableIncome * 0.05
        Case Is < 30000
            TaxDue = TaxableIncome * 0.1 + 500
        Case Is < 70000
            TaxDue = TaxableIncome * 0.15 + 2500
        Case Is < 140000
            TaxDue = TaxableIncome * 0.2 + 8500
        Case Is < 250000
            TaxDue = TaxableIncome * 0.25 + 22500
        Case Is < 500000
            TaxDue = TaxableIncome * 0.3 + 50000
        Case Else
            TaxDue = TaxableIncome * 0.32 + 125000
    End Select

End Function

For this to work you have to put it in a module in the file where you want to use the formula. After it's in the module, simply type =TaxDue in a cell.

Upvotes: 2

YowE3K
YowE3K

Reputation: 23994

The formula you are after is:

=IF(J2< 10000, J2        *0.05,
 IF(J2< 30000,(J2- 10000)*0.10+   500,
 IF(J2< 70000,(J2- 30000)*0.15+  2500,
 IF(J2<140000,(J2- 70000)*0.20+  8500,
 IF(J2<250000,(J2-140000)*0.25+ 22500,
 IF(J2<500000,(J2-250000)*0.30+ 50000,
              (J2-500000)*0.32+125000))))))

Upvotes: 2

L.Dutch
L.Dutch

Reputation: 966

Sometimes Excel's remark about "too many arguments" is to be read as "too many parentesis".

Try this version of your formula:

=IF(J2<10000,J2*0.05,IF J2<30000,(J2-10000)*0.1+500,IF J2<70000,(J2-30000)*.15+2500,IF J2<140000,(J2-70000)*.20+8500,IF J2<250000,(J2-140000)*.25+22500,IF J2<500000,(J2-250000)*.30+50000,IFJ2>500000,(J2-500000)*.32+125000)

Upvotes: 2

Bernd Stoeckel
Bernd Stoeckel

Reputation: 145

I have no excel handy for another 2 hours to test but look at you closing of the nested if formulas. I have the impression you close each before the 'else' argument. You did it the right way in the first if. Put all the closing ')' to the end.

Upvotes: 2

Related Questions