Forum Discussion
trying to make two functions from one cell value need help
hello, attempting to solve issue with a cell
what i need is a value calculated from a range of 01-100 but a differnt calculation from the same cell if the range is 101-4000
please see attached file for a better description.
13 Replies
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.
- yes sorry the formula is intended to provide pricing on 1 item but profit to be calculated at a lower amount if the purchase price is lower than 100.
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-4000I 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."
