Forum Discussion
trying to make two functions from one cell value need help
QTY. is item quantity. B1 is the price C1 is the total after the calculation is complete, D1 is the final calculation multiplied by the quantity.
C3 is is the variable rate of exchange (USD )
D3 is the amount I want to subtract from the calculated total if the the price in B2 is in the range of 1-100,
D4 is the amount I want to subtract if the price in B2 is from 101-4000
I hope that one of these two possible ways to interpret what you said delivers the results you were seeking.
As you'll see, I used a VLOOKUP with a table to incorporate that reduction in profit; it wasn't clear to me, though, whether you really wanted that subtracted in the C column or in the D...either way, I think you'll see a different approach.
Reducing the price by 30 when the price is 101 makes it lower than the price at 100, which kinda doesn't make sense to me...... Granted, that's just a boundary issue, but something to be aware of if customers wanted to understand. I can imagine people saying, "Please charge me a higher price, so it'll cost me less."
- works perfectly much appeciated
Here's an example of what I was referring to in my last post, a working example that discounts the first $100 by 10%, anything above that by 30%. But you can change the table that contains those values and see the effect immediately. No need to rewrite the formula: I use "named ranges" in the formulas, to make them more readable.
- sorry for the late reply, i will try to incorporate the last formula you sent and keep you updated
here is the actual page Im working on so you can see what Im attempting to accomplish
It occurs to me that another approach would be to emulate how tax tables (in the US) work.
Take 10% off whatever portion of the total comes to up to $100, and then 30% off the portion of the total that exceeds $100.
So a $240 purchase (before discount) would become(100-10%)=90
(140-30%)=98
Total 188
To me, an approach like that makes more sense. Using percentages to calculate the discount, rather than dollar amounts, provides an incentive to customers to make larger single purchases. (I'd probably change the percentages to lower numbers; maybe 5% up to $100, 15% on the amount beyond $100. Putting those numbers into a table makes it easy to model "What-if?" without changing the formulas,