Calculate the difference between an entered number and a number two rows up in the same column

Dahzee · December 23, 2022, 9:23am

I’m new to spreadsheets and am trying to calculate the difference between two meter readings in a column.

To make it easier to read I have an empty row between each each amount in a column.

The difficulty I have is I don’t know how when I start a new entry in my column to tell Calc that the number two rows above in the column is the number I want to find the difference between to see how much gas/electricity I have used and to put the answer in a cell for example two columns across.

Obviously It is easy to put the formula in each time, but I would like to automate this (or is it format the column with a formula) so I just need to enter the amount in a new cell (two down from the last one) and it will do the calculation and give me the result.

Thanks in advance.

Lupp · December 23, 2022, 10:53am

Welcome.
You meanwhile got answers probably better than my commentr. Nonetheless I post this attaching the example I made:
disask85665DontTryEveryOtherRow.ods (94.7 KB)
Regard in specific the “listing-paper-suggestion”. It uses Conditional Formatting which will be new to you again, but is an oftzen very useful feature
///
New entry in A131 (column heading “FromMeter” e.g.) Formula in B131 (column heading “ChangeSinceLastEntry” e.g.) =A131-A130 .
This formula you can easily fill into the cells of column B upwards either by dragging with the mouser or using the menu path >Sheet>Fill Cells>Fill Up after having selected the cells in column B above row 131.
You can also fill it down, of course, but the result may not be completely as expected. See attachment for more details.

This is definitely a very bad idea produced again and again by beginners. If you want better guide for the eyes, you can either make your sheet looking like good old listing paper, or simply increase the row height by (say) 0.5 cm.

ajlittoz · December 23, 2022, 10:06am

There is a design flaw in your schema: you want to mix readings and computations.

Don’t try to outsmart Calc. Write down all your readings consecutively in a column. I assume the first cell of a row contains a date, at least for documentation purpose. Compute your differences in another column.

Your sheet should look like:


	A	B	C
1	Date	Reading	Difference
…
150	Nov 2022	1500	=B150-B149
151	Dec 2022	1600	=B151-B150

Designations like B150 are relative, meaning if you copy and paste them they will keep the same geometry relation as in the original location. On the contrary if you use $ before one of the coordinates ($B150, B$150 or $B$150), you “freeze” or constraint this coordinate to be hard wired to the initial row or column.

In your case, once you have written down the first formula, select the cell and extend the selection by dragging downwards. This will fill the cells with updated versions of the formula.

Hrbrgr · December 23, 2022, 10:14am

You should dispense with your blank lines and aim for a good data structure.

85665 HB Meter reading electricity.ods (29,1 KB)

ajlittoz · December 24, 2022, 9:52am

Thanks for the feedback, but site rules dictates than “answers” are reserved for effective answers. So please, delete your non-answerand repost your (appreciated) thanks as comments.