Excel Help - IF/OR/ELSE/AND statements

Factual Questions
1

I have the following table:

(columns)
A…B…C

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?

Thank you in advance for your help!

2

I should post here more often - I was inspired to beat the SDMB hoards so figured it out myself, through trial and error.

In case anybody is interested, here is my solution (using actual cell references, not the example references abpve):

=IF(D13<$J$5,D13*$L$4,IF(OR(D13>$J$5,D13<$K$5),D13*$L$5,IF(OR(D13>$J$6,D13<$K$6),D13L6,IF(OR(D13>$J$7),D13$L$7,0))))

Good times. :slight_smile:

3

There’s also VLOOKUP. If you have exact match off, I believe it finds the value that does not exceed the amount in the first column.

4

Yeah, after I posted I found that is an issue too… the above formula didn’t work if D13 = the values in my range columns so I had to modify it to:

=IF(D13<$J$5,D13*$L$4,IF(AND(D13>=$J$5,D13<=$K$5),D13*$L$5,IF(AND(D13>=$J$6,D13<=$K$6),D13*$L$6,IF(AND(D13>=$J$7),D13*$L$7,0))))

Essentially adding an equal sign behind every > or < in the formula.

Now I can model to my hearts content. :wink:

5

Great, John T! I’ve been having problems with conditionals also, may I PM you sometime for help?
(Conditionals just fascinate me!)
Jake

6

A lot less messy:


=VLOOKUP($D$1,$A$2:$C$5,3,TRUE)

Where D1 contains the quantity you are trying to look up.

Alternatively:


=OFFSET($C$1,MATCH($D$1,$A$2:$A$5,1),0)
7

Any time. Can’t promise my help will be worthwhile, but I’m always game for a challenge. :slight_smile:

8

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. :wink:

9

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.

10

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.