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