On Fri, 7 Oct 2005 21:04:40 -0500, CyndiP
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
|