Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need Help with a formula
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 |
#2
|
|||
|
|||
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" wrote in message ... 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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
I wrote:
Also, "1000-25" can be written simply 750. 975, not 750 of course. Klunk! |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |