Is there such a formula? ... Yes/No?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The best way to explain my question is by an example:
Lets say I have a bunch of numbers, one of them being 100,000.
Is it possible to create a formula in an Excel cell, which will tell me a
value when I want to sum -
the first 5,000 of the 100,000 multiplied by 14%, then add the NEXT 5,000 *
10%, added with the NEXT 10,000 * 8%, and more than 20,000 of the 100,000
multiplied by 5%.
Then carry on this formula to other values, such as 150,000; 254,948... etc

Does anyone understand what I'm asking?
 
Wow, thanks very much! I thought I had hours of tedious calculation ahead of
me.
Thanks again.
 
for the first example on the site you directed me to.... would the formula
change of the first tax rate was not 0%?
 
Here's another way to do it:

=MAX(MIN(A1,5000),0)*0.14+MAX(MIN(A1-5000,5000),0)*0.1+MAX(MIN(A1-10000,10000),0)*0.08+MAX(A1-20000,0)*0.05

HTH,
Elkar
 
jayseeca said:
for the first example on the site you directed me to.... would the
formula
change of the first tax rate was not 0%?
Click to expand...

Because, in your example, the tax starts at 0 you need the first array
(containing the bands) to start at 0, i.e. for the example you give

=SUMPRODUCT(--(A1>{0,5000,10000,20000}),A1-{0,5000,10000,20000},{0.14,-0.04,-0.02,-0.03})

..or a modified version of Elkar's suggestion....

=MEDIAN(A1,5000,0)*0.14+MEDIAN(A1-5000,5000,0)*0.1+MEDIAN(A1-10000,10000,0)*0.08+MAX(A1-20000,0)*0.05
 
Back
Top