Convert Irregular Data to Monthly

A friend asked for help to approximate monthly electricity usage and/or spending using data from prepaid electricity purchases over irregular dates over a period of time. I have only worked on the spend since extrapolating to the usage would be easy and obvious.

The data contains:

• Date
• Price (including tax)
• Price (before tax)
• Amount of electricity purchased

I have attached my approach using MS Excel which involves allocating the purchases to months based on the days in between the purchases to extract monthly and quarterly usage. I believe I would have to ignore the first and last purchases because:

• The first is not from the beginning of the month
• There remains some unused electricity from the last purchase.

I would appreciate any comments on a better approach.

Additionally, I noticed there was no purchase in Oct 2022 and this shows as no purchase (or electricity use) which is obviously wrong. I tried to remedy this by manually entering zero values in months without purchases. {I did not succeed} Such cells are filled in with light green.

Thanks

Originally Posted by Glenn Kennedy

B2, copied down:
=SUMIFS(Data!B:B,Data!A:A,">="&A3,Data!A:A,"<="&EOMONTH(Analysis!A3,0))

H2, copied down:
=AVERAGEIFS(Data!E:E,Data!A:A,">="&G3,Data!A:A,"<="&EOMONTH(G3,0))

Delete the stuff in data from column G onwards. I think it was incorrect for column B, in any event. Manually add up the values for August and see.

Thank you for the elegant solution.

Electricity, which is continuous, while the purchases are made irregularly, can you think of a way to segment the data into monthly purchases/usage? For example, October 2022 would show zero use because there was no purchase in that month.