Further If statement thoughts...
 

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

Further If statement thoughts...
 

Maybe this

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

Mike

"mike_vr" wrote:

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

Further If statement thoughts...
 

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


On 30 Sep., 15:49, mike_vr wrote:
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

Further If statement thoughts...
 

Nice one Mike, thanks!!!

"Mike H" wrote:

Maybe this

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

Mike

"mike_vr" wrote:

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

Further If statement thoughts...
 

Thanks Per, both for today and yesterday, appreciate it!

"Per Jessen" wrote:

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


On 30 Sep., 15:49, mike_vr wrote:
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