Forum Discussion
trying to make two functions from one cell value need help
Can you describe in words what that formula is intended to do?
Frankly, I can't decipher your formula--I know how MAX and IF work, and the syntax for each--but the way you've laid this out is confusing to someone looking at it for the first time from the "outside":
- absence of clear labels (they'r no doubt clear to you, but not to the casual reader), so WHAT are each of the numbers representing?
- the formula in question (cell C2, right?) refers to cells to its left, its right, and beneath it
From the condition you describe, it sounds to me as if a table, with VLOOKUP, working with range lookup, would work more readily and more clearly than the IF function.
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.