Op 01.jun..2022 om 12:57 schreef NY:
I have a spreadsheet (xlsx format, Office 2007) which contains a list of dates (1 Jan 2019, 1 Feb 2019, ..., 1 May 2022) and corresponding
electricity and gas usage per day for each month. Calculated per day to standardise for months having different numbers of days.
(lists are in descending order of date because my wife prefers to see
the list (but not the graph) with the latest date at the top of the list)
(electricity kWhr/day, gas cu ft/day)
May 2022 12.2 1.7
Apr 2022 12.3 2.6
Mar 2022 12.6 3.4
Feb 2022 14.1 5.3
Jan 2022 14.8 7.1
Dec 2021 15.5 6.5
Nov 2021 14.0 5.1
Oct 2021 14.2 3.5
Sep 2021 13.5 2.2
Aug 2021 13.1 1.9
Jul 2021 11.6 1.6
Jun 2021 11.8 1.9
May 2021 11.5 3.7
Apr 2021 11.5 4.5
Mar 2021 12.5 5.0
Feb 2021 13.5 6.1
Jan 2021 15.2 7.9
Dec 2020 16.7 6.6
Nov 2020 14.0 4.8
Oct 2020 14.0 3.6
Sep 2020 13.8 1.9
Aug 2020 14.7 1.8
Jul 2020 18.3 2.1
Jun 2020 15.5 2.4
May 2020 14.6 2.8
Apr 2020 20.9 4.1
Mar 2020 15.9 5.8
Feb 2020 16.3 7.0
Jan 2020 17.9 7.0
Dec 2019 17.5 5.7
Nov 2019 14.6 6.1
Oct 2019 18.3 5.1
Sep 2019 13.4 2.0
Aug 2019 12.2 1.7
Jul 2019 11.2 1.4
Jun 2019 10.5 2.2
May 2019 8.1 2.1
The first column is a date field - eg "01/05/2022" in UK D/M/Y order - displayed as "mmm yyyy".
I have graphed these in ascending date order, but I'd like also to be
able to generate a list for the same calendar month for successive
years. For example
May 2022 12.2 1.7
May 2021 11.5 3.7
May 2020 14.6 2.8
May 2019 8.1 2.1
These can then be graphed, to show how usage has varied over the years
for any given month (when the outside temperature will *tend* to be
similar for successive years).
What's the best way to do it? Maybe with a cell where the user fills in
the required month, and a corresponding table of figures and hence graph
is generated.
The original list will gradually grow in size as each month passes - eg
in a month's time I'll be adding readings for June 2022.
I know I could manually generate 12 sheets (one per month) with the each year's readings for that month, and have 12 separate graphs. But that
needs a bit more ongoing maintenance.
I'm using Office 2007, though I have another PC that uses Office 365 if
you can only give advice for that.
create a new column in which you put the date of the last day of the
month concerned by adding 1 month to your date 01/mm/yy and deduct 1
day. Representation of those dates as mm/yy gives always the correct month.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)