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