# Need a cell that relies on a cell with range Microsoft Excel 2003 (full product)
August 30, 2010 at 10:20:01
Specs: Windows XP
 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. See More: Need a cell that relies on a cell with range

#1 August 30, 2010 at 10:56:17
 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.

Report •

#2 August 30, 2010 at 11:14:22
 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.02 ```In 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

Report •

#3
August 30, 2010 at 11:20:43
 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.

Report •

Related Solutions

#4 August 30, 2010 at 11:28:53
 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

Report •

#5
August 30, 2010 at 11:33:46
 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.

Report •

#6 August 30, 2010 at 11:47:28
 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.

Report •

#7 August 30, 2010 at 11:47:36
 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

Report • 