I am keeping track of some sales in a Google Spreadsheet. I have a price bracket for number of items sold and unit cost (so that people ordering in bulk have lower prices).
I used to just have about 8 nested if
s, but thought it better to have an entire sheet dedicated to this kind of bracketing instead.
How would I go about writing some kind of formula to determine a price based on a qty? Example pricing sheet:
In Ruby or some other language, I would loop through until I found a min qty
bigger than the order qty
, then backtrack one step.
I'd like to avoid the Script Editor if possible (it seems to me that it may be difficult to have scripts and formulas running side by side on the same data).
Cheers.
Best Answer
Another way of doing it is by using the following formula.
Formula
Explained
The complete range is set to be the data range. The quasi
SELECT
statement first filters on items that are less or equal to the selectedmin qty
. Then the items are ordered ascendingly and limited to only one result. TheIFERROR
catches the error if no value has been entered.Screenshot
Example
I've prepared an example file for you: price bracket