Need Help with a formula

[CyndiP]

I am stumped on a formula... if ANY one can help me - please post!!!

Here is what i am trying to do:

Item sells at X Amount

If X amount is between $.01 - $25 then take 8% of that number = ?

If X amount is between $25.01 - $1000 then take 8% of the $.01 - $25 then 5% of the remaining = ?

If X amount is over $1000.01 then take 8% of the $.01 - $25, 5% of the $25.01 - $1000 then 3% for the remaining = ?


The final number would be a percentage based off of the above.

IE - If I sell an item at $18 - the number showing should be $1.44 - (8% of the $18)

IE - If I sell an item at $500 - the number showing should be $25.75 - (8% of the $25 = $2. automatically, then 5% of the $475)

PLEASE HELP - I AM STUCK ON A WORK PROJECT!!

[Bernie Deitrick]

CyndiP,

With X in cell A1, use the formula

=MIN(25,A1)*8%+MIN(975,MAX(0,A1-25))*5%+MAX(0,(A1-1000))*3%

HTH,
Bernie
MS Excel MVP


"CyndiP" <[email protected]> wrote in
message news:[email protected]...
>
> I am stumped on a formula... if ANY one can help me - please post!!!
>
> Here is what i am trying to do:
>
> Item sells at X Amount
>
> If X amount is between $.01 - $25 then take 8% of that number = ?
>
> If X amount is between $25.01 - $1000 then take 8% of the $.01 - $25
> then 5% of the remaining = ?
>
> If X amount is over $1000.01 then take 8% of the $.01 - $25, 5% of the
> $25.01 - $1000 then 3% for the remaining = ?
>
>
> The final number would be a percentage based off of the above.
>
> IE - If I sell an item at $18 - the number showing should be $1.44IE -
> (8% of the 18)
>
> IE - If I sell an item at $500 - the number showing should be $25.75 -
> (8% of the $25 = $2. automatically, then 5% of the $475)
>
> PLEASE HELP - I AM STUCK ON A WORK PROJECT!!
>
>
> --
> CyndiP
> ------------------------------------------------------------------------
> CyndiP's Profile:
> http://www.excelforum.com/member.php...o&userid=27941
> View this thread: http://www.excelforum.com/showthread...hreadid=474415
>

[[email protected]]

CyndiP wrote:
> I am stumped on a formula... if ANY one can help me
> - please post!!!

Keep in mind that there are many ways to do the same thing.

> If X amount is between $.01 - $25 then take 8% of that
> number = ?
>
> If X amount is between $25.01 - $1000 then take 8% of the
> $.01 - $25 then 5% of the remaining = ?
>
> If X amount is over $1000.01 then take 8% of the $.01 - $25,
> 5% of the $25.01 - $1000 then 3% for the remaining = ?

(I think you mean "over $1000".)

=ROUND(8%*MIN(25,A1) + 5%*MAX(0,MIN(1000-25,A1-25)) +
3%*MAX(0,A1-1000),2)

Caveat: You might want ROUND(...,2) around each sub-expression
instead of the final sum. Also, "1000-25" can be written simply
750. I wrote it as such so that you can see the derivation.

[[email protected]]

I wrote:
> Also, "1000-25" can be written simply 750.

975, not 750 of course. Klunk!

[Ron Rosenfeld]

On Fri, 7 Oct 2005 21:04:40 -0500, CyndiP
<[email protected]> wrote:

>
>I am stumped on a formula... if ANY one can help me - please post!!!
>
>Here is what i am trying to do:
>
>Item sells at X Amount
>
>If X amount is between $.01 - $25 then take 8% of that number = ?
>
>If X amount is between $25.01 - $1000 then take 8% of the $.01 - $25
>then 5% of the remaining = ?
>
>If X amount is over $1000.01 then take 8% of the $.01 - $25, 5% of the
>$25.01 - $1000 then 3% for the remaining = ?
>
>
>The final number would be a percentage based off of the above.
>
>IE - If I sell an item at $18 - the number showing should be $1.44IE -
>(8% of the 18)
>
>IE - If I sell an item at $500 - the number showing should be $25.75 -
>(8% of the $25 = $2. automatically, then 5% of the $475)
>
>PLEASE HELP - I AM STUCK ON A WORK PROJECT!!

Set up a table with your "break points" in column 1; the dollar amount to be
taken at that break point in column 2, and the percentage in column 3.

If the table is in M1:O3,Column 2 can be calculated by the formula:

=N1+(O1*(M2-M1))

Name the table "tbl".

For your data, the table would look like:

0 0 8%
25 2 5%
1000 50.75 3%

and would contain:

0 0 0.08
25 =N1+(O1*(M2-M1)) 0.05
1000 =N2+(O2*(M3-M2)) 0.03

The formula to calculate your final number would be (with your value in A1):

=VLOOKUP(A1,tbl,2)+VLOOKUP(A1,tbl,3)*(A1-VLOOKUP(A1,tbl,1))

The parameters can be easily changed by changing the table.


--ron