1 From…To…Rev/Piece
2 1…49999…$0.185
3 50000…99999…$0.175
4 100000…149999…$0.165
5 150000…0.160
I am trying to build a formula that, for any given # of pieces, I will get the correct revenue figure. The formula is to determine which rate to bill by based upon the values of another cell (say, A16). For example, if A16=75,000 pieces, then I will bill 75,000*.175, but if A16=105,000 pieces, I will bill 105,000*$.165.

From my experience I’ll need nested IF/OR statements, but I keep blowing it up once I get to rows 3 and 4.

Note: The formula must be based upon the values within the cell references (which is why I put column letters and row #'s in the above) - I can’t hard-code it with the above values.

Is this even something that I can do formulaically (is that a word?), or am I going to have to create some vb code?

True, but when I showed the formula to my boss, he said “There’s not another person in the company who could even come close to doing that.” So the messiness actually had some personal benefit that a simpler formula wouldn’t have.

I’d like to suggest you use the LOOKUP function that references a table containing the break points and prices. The resulting formula is much easier to understand. Even you will have trouble understanding your nested IF statements 6 months from now. The look-up table will make it easier to accommodate future changes in breakpoints and prices. Put a huge fake quantity and price at the end of the table. If you insert or remove rows, the range referenced in the LOOKUP function will be adjusted automatically. For extra credit, put your look up table on a separate worksheet.

Agreed. That table screams for a LOOKUP function. Once you are a little familiar with the function it makes it a lot easier to verify that your formula is correct.