Tue, 19 Dec 2023 05:08:16 +0000 http://www.greenbuildingforum.co.uk/newforum/ Lussumo Vanilla 1.0.3 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298541#Comment_298541 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298541#Comment_298541 Wed, 02 Nov 2022 11:58:58 +0000 barge17 For years I have been keeping a monthly record of our usage on a simple spreadsheet, screenshot attached. But it messy because each month I have to start a new row four rows down from the last month's, then copy and paste the formulae into the middle row between them.

There must be a much tidier way of doing this. 20 years ago I used 'MS Works" and it had a database section where you could make a "card " or something which automatically transfered the figures to a spreadsheet, which could then in turn do some calculations and then generate graphs, etc

I would also like to include units such as Therms and KWH and £s to make the records more usable. I hope somone can point me in the right direction, please.

Nowadays I use either Dropbox or Google Sheets for this sort of thing and find them very good. thanks, D
.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298633#Comment_298633 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298633#Comment_298633 Fri, 04 Nov 2022 18:02:09 +0000 borpin
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).]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298655#Comment_298655 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298655#Comment_298655 Sat, 05 Nov 2022 16:46:07 +0000 barge17
Thanks, I double space them to make them easier to read. But how do you extract the consumptions to do things with them, eg make a graph, or take an average? because they always appear on a newly created alternate line. Possibly you set up the whole blank year in advance and fill in the readings as time goes on?
.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298656#Comment_298656 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298656#Comment_298656 Sat, 05 Nov 2022 22:18:17 +0000 philedge
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)]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298657#Comment_298657 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298657#Comment_298657 Mon, 07 Nov 2022 09:35:14 +0000 Doubting_Thomas
However, if you are more adept at programming, I understand those in the know often use Node-RED (https://nodered.org/) to post readings to Grafana (https://grafana.com/) which can make all sorts of clever dashboards.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298659#Comment_298659 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298659#Comment_298659 Mon, 07 Nov 2022 11:43:47 +0000 barge17 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.
.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298670#Comment_298670 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298670#Comment_298670 Mon, 07 Nov 2022 19:07:10 +0000 LF If you have data like this
A B C D E F ETC
Date water water cons Gas con ETC

So A has all dates, C has all water consumption

In excel, I use charts loads and have a simple method for getting them done quickly. Likely similar in web version.

Click on date column then click on the columns you want to graph.(Directly on A, B etc. in header bit
This will highlight the whole column,
( Or you can use ctrl and "wipe" over the data column by column till you have everything you want to chart)
Then Insert chart , xy scatter and it will plot for you.
You can move some series to right hand (secondary axis) if scales are very different

Move chart to separate sheet/tab - then copy sheet to get different cuts on the data - deleting some items if you do not want them.

Good luck]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298673#Comment_298673 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298673#Comment_298673 Tue, 08 Nov 2022 07:58:21 +0000 barge17 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
.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298674#Comment_298674 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298674#Comment_298674 Tue, 08 Nov 2022 08:12:28 +0000 barge17 So in my screenshot at the top of this thread, the first two rows are fixed and always show at the top of my sheet even though I am now as far as row 438.

If I delete the heading from those rows it will be awkward to be sure of filling in the readings in the correct cell. Or can I just use the data I want below the headings and tranfer it to the next sheet for processing?
.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298678#Comment_298678 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298678#Comment_298678 Tue, 08 Nov 2022 10:26:00 +0000 LF 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.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298681#Comment_298681 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298681#Comment_298681 Tue, 08 Nov 2022 11:12:48 +0000 barge17 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298697#Comment_298697 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298697#Comment_298697 Wed, 09 Nov 2022 16:34:58 +0000 borpin
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 ]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298698#Comment_298698 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298698#Comment_298698 Wed, 09 Nov 2022 16:35:13 +0000 borpin http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298731#Comment_298731 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298731#Comment_298731 Thu, 10 Nov 2022 17:52:16 +0000 barge17 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298866#Comment_298866 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298866#Comment_298866 Thu, 17 Nov 2022 06:46:06 +0000 barge17
Having charts on new sheets is a transformation!

