Help with nested formula

[Guest]

Hi,
Could you please help me with a formula for the following situation:
I have the amount of 20450.
(1-10000) at 30%
(10001-20000) at 27%
(20001-30000) at 24%
(30001-40000) at 21%
(Over 40001) at 18%
But if the result is less than 0 then I need the cell to show 0.
Is there a formula for this?
Thanks in advance.

 

[Ragdyer]

Check out this link to J.E. McGimpsey's web page on this very subject:

http://www.mcgimpsey.com/excel/variablerate.html

 

[Pete_UK]

Set up this table somewhere on your sheet (eg X1:Y5):

1 30%
10001 27%
20001 24%
30001 21%
40001 18%

Then, assuming your value of 20450 is in A1, use this formula to
return the appropriate percentage:

=IF(A1<1,0,VLOOKUP(A1,X1:Y5,2))

Hope this helps.

Pete

 

[Guest]

With the base value in cel A1

Try this:
B1: =IF(A1>0,33%-MIN(CEILING(A1/10000,1),4)*0.03,0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks for you answers but I will explain different:
I was entering the result manually and this is the end result:
I have the amount of 20450 in A1.

(1-10000) at 30% = 30000
(10001-20000) at 27% = 27000
(20001-30000) at 24% = 108
(30001-40000) at 21%
(Over 40001) at 18%
Total = 57108

Ron and Pete if you try this with that formula you will notice the result is
0.24. In this case I need the first 10K calculated at 30%, the second 10K
calculated at 27% and so on, and at the end I am adding that column to get
the total. Now, you can see that the line for 21% and 18% do not apply so I
need the result to be 0.

 

[Dana DeLouis]

(1-10000) at 30% = 30000

Hi. Just to mention...

=10000*30% = 3000

I was thinking the solution might be 5808.

Here's my guess...

=MIN(3000 + 0.18*A1,1800 + 0.21*A1, 900 + 0.24*A1,300 + 0.27*A1, 0.3*A1)

 

[Pete_UK]

Ah, I thought you meant that you had only one percentage which applied
to the value. As your percentages are compounded, the link to JE's
site that Ragdyer gave you earlier, i.e.:

http://www.mcgimpsey.com/excel/variablerate.html

explains very well how to accomplish this, either using a single
formula or with tables.

Hope this helps.

Pete

 

[Guest]

Hi Dana, thanks for the correction, that's what I meant. :^)