Sorry I couldn't make the subject line any more precise, but I'm not even 100% sure how to word this. Here's my best shot. I have two columns that are being multiplied by one another to equal a third column. The third column is a qty and I need to create a 4th column that would have prices. The prices would be reliant on the qty in the 3rd column. ex.) a x b = 1-100 (c) the price in (d) = .10 but if a x b = 101-250 (c) the price in (d) = .08 etc. I would need 6 different prices in total for different. I then have column E which would be multiplied by the total qty in column C and the price in column D. Thank you in advance for any possible help on this issue. I've tried a couple of formula's that I found online and none of them have worked so far.

This formula will address the 2 conditions you gave in your example. =IF(B1>100,0.08,IF(B1>0,0.1))

Just expand the concept for more conditions. You'll note that I start with the "highest quantity range" and used "greater than" a value just below the lowest value in each range.

I do that because an IF statement will stop as soon as it finds a condition that is True. By starting with the highest range, it will check each range and work it's way down through the ranges until it finds a number that fits in that range.

For example, this will look at 4 ranges:

=IF(B1>499,0.06,IF(B1>249,0.07,IF(B1>100,0.08,IF(B1>0,0.1))))

P.S. I am assuming you are working with integer values, which is why you can use >.

If you are working with decimals (100.5) then you need to use >=101, not >100.

Hi, Create a table of rates like this:

K L 1 Qty $$ 2 1 0.10 3 101 0.08 4 251 0.06 5 501 0.04 6 1001 0.02In the first cell in column D (I used D2), alongside a quantity in column C enter this formula:

=C2*VLOOKUP(C2,$K$2:$L$6,2)

Drag the formula down, to extend it alongside the quantities in column C.The quantities will now be multiplied by the volume-related price.

Just to test that the lookup works 'as expected' use this in C2:

VLOOKUP(C2,$K$2:$L$6,2)Regards

Oh thank you so much. We've needed to have a quick quote tool for so long and I was just stuck on this one thing that is making is so much more simplified. I just have one additional question. Is it possible to make it so that the box doesn't say FASLE when there isn't any data in the cells that it's relying on.

Hi, Just wrap whichever formula you used in an IF test for an empty cell:

=IF(C2="","",C2*VLOOKUP(C2,$K$2:$L$6,2))If C2 is empty it returns ""

else it uses the selected formulaRegards

Thanks Humar, but I hate to admit I'm either having a dumb day or just way to many distractions at work for me to think straight. I used the solution that DerbyDad gave. Not sure if the solution still applies.

Actually, it's me that is having a dumb day. Humar's solution is actually better since it would make it easier to update your quantity ranges and/or prices in a single location without ever having to touch the formulas.

Any change to the table would automatically be picked up by every formula.

Hi, Oh yes, it applies:

=IF(B1="","",IF(B1>499,0.06,IF(B1>249,0.07,IF(B1>100,0.08,IF(B1>0,0.1)))))If you are using this formula in a large number of cells, using a table approach makes it easier to update prices, or quantity ranges. Change one table and all cells referencing the table will have been updated.

In either case you can extend the use of IF functions, say you wanted to give preferred customers a better rate you could do this:

=IF(C2="","",IF(X2="Preferred",C2*VLOOKUP(C2,$M$2:$N$6,2),C2*VLOOKUP(C2,$K$2:$L$6,2)))

Then the rate for preferred customers would come from an alternate table.This approach will work with DerbyDad03's formula as well.

Regards

Ask Your Question

Weekly Poll

Do you think ride and car sharing are the future of transportation?

Discuss in The Lounge

Poll History