A couple things I can't just find a solution for:
 Trying to customize the chart, I can't find how I got the label "blue" on to the dashed blue line, and it's not easy to see which column of figures each line relates to.

Future charts will not have as many lines, there will be multiple charts with different data sets soon.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298872#Comment_298872 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298872#Comment_298872 Thu, 17 Nov 2022 09:30:23 +0000 philedge http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298875#Comment_298875 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298875#Comment_298875 Thu, 17 Nov 2022 13:10:15 +0000 LF In excel you click on the series of data you want to change the colour and format of. So say the orange line. One click only. Then right mouse then format data series.

If numbers very large for one series can move them to secondary (right hand axis) to let you see the smaller data easier. this is on format data series on excel as well.

If you delete the rows between the data labels at top and the actual data and include the data label in the selection of data/numbers it will bring it in. Or go to edit data and you can normally find it in there.



Keep working on your aster chart and then all the formatting will be there. Then copy the sheet with the master chart - expect google is same as excel. Click on the series/lines you want to delete and hit delete and they are gone from chart and legend.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298911#Comment_298911 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298911#Comment_298911 Sat, 19 Nov 2022 08:53:15 +0000 barge17 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.
.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298915#Comment_298915 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298915#Comment_298915 Sun, 20 Nov 2022 12:29:41 +0000 borpin Posted By: barge17gas readings to KwhkWh - Watt is a person so it is a capital.

Sorry, just one of those things that bugs me and yes, it's a hill I'd die on.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298917#Comment_298917 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298917#Comment_298917 Sun, 20 Nov 2022 13:28:25 +0000 WillInAberdeen
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.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298926#Comment_298926 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298926#Comment_298926 Mon, 21 Nov 2022 14:06:27 +0000 barge17 Point taken about the units, thanks.

Looking at our gas bills, I couldn't work out how they had done their conversion from M3 to kWh but, for the time being, I have worked back the calculation from this web site which means I am multiplying the cubic meters by 32.156307 to get the kWh. https://www.theenergyshop.com/guides/how-to-convert-gas-units-to-kwh]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298937#Comment_298937 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298937#Comment_298937 Mon, 21 Nov 2022 23:04:29 +0000 LF Just looked at link, think you have the imperial number.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298967#Comment_298967 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298967#Comment_298967 Wed, 23 Nov 2022 06:36:45 +0000 barge17 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298992#Comment_298992 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=298992#Comment_298992 Fri, 25 Nov 2022 07:01:55 +0000 borpin 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.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299338#Comment_299338 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299338#Comment_299338 Thu, 22 Dec 2022 12:37:19 +0000 barge17 Further down the line, I can now manage charts for existing data, but I have been trying to make a chart ready for a longer time period, with blanks for data to come, but that means the formulas are not happy with no zeros allowed, etc.

Perhaps there is an elegant solution? But so far it escapes me, and It is a drag making a fresh chart each time I want one with up - to -date data. Meantime I shall colour the colums I want for the chart to remind me which ones to use.
.]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299342#Comment_299342 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299342#Comment_299342 Thu, 22 Dec 2022 16:14:58 +0000 philedge
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!!]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299348#Comment_299348 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299348#Comment_299348 Thu, 22 Dec 2022 17:44:48 +0000 WillInAberdeen http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299355#Comment_299355 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299355#Comment_299355 Fri, 23 Dec 2022 09:06:12 +0000 barge17 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299356#Comment_299356 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299356#Comment_299356 Fri, 23 Dec 2022 09:13:15 +0000 barge17 so I'm guessing I need two IFs in the cells affected? ( or an IF and an OR)]]> http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299363#Comment_299363 http://www.greenbuildingforum.co.uk/newforum/comments.php?DiscussionID=17628&Focus=299363#Comment_299363 Fri, 23 Dec 2022 11:01:12 +0000 philedge
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!]]>