Forum Discussion
Weight cost formula
From my point of view Riny_van_Eekelen gave right solution, it's better to keep transportation fee depends on weight in helper cells. From maintenance point of view that's much easier, any correction could be done in one place only, not in each formula.
But if you what to hardcode it that could be like
=D14+LOOKUP(B14,{0,1000,2000,3000,4200,15000,19000,40000,60000},{22.5,41.25,41.25,42.5,45.75,46.5,71.5,85.5,85.5})or, if to use only your range
=D15+LOOKUP(B15,--LEFT($F$4:$F$12,SEARCH("-",$F$4:$F$12)-1),$E$4:$E$12)where the weight is in B14 or B15.
Both are in attached.
Hello Riny_van_Eekelen
No this doesn't answer the question. I need a formula that should be in the Total selling price column and take the transport fee and weight into consideration. Like C4 = D4+E4(transport fee) when weight is 1-1000.
C5 = D5+E5(transport fee) when weight is 1000-2000 and so on...
How can this be done???
Sorry, but I don't get it. Assuming you don't just want to enter =D4+E4 in cell C4. Thought you wanted to calculate the total price as 10 plus the transportation cost depending on the weight. That would make perfect sense.
Could you otherwise indicate what outcome you want to see in numbers. Then. I'm sure we can create a formula for you.
Hello Riny_van_Eekelen
Yes you are correct. "Thought you wanted to calculate the total price as 10 plus the transportation cost depending on the weight. That would make perfect sense."
I want to calculate total price as 10 plus the transportation cost depending on the weight. I set C4 just so people understands where to put the "=".
Do you understand what I want now?
My suggested solution did precisely that, I thought. The examples I gave were merely to demonstrate how to use the VLOOKUP function. For instance, when a package weighs 3250 grams the transportation cost is 42.50 (from the 3000-4200 weight group). But, for the VLOOKUP function to work I created an extra column that has the lower boundary of the weight groups as a number, as the function can not determine that the weight 3250 (being a number) falls in the group "3000-4200" being a text. the formula in D4 (my schedule) is =VLOOKUP(B4,G4:H12,2) finds the correct transport cost in the range G4:H12 based on the weight in B4.