Further If statement thoughts...

M

mike_vr

Hi all

Further to yesterday, apparently I got the requirements wrong. Instead of a
simple if statement to get the right % for a range of values like so:

0-3,500 = 0.5%
3,501-10,000 = 1.0%
10,001-upwards = 1.5%

I now need to have it as all amounts up to 3,500 to be taxed at 0.5%, any
amount over that the difference taxed at 1% up until 10,000 and then the
remainder over 10,000 to be taxed at 1.5%.

I.e. if we had an amount of 12,000 then the first 3,500 would be taxed at
0.5% = 17.5, the next 6,500 (10,000-3,500) at 1% = 65 and then the remaining
2,000 at 1.5% = 30 to give a total final result of 112.5.

Current formula to get the tax brackets is
=IF(G1<=3500,0.005,IF(G1<=10000,0.01,0.015))*G1

Thanks in advance,
Mike
 
M

Mike H

Maybe this

=MIN(A1,3500)*0.5%+(A1>3500)*(MIN(A1-3500,6500)*1%)+(A1>10000)*MIN(A1-10000,9999999)*1.5%

Mike
 
P

Per Jessen

Hi Mike

Try this:

=IF(G1<=3500,G1*0.005,IF(G1<=10000,17.5+(G1-3500)*0.01,17.5+65+
(G1-10000)*0.015))

Regards,
Per
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Simple nested if statement 1
What's wrong with this formula - NESTED IF STATEMENTS 4
IF formula with correct formatting in result 3
Nested If statement to find greater than but less than numbers 3
Need help on exporting an Excel Sheet into Access 2000 1
Excel 2003 IF AND question 7
excel timesheet nested if statements 1
Help with IF statement 3

Top