Formula to calc YTD % variance on a month by month basis

%3CLINGO-SUB%20id%3D%22lingo-sub-2692878%22%20slang%3D%22en-US%22%3EFormula%20to%20calc%20YTD%20%25%20variance%20on%20a%20month%20by%20month%20basis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2692878%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20collate%20monthly%20consumption%20data%20in%20an%20excel%20workbook%20and%20want%20to%20show%20the%20progressive%20YTD%20%25%20variance%20between%20data%20from%20different%20years%20but%2C%20%3DSum(X%2BY-A%2BB)%2FA%2BB%20and%20so%20on%20does%20not%20want%20to%20play%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2692878%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2692935%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20calc%20YTD%20%25%20variance%20on%20a%20month%20by%20month%20basis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2692935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1139559%22%20target%3D%22_blank%22%3E%40Paul_bentham%3C%2FA%3E%26nbsp%3BWhat%20is%20it%20that%20doesn't%20work%20as%20expected%3F%20Can%20you%20indicate%20where%20you%20would%20want%20to%20see%20what%3F%20And%20why%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2695148%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20calc%20YTD%20%25%20variance%20on%20a%20month%20by%20month%20basis%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2695148%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BHi%20Riny%2C%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20column%20%22N%22%20I'd%20like%20to%20show%20the%20accumulative%2C%20Year%20to%20date%20Variance%20in%20consumption%20shown%20in%20Column%20%22L%22%20against%20the%20same%20periods%20in%202019%2C%20Column%20%22C%22.%20Example%20at%20Cell%20%22N7%22%20shows%20the%20variance%20for%20the%20single%20month%20of%20April%202021%20v%20April%202019.%3C%2FP%3E%3CP%3EIn%20Cell%20%22N8%22%20I'd%20like%20to%20show%20the%20variance%20of%20the%20consumption%20used%20in%20April%20and%20May%202021%26nbsp%3B(Cells%20L7%20%2B%20L8)%20against%20the%20same%20two%20months%20in%202019%20(Cells%20C7%2BC8)%20and%20show%20the%20variance%20in%20cell%20%22N8%22.%3C%2FP%3E%3CP%3EIn%20Cell%20%22N9%22%20should%20continue%20the%20above%20but%20include%20the%20total%20usage%2Fconsumption%20of%20April%2C%20May%20and%20June%202021%20v%20the%20same%203%20months%20in%202019.%3C%2FP%3E%3CP%3ECells%20N10%2C%20N14%2C%20N18%20and%20N22%20already%20show%20the%20quarterly%20variance%20year%20on%20year%20so%20you%20can%20ignore%20those%20cells%20.%3C%2FP%3E%3CP%3EHope%20this%20makes%20sense%20and%20you%20can%20assist.%3C%2FP%3E%3CP%3Emany%20thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies

@Paul_bentham What is it that doesn't work as expected? Can you indicate where you would want to see what? And why?

@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.

@Paul_bentham 

To sum months by quarters and for the year use SUBTOTAL() instead of SUM()

image.png

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.