I do much the same except it is all on one line (so your 'cons' calculations are next to the data).

In Excel select the whole row, Ctl-C, then right click on the selected line and 'Insert copied cells' - keeps all of the formulas correct.

Over the years I have done various dynamic ranges so I can get a rolling average for the last 365 days (it isn't always the same number of rows).

Its a long time since Ive used the graph/chart function but Im fairly sure you'd need your data in rows or columns which you dont have. To get the dates all in one column youd need to remove the "cons" in column A. If you want you consumption to be the other axis then you'd need to only have the consumption figures in column B or move the consumption figure to a new column C inserted between the water and gas columns.

I'm not sure what data youd want to average out but an obvious one would be your consumption per day rather than the consumption for a variable period which is somewhat meaningless when the period varies. To get the average per day for the period ending 1/11/2022, youd need the total consumption for the period divided by the number of days, so in cell B433 you would have =(B435-B431)/(A435-A431)

.
Thank you for your comments, I'll get back to this project soon and report here. I feel that Google sheets is very much like "Excel for the Web" which Dropbox uses, and also backs your work up on the web.

I had a quick look at Node-Red and it's forum, etc looks well used and friendly. Might have a dabble on there. Last year I was dabbling with Arduino programming and found it all very user unfriendly. However, I built a wood boiler controller with it on a Nano and it works resasonably well.
.

.
thanks LF, now I am starting to get it. You said "Move chart to separate sheet/tab - then copy sheet to get different cuts on the data" - I have never used separate sheets ( nor realised it was possible) and I have always wanted to keep my data in one screen width to avoid scrolling, etc.
.
So I can use as many sheets as required for different purposes, just keeping my sheet No 1 for the raw data input?
.
good do
.

On view can turn off "split screen" I think it is or unfreeze screen
Or can sometimes drag the bar with holding left mouse button down on the bars to the top of the page
For the vertical bar drag the left of the page.

You can keep the headings, these will be the test that comes up on the graph for the series (eg "Water cons")

Only reason for extra sheets is the graphs over the appear over the data - you can move them but easier in a separate sheet. Can then print chart
If you do all the data and set up nice axis and format,
I then copy this master to new tab/worksheet and remove some of the data series just by clicking on them and deleting. I am on office / excel 2010 so not latest versions.
Must be a youtube on drawing charts in your chosen spreadsheet.
I did a lot of data manipulation in previous days and excel could always do it rather than needing to go to databases.

You can link across worksheets if you have data on more than one sheet. press = then move to the point you want to use on the other sheet.

I'll post a couple of screenshots. It has grown in complexity over time and I calculate things like Annualised cost per kWh, Rolling Annual Cost, Use by month etc. Calculating the rolling figure is a little complex as I don't always remember to take the reading and if something happens (like new meters) you get an extra reading.

I also use number formatting to include the units (which then looks good on the graphs).

Key thing is all data has to be on consecutive rows - remember it is data it isn't a presentation format.

I recently added the ability to generate and display the historical Max and Min.

I Keep fiddling with it

thanks, Borpin, that's exactly what I am working towards........

Maybe the blue label is showing because thats the colour youre edditting on the right hand side of the page?? Try selecting a different colour to edit and see if a different label appears

.
Getting there slowly now, I have more or less got to grips with daily usage and the chart editing. I have extended the rows on sheet1 to include columns for the dailys and unit conversions, so now I need to convert the gas readings to Kwh - I'll get the conversion off the bill. Then make yet another chart.
.

I suspect barge17 knows that but their autocorrect was trying to be helpful, and moved the capital letter to the start of "kWh", at least mine kept on doing that.

My autocorrect also just tried to autocorrect "autocorrect", which it thought should be "AutoCorrect".

I prefer MJ as the unit of energy (also capitalised, but with lower case k or upper case G). kWh is a ridiculously unscientific unit, it's guaranteed to cause confusion between work and power, and whoever thought of using h obviously didn't check what the universal unit of time is.

But I worked with two theoreticians, one of them thought that all thermodynamic energy should fundamentally be expressed in electron-volts as that is what it really is. The other thought that mass and energy are equivalent and so energy should be measured in kg.

I gave up on being pedantic about units after that, it was just no fun anymore, so I am fine with whatever units anyone wants to use, and however they spell them, if the meaning is clear enough.

Hi. Conversion looks wrong. It is about 10 kWh per m3 of gas.
Just looked at link, think you have the imperial number.

Posted By: barge17Thanks LF, correct, I now have worked it out at 10.108 kWh per m3.

The calorific value changes (slightly) over time and is different in different parts of the country (IIRC). There *will* be an explanation of the calculation and the calorific value on your Bill.

You can use the IF function to do a test before you execute the formula thats generating errors, eg-

In column R youve got a value of 0.49 with an error under it. I dont know what formula youve got in that cell but let's say its doing something with the 0 in the cell to the left. In the cell with the error use the IF function to test if the value to the left is 0. If its 0 the result would be " " which displays nothing in the cell. If its anything other than 0 then run the current formula.

What Ive written will likely not make much sense until you read up on the IF function!!

More good advice thanks, I went for the easier option and copied in =IFERROR(A12-A11, "") and it worked immediately, but it threw up another issue, ie the negative numbers in row 13 which moves down as each day's meter readings are entered:

Those negative numbers were in your previous screen shot so not related to adding the IFERROR function.

When you paste in a screen shot make sure the cell youre talking about is selected and the formula in that cell is displayed at the top of the page- its a bit difficult to understand whats happening if you can't see the data. Also make sure the column and row references are in the screen shot- they are in the last shot but not the one before. Makes it so much easier to comment!