编写电子表格增长率计算公式
根据表格布局生成增长率与复合增长公式,处理零值、负基数和未完整期间,并附算例。
完整提示词
<context>
You calculate growth figures that end up in board packs and investor updates, where a wrong sign or an inflated percentage is embarrassing. Experts know the traps. Growth from a zero base is undefined, not infinite. Growth from a negative base flips sign (a loss going from -100 to -50 computes as -50% when it is an improvement), so it needs ABS in the denominator or, better, "n/m" (not meaningful) and the absolute change. A change in a rate (margin from 20% to 25%) is 5 percentage points, not 25%, and readers confuse the two. CAGR is (end/start)^(1/years)-1 where years is the number of intervals, not the number of data points, so five annual values span four years; Excel and Google Sheets both have RRI(periods, start, end) for it. The average of monthly growth rates is not the compound monthly rate and overstates growth when it is volatile. A partial current period compared with a full prior one is not growth at all.
App: {{app}}
<data_layout>
{{data_layout}}
</data_layout>
<growth_needed>
{{growth_needed}}
</growth_needed>
</context>
<task>
1. Define each requested figure precisely: numerator, denominator, number of periods, and whether it is a percentage or a percentage-point change. Identify which measures are themselves rates or ratios.
2. Write the formulas, locked for filling across or down: simple change, percentage change, percentage-point change for rates, CAGR with RRI or the power formula, and compound average period growth if asked. Put the period count in a cell or compute it from dates, never hard-code it.
3. Handle the bad bases inside the formula: return "n/m" (or blank) when the base is zero or negative and show the absolute change instead; explain the choice. If the user insists on a percentage from a negative base, use ABS(base) in the denominator and label it.
4. Check for partial periods (a current month or year not finished) and give a like-for-like option: the same days of the prior period, or a run-rate clearly labelled as an estimate.
5. Work through at least four examples from the user's data or realistic values: a normal case, a zero base, a negative base, and a CAGR, showing the arithmetic.
6. Suggest how to show it: the growth figure beside the absolute values, one decimal place, and "pp" for percentage points.
</task>
<constraints>
- Never present growth from a zero or negative base as an ordinary percentage without a label.
- Do not average percentages across items of different size to get a total growth rate; compute growth on the totals.
- Use only functions available in {{app}}; use English-locale commas and note that some locales use semicolons.
- Do not judge whether the growth is good or bad for the business unless asked; the job is correct numbers.
- If the values look like they changed definition or scope between periods (a jump caused by an acquisition, a new product line), flag it as a question rather than ignore it.
</constraints>
<output_format>
## Definitions
One line per figure.
## Formulas
Each in a code block with its target cell.
## Worked examples
Table: start | end | periods | formula result | comment.
## Cases that need a different number
Bullets: zero base, negative base, rates, partial periods, changed scope.
## How to present it
Three to five bullets.
</output_format>可替换内容
- {{app}}
- {{data_layout}}
- {{growth_needed}}
分类与标签
办公效率、电子表格、增长率计算、复合增长率、异常基数处理