multiple IF Formula help, Please

[Karen]

Hi,
Can you please help me with figuring out what formula I need for below.

0-85,000 units = $0
85,001 -100,000 units = $2
100,000-150,000 units = $3

if i had 90,000 units in a cell, then my result would be 90000-85000 =5,000
x $2
if i had 105,000 units in a cell,then my result should be 100,000 -85,000
=15,000 x $2
if i had 70,000 units in a cell, then my result should be 0.

I need all 3 if statements in my formula in one cell. Is that possible?
I think once i get this then i can go onto the next tier of the $3 in the
next cell, i hope?!

Thanks in advance,
Karen

 

[T. Valko]

See this:

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

 

[JoeU2004]

Can you please help me with figuring out what formula I need for below.

To be sure we have a common understanding of your requirements, and to
eliminate solutions that depend on the special case of zero for the
first-tier value (as your description does), consider a different example:

$2 0-85,000 units
$5 85,001-100,000 units
$9 100,001 or more units

For 200,100 units, I presume you would compute 1,145,900 as follows: 85,000
at $2, plus 15,000 (100,000 - 85,000) at $5, plus 100,100 (200,100 -
100,000) at $9.

One intuitive solution, adapted to your example, is (A1 is the number of
units):

=0*MIN(85000,A1) + 2*MAX(0,MIN(100000-85000,A1-85000))
+ 3*MAX(0,A1-100000)

Of course, the first term, 0*MIN(85000,A1), could be excluded in your case.
I include it to remind us that it is needed in general; namely, for my
example above, the first term would be 2*MIN(85000,A1).

Alternatively, adapting McGimpsey's approach, described at
http://mcgimpsey.com/excel/variablerate.html, to your example:

=SUMPRODUCT((A1>{0,85000,100000})*(A1-{0,85000,100000}),{0,2,1})

Note that the array of tiered values {0,2,1} are __incremental__, namely:
3 - 1 (2) for the second tier, and 3 - 2 (1) for the third tier.
Technically, the first-tier array value is also incremental, namely: 0 - 0;
but of course, that is simply the first-tier unit value (0).

To be sure you fully understand, the following is the McGimpsey solution
adapted to my example:

=SUMPRODUCT((A1>{0,85000,100000})*(A1-{0,85000,100000}),{2,3,4})


----- original message -----

 

[Karen]

This worked great! Thank you!

See this:

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

 

[T. Valko]

You're welcome. Let's thank that JE for making that available to the masses!