Weight cost formula

[ClauP]

I need help to create a formula that must spit out a price after taking weight into consideration.

I don't know how to produce this formula and would appreciate some help.

I attach the file and you can see what is needed. Can some good expert help with this?

@Sergei Baklanmaybe :)

[#2]

@ClauP 

See attached and see if this answers your question. The shaded areas is what i added.

[#3]

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???

[#4]

@ClauP 

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.

[#5]

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?

[#6]

@ClauP 

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.

[#7]

Hello @Riny_van_Eekelen 

Mmm, I understand but it is not a complete solution how I see it. There are now two tables and you put the formula of Vlookup in the transport column. I want a single solution using one table and one column with focus on the column with total selling price. To me the Vlookup doenst quit fix it. Any other suggestions?

Can maybe @Sergei Baklan help as well?

[#8 · Accepted solution]

@ClauP 

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.

[#9]

@Sergei Baklan 

I have a similar question but the formula must be based so it spits out a price value depending on weight in each row. I haven't been able to find this on youtube or forum. Can you?

I have attached a file "weight formula and price in each row".