Calculate Year-over-Year and Month-over-Month Growth in Excel Without Misleading Percentages

You get a growth table, exact Excel formulas that handle zero and negative baselines and partial months, plain-language takeaways, and a list of percentages that would mislead.

Prompt

Prompt
You are a senior financial reporting analyst who builds growth reports in Excel.

I will paste my data right after this prompt: period labels and one or more metrics. Before answering, ask me up to 3 short numbered questions in one message, for example whether the latest period is complete and how my columns are laid out. Then wait for my answers.

Your job: calculate month-over-month and year-over-year growth correctly and explain what it means.

Rules:
1. Give exact Excel formulas using my real cell references, written so they fill down without errors.
2. If the baseline is zero, return "n/a" and show the absolute change instead. Never show a percentage calculated from zero.
3. If the baseline is negative or the sign flips, divide by the ABS of the baseline, flag it, and lead with the absolute change.
4. For partial periods, compare like-for-like days or a stated run rate. Never compare a partial month against a full one.
5. If the data looks seasonal, say so and treat YoY as the main signal. Label MoM swings that come from the season.

Output format:
1. Growth table: Period | Value | Abs change MoM | MoM % | Abs change YoY | YoY % | Flag
2. Formulas: each formula and the cell it goes in
3. What the numbers say: 3-5 plain bullets
4. Where a percentage would mislead: each case and what to report instead

End with one line listing your assumptions and any missing data.

Example output

At a glanceNet profit, latest 4 monthsMar 2024 is a run rate from 1,620 actual over 18 of 31 days
  • Dec 2023: 9,800
  • Jan 2024: 1,500
  • Feb 2024: 2,400
  • Mar 2024 (run rate): 2,790

Example for: a small online candle shop tracking monthly net profit from Dec 2022 to Mar 2024. The data is in rows 2-17, and March 2024 covers only 18 of 31 days.

1. Growth table (latest 4 months)

PeriodValueAbs change MoMMoM %Abs change YoYYoY %Flag
Dec 20239,800+3,500+55.6%+1,900+24.1%Seasonal peak
Jan 20241,500-8,300-84.7%+2,700+225.0%Sign change (Jan 2023 was -1,200)
Feb 20242,400+900+60.0%+2,400n/aZero base (Feb 2023 was 0)
Mar 2024 (run rate)2,790+390+16.3%+690+32.9%Partial: 1,620 actual over 18 days

2. Formulas

Layout: A = first day of month (a real date), B = net profit, C = days of data in that month.

  • D2 (adjusted value, fill down): =B2/C2*DAY(EOMONTH(A2,0))
  • E3 (abs change MoM, fill down): =D3-D2
  • F3 (MoM %, fill down): =IF(D2=0,"n/a",E3/ABS(D2))
  • G14 (abs change YoY, fill down): =D14-D2
  • H14 (YoY %, fill down): =IF(D2=0,"n/a",G14/ABS(D2))
  • I14 (flag, fill down): =IF(D2=0,"Zero base",IF(SIGN(D14)<>SIGN(D2),"Sign change",IF(ABS(D2)<500,"Small base",IF(C14<DAY(EOMONTH(A14,0)),"Partial",""))))

Format F and H as percentages with one decimal place.

3. What the numbers say

  • Profit is clearly seasonal. December peaks every year, so the -84.7% January drop is the season, not a collapse.
  • Every 2024 month beats the same month in 2023 in absolute terms, by +690 to +2,700.
  • December grew a solid 24.1% YoY on a meaningful base of 7,900.
  • March is on pace for about 2,790, roughly a third above last March, if the second half of the month matches the first.

4. Where a percentage would mislead

  • Jan 2024 YoY +225%: the base was a loss, so the percentage overstates the change. Report it as "moved from a 1,200 loss to a 1,500 profit (+2,700)."
  • Feb 2024 YoY: you cannot calculate growth from zero. Report "+2,400 vs break-even."
  • Jan 2024 MoM -84.7%: this is a seasonal comparison. Show it next to the YoY figure, or drop it from headline slides.
  • Mar 2024: quote the run rate only with the label "18 of 31 days." Late-month orders may not follow the early pace.

Assumptions: column A holds real dates, profit is in one currency, and there were no one-off items. I had no daily data, so I could not do a true like-for-like comparison of March 1-18.

Written by AI from this prompt. Your results will vary.

Tips and common edits

Why this works

  • Explicit rules for zero, negative and partial baselines stop the model from producing infinite or sign-flipped percentages that look valid.
  • Asking for formulas with your real cell references gives you something you can paste and fill down, not a generic example.
  • A dedicated section on misleading percentages makes the model say what to report instead, which protects you in front of stakeholders.

Use this when building a monthly report or checking growth figures before a meeting. Have your period and value columns ready to paste into ChatGPT, Claude or Gemini, and know whether the latest month is complete.

Common edits

  • Change the small-base threshold of 500 in the flag formula to a level that fits your metric's scale.
  • Ask for quarter-over-quarter growth or a 3-month rolling average to smooth out noisy monthly data.

Prompt details

By
Prompts-GPT editors
Published

Did this prompt work?

Keep exploring

More in Excel & Spreadsheets