Ask Your Question

Calc Spreadsheet Formula Help [closed]

asked 2017-09-25 20:40:41 +0100

FoxySystems gravatar image

Hello Everyone,

I am after some help with Calc. I am new to using calc and I am using Libre Office Version: (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! :)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by FoxySystems
close date 2017-10-04 20:24:23.617906

1 Answer

Sort by » oldest newest most voted

answered 2017-09-26 00:50:32 +0100

Lupp gravatar image

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.

edit flag offensive delete link more


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! :)

FoxySystems gravatar imageFoxySystems ( 2017-09-26 10:27:44 +0100 )edit

If the answer solves your question please tick the ✔.

m.a.riosv gravatar imagem.a.riosv ( 2017-09-26 11:37:45 +0100 )edit

Question Tools

1 follower


Asked: 2017-09-25 20:40:41 +0100

Seen: 84 times

Last updated: Sep 26 '17