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.

Another way of doing it is by using the following formula.

## Formula

``````=IFERROR(QUERY(A2:C8, "SELECT C WHERE B <=" & E2 & "ORDER BY C ASC LIMIT 1"),"")
``````

## 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 selected `min qty`. Then the items are ordered ascendingly and limited to only one result. The `IFERROR` catches the error if no value has been entered.

## Example

I've prepared an example file for you: price bracket

• I like the query solution. What is iferror? Commented Apr 3, 2014 at 1:39
• @tekknolagi The `IFERROR` catches the error if no value has been entered. Commented Apr 3, 2014 at 12:11
• So it just is blank if there's an error? Commented Apr 3, 2014 at 19:37
• @tekknolagi Why don't you meet me in this chat room: chat.stackexchange.com/rooms/13056/spreadsheet-nerds Commented Apr 3, 2014 at 19:51

Actually, I figured this one out:

``````=if(isblank(C3), "", VLOOKUP(C3, Prices!\$A\$2:\$B\$8, 2, true))
``````

Where `C3` is the quantity sold, and `A2:B8` is the pricing chart range. `2` is the column index (1-indexed), which is in this case `unit price` (I took out the `id` column).

The `true` indicates that my list is already sorted.

I check if blank so that I don't have 0s all over the place.

• +1; Nice formula. Clue to your formula is the `TRUE` statement in the `VLOOKUP` formula. Perhaps you can elaborate on that? Commented Apr 2, 2014 at 22:20
• @JacobJanTuinstra done! Commented Apr 3, 2014 at 1:37