software

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?

Yes

www.mcgimpsey.com/excel/variablerate.html

Regards,

Peo Sjoblom

quot;jayseecaquot; wrote:

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

Wow, thanks very much! I thought I had hours of tedious calculation ahead of
me.
Thanks again.

quot;Peo Sjoblomquot; wrote:

gt; Yes
gt;
gt; www.mcgimpsey.com/excel/variablerate.html
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; quot;jayseecaquot; wrote:
gt;
gt; gt; The best way to explain my question is by an example:
gt; gt; Lets say I have a bunch of numbers, one of them being 100,000.
gt; gt; Is it possible to create a formula in an Excel cell, which will tell me a
gt; gt; value when I want to sum -
gt; gt; the first 5,000 of the 100,000 multiplied by 14%, then add the NEXT 5,000 *
gt; gt; 10%, added with the NEXT 10,000 * 8%, and more than 20,000 of the 100,000
gt; gt; multiplied by 5%.
gt; gt; Then carry on this formula to other values, such as 150,000; 254,948... etc
gt; gt;
gt; gt; Does anyone understand what I'm asking?

for the first example on the site you directed me to.... would the formula
change of the first tax rate was not 0%?

quot;Peo Sjoblomquot; wrote:

gt; Yes
gt;
gt; www.mcgimpsey.com/excel/variablerate.html
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; quot;jayseecaquot; wrote:
gt;
gt; gt; The best way to explain my question is by an example:
gt; gt; Lets say I have a bunch of numbers, one of them being 100,000.
gt; gt; Is it possible to create a formula in an Excel cell, which will tell me a
gt; gt; value when I want to sum -
gt; gt; the first 5,000 of the 100,000 multiplied by 14%, then add the NEXT 5,000 *
gt; gt; 10%, added with the NEXT 10,000 * 8%, and more than 20,000 of the 100,000
gt; gt; multiplied by 5%.
gt; gt; Then carry on this formula to other values, such as 150,000; 254,948... etc
gt; gt;
gt; gt; Does anyone understand what I'm asking?

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

quot;jayseecaquot; wrote:

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


jayseeca Wrote:
gt; for the first example on the site you directed me to.... would the
gt; formula
gt; change of the first tax rate was not 0%?
gt;

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(--(A1gt;{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--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=543842