Calc Spreadsheet Formula Help

[FoxySystems]

Hello Everyone,

I am after some help with Calc. I am new to using calc and I am using Libre Office Version: 5.4.1.2 (x64)
I do not even know if this is possible or not but I am trying to get calc to work out delivery prices based on weight. I have a column with all the weights listed for each of the products. Once the customer has selected all there products there is 2 cells that display the total cost and total weight
I want the total weight figure to be used to automatically input a delivery cost into a delivery cost cell using the delivery prices and weights on another sheet

I hope that makes scene. If this is possible please could you help me with what formula I will need to do this?

Many Thanks!

[Lupp]

You surely don’t believe this is done by just “a formula”. You need to understand lookup methods. I would recommend a combination of MATCH with INDEX or (more flexible) with OFFSET.

An additional issue (imo) is that lookup tables as you need one for the delivery cost generally are given in a way assigning results to intervals which are low-side open and high-side closed. On the other hand matching in spreadsheets is always done top-down (or left-right). The “+1 modes” of MATCH or VLOOKUP e.g. as they work for technical reasons, are better fit for low-side closed, high-side open intervals in ascending order. I would therefore advise to shape the table in descending order and to use MATCH in “-1 mode”. (VLOOKUP doesn’t support this mode.)

See this attached demo.

[FoxySystems]

Not I did not think I could just use a formula to solve the problem, but I am new to calc and spreadsheets in general and did not know what else to call it

Thank you for you reply Lupp and for the attached spreadsheet demo it is much appreciated!

[mariosv]

If the answer solves your question please tick the .