Spreadsheet help !

[AE-NMidlands]

Think I complicated it by not taking regular readings ! sorry ........

but if you just use the simple calculation (reading this month minus reading a year - or 2 - ago) / (date of this month's reading minus the date of the older one) it doesn't matter.
You can easily alter the start date to change the period you are looking at. I have monthly base data plus figures for 1, 2 and 3-year averages, the seasons being so variable nowadays.
It's nice to see a 3-year moving average dropping steadily... or is that just Global Warming?
A

[Stinsy]

The problem was the readings weren’t regular, sometimes there were several readings in a week and other times there were many months between readings. I built a function that looked back to the previous reading closest to 365 days ago then work out the difference between that and the current reading and divide by howevermany days between the two dates.

If all the readings were evenly spaced I don’t think he’d have asked for help!

[John_S]

The problem was the readings weren’t regular, sometimes there were several readings in a week and other times there were many months between readings. I built a function that looked back to the previous reading closest to 365 days ago then work out the difference between that and the current reading and divide by howevermany days between the two dates.

If all the readings were evenly spaced I don’t think he’d have asked for help!

Spot on Stinsy. Hence my warning to Ecogeorge thar periodic would complicate things. Your solution was what I had in mind.

Glad you could help.

John

[AE-NMidlands]

The problem was the readings weren’t regular, sometimes there were several readings in a week and other times there were many months between readings. I built a function that looked back to the previous reading closest to 365 days ago then work out the difference between that and the current reading and divide by howevermany days between the two dates.

If all the readings were evenly spaced I don’t think he’d have asked for help!

Why build a function? If you just do the meter number in one row minus the number in the row for however long ago you want (adjusted manually if needed) divided by the difference in the dates you get your long-term average. Frequent data or big gaps are irrelevant.
I think I changed to this method when I had 2 readings each month that the tarriff changed half way through.
A

[Stinsy]

It needs to figure out how many rows up it needs to look. It could be 3 rows up is could be 30 rows up!

If the readings were weekly you’d always be looking 52 rows up. If readings were monthly you’d always be looking 12 rows up.

But sometimes there were several readings in a week and sometimes there were many months between readings.

Like I said earlier, if it was as simple as you’re imagining he wouldn’t have needed help.

[ecogeorge]

https://camelot-forum.co.uk/phpBB3/ucp. ... &f=0&p=135#
This may be a link to Stinsy ammended spreadsheet .....thanks again.
George

[Stinsy]

This is the correct link:

Code: Select all

 https://docs.google.com/spreadsheets/d/13T11xgmkbjePrFsiTJFGPf8wGUoLx0hI5cjKEjNXbws/

[Stinsy]

BTW this is the formula I used:

Code: Select all

=(D51-indirect("D"&row()-(countifs($A:$A,">="&($A51-365),$A:$A,"<="&($A51)))))/($A51-indirect("A"&row()-(countifs($A:$A,">="&($A51-365),$A:$A,"<="&($A51)))))

If you think there is a more elegant method, let me know...