Convert randomly dated entries to monthly and/or daily total

[archipelag0]

I have a series of data entries (recording electricity usage in kWh) that were taken on various random days of the year.
I'd like to summarise this into monthly totals and ideally find a daily average for each month.

As an example:

Monday, December 28, 2020	5081
Saturday, January 09, 2021	5413
Monday, January 25, 2021	5543
Tuesday, February 09, 2021	5634
Tuesday, March 02, 2021	5847
Tuesday, March 09, 2021	5922

So for January, for example since the first data wasn't taken for a few days it would need to be reckoned backwards using the values from December. Likewise February could be determined from the average number of days in the month and the dates either side. Effectively it's 'chopping' an interpolated curve of points into monthly/daily slices.

I looked at SUMPRODUCT and EOMONTH but can't quite find the right way to do this.

Can anyone help?

Thanks in advance

 

[ebea]

Try write your dates, as in example here. Then it will be easier to sum your monthly total.

Mappe3
	A	B	C	D	E
1	Dates	values		Months	Total
2	28-12-2020	5081		01-12-2020	5081
3	09-01-2021	5413		01-01-2021	10956
4	25-01-2021	5543		01-02-2021	5634
5	09-02-2021	5634		01-03-2021	11769
6	02-03-2021	5847
7	09-03-2021	5922
Sheet1

Cell Formulas
Range	Formula
E2:E5	E2	=SUMIFS($B$2:$B$7,$A$2:$A$7,">="&D2,$A$2:$A$7,"<="&EOMONTH(D2,0))

 

[archipelag0]

@ebea thank you for the prompt response. What I should have said is that the total in the values (column B) is cumulative, so the figure in your example E3 is incorrect as it's a running total not a discrete amount. I want to figure out what the values have increased by on a daily and monthly basis.

 

[ebea]

@ebea thank you for the prompt response. What I should have said is that the total in the values (column B) is cumulative, so the figure in your example E3 is incorrect as it's a running total not a discrete amount. I want to figure out what the values have increased by on a daily and monthly basis.

In that case, it will be a bit tricky, as some months only have 1 reading, and other 2 or more. So a MAX-MIN solution between dates, would also not work, hence the readings you have. Maybe other have a better solution.

 

[ebea]

Try this, and see if it do what you want.

Mappe1
	A	B	C	D	E	F
1	Dates	values		Months		Total
2	28-12-2020	5081		01-12-2020	31-12-2020	462
3	09-01-2021	5413		01-01-2021	31-01-2021	91
4	25-01-2021	5543		01-02-2021	28-02-2021	288
5	09-02-2021	5634		01-03-2021	31-03-2021	0
6	02-03-2021	5847		01-04-2021	30-04-2021	0
7	09-03-2021	5922		01-05-2021	31-05-2021	0
Ark1

Cell Formulas
Range	Formula
F2:F7	F2	=MAX(IF($A$2:$A$7>=D3,IF($A$2:$A$7<=E3,$B$2:$B$7)-MAX(IF($A$2:$A$7>=D2,IF($A$2:$A$7<=E2,$B$2:$B$7)))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[archipelag0]

@ebea, thanks it certainly gives some figures in the total column, but I notice that for several months I get a zero value which clearly can't be the case.

Can you confirm what your formula is doing? I understand the IF statements are looking to see if the date of the record sits within a certain month (between dates in columns D & E), but I don't get how the MAX function is used here, or why some values are returned as zero.

 

[ebea]

@ebea, thanks it certainly gives some figures in the total column, but I notice that for several months I get a zero value which clearly can't be the case.

Can you confirm what your formula is doing? I understand the IF statements are looking to see if the date of the record sits within a certain month (between dates in columns D & E), but I don't get how the MAX function is used here, or why some values are returned as zero.

When it comes to zero values, it's because there's no data to calculate.

Yes, IF are as you write, to find the start and end of a month period. Max are to take the max value from previous month, and suctract the next month.

So as I do not knows your exact data, I can't write an exact period. So only you have your actual numbers, and can fill in these, for each month. And by doing so, I guess you will get rid of zero values.
This here, is only a proposal, out from your picture.