I need to find the Cumulative YTD $ and % variance of actual current month profit compared with pre-filled monthly target in Excel:
| B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|
| Month | Monthly Target | YTD Actuals | Variance ($) | Variance (%) | Cumulative Variance ($) | Cumulative Variance (%) |
| January | $100,000 | $110,000 | $10,000 | 10% | $10,000 | 10% |
| February | $120,000 | $125,000 | $5,000 | 4% | $15,000 | 6.8% |
| March | $125,000 | $90,000 | ($35,000) | (28%) | ($20,000) | (5.8%) |
| April | $115,000 | $105,000 | ($10,000) | (8.7% | ||
| May | $120,000 | $125,000 | $5,000 | 4.2% |
Column C is already pre-filled for the whole year. I need columns G and H to calculate the cumulative YTD variance each month without columns E or F which is only included for explanation. Each month's variance, should calculate what the total YTD should be vs what it actually is. For instance, March Cumulative Variance ($) is Jan - March monthly target summed ($345,000) minus Jan - March YTD Actuals summed ($325,000). Difference YTD being -$20,000.
Is there a simpler way than cell formulas being:
- G2 =sum(C2-D2)
- G3 =sum(C2:C3)-(D2:D3)
- G4 =sum(C2:C4)-(D2:D4)
Where the same formula can apply to each cell but yield their respective results based on the month?
CodePudding user response：
Can you calculate the cumulative variance by cumulating the variances?
G2 =Sum(E$2:E2)
Then copy and paste down column G - the $ will anchor the start of the range to row 2:
G13 =Sum(E$2:E13)