Aug 27 2021 08:31 AM
Hi,
I collate monthly consumption data in an excel workbook and want to show the progressive YTD % variance between data from different years but, =Sum(X+Y-A+B)/A+B and so on does not want to play ;)
Aug 27 2021 08:46 AM
@Paul_bentham What is it that doesn't work as expected? Can you indicate where you would want to see what? And why?
Aug 28 2021 06:54 AM
@Riny_van_Eekelen Hi Riny,
In column "N" I'd like to show the accumulative, Year to date Variance in consumption shown in Column "L" against the same periods in 2019, Column "C". Example at Cell "N7" shows the variance for the single month of April 2021 v April 2019.
In Cell "N8" I'd like to show the variance of the consumption used in April and May 2021 (Cells L7 + L8) against the same two months in 2019 (Cells C7+C8) and show the variance in cell "N8".
In Cell "N9" should continue the above but include the total usage/consumption of April, May and June 2021 v the same 3 months in 2019.
Cells N10, N14, N18 and N22 already show the quarterly variance year on year so you can ignore those cells .
Hope this makes sense and you can assist.
many thanks.
Aug 28 2021 07:59 AM
To sum months by quarters and for the year use SUBTOTAL() instead of SUM()
it will ignore other subtotals within range. To calculate percent
=(SUBTOTAL(9,$L$7:L7)-SUBTOTAL(9,$C$7:C7))/SUBTOTAL(9,$C$7:C7)
and drag it down. Please check in attached.