Calculate weighted averages correctly

Prompt that finds averages of averages and recalculates them with the right weights. Paste it into an AI tool of your choice, which then calculates the average correctly.

Topic: Spreadsheets & data
Type: Prompt
Level: Intermediate
Time: about 15 min

Results depend on the tool, the model and what you enter.

Result preview

Example: written by our editors, not real AI output.

| Branch | Avg price | Units | | A | €12.00 | 1,000 | | B | €20.00 | 10 | | C | €14.00 | 500 | Old (simple mean): €15.33 New (weighted by units): €12.71 Formula: =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4) Biggest influence: branch A

Fill in

What you type stays in this browser window. We store and send nothing.

MetricWhich number?

DataWhat does the data look like?

PurposeWhat do you need the value for?

Your prompt

Help me calculate an average in a spreadsheet or report correctly.
Metric: [Metric]
Data: [Data]
Purpose: [Purpose]
Steps: 1. Read the table and describe how the average is produced today. 2. Check whether averages or rates of individual groups are simply averaged although the groups differ in size. 3. Set the right weight, such as units for prices, visits for rates or responses for ratings, and explain why. 4. Recalculate the weighted value and put it next to the old one. 5. Show which groups cause the difference. 6. Write the formula so it can be used directly in a spreadsheet: sum of products divided by sum of weights.
Output: old and new value, chosen weighting with reason, formula and the three groups with the biggest influence.

Take particular care to weight by the volume the value really represents.

Before you answer, check: Are all instructions met? If key information is missing or a placeholder in square brackets is still in the text, ask instead of guessing. Mark assumptions as assumptions.

Help me calculate an average in a spreadsheet or report correctly.
Metric: [Metric]
Data: [Data]
Purpose: [Purpose]
Steps: 1. Read the table and describe how the average is produced today. 2. Check whether averages or rates of individual groups are simply averaged although the groups differ in size. 3. Set the right weight, such as units for prices, visits for rates or responses for ratings, and explain why. 4. Recalculate the weighted value and put it next to the old one. 5. Show which groups cause the difference. 6. Write the formula so it can be used directly in a spreadsheet: sum of products divided by sum of weights.
Output: old and new value, chosen weighting with reason, formula and the three groups with the biggest influence.

Take particular care to weight by the volume the value really represents.

Before you answer, check: Are all instructions met? If key information is missing or a placeholder in square brackets is still in the text, ask instead of guessing. Mark assumptions as assumptions.

This is an instruction for an AI, not the result itself.

Where this prompt works

These AI tools are a good fit for this prompt:

How to use it

Fill in the fields or tap a suggestion. Leave out names, addresses and other personal data.
Copy the finished prompt into an AI tool of your choice.
Paste the table with groups, values and, if available, volumes per group.

Why does this work?

The average of a shop with 10 sales and one with 1,000 is not a fair mean. Without weights, the small group gets as much say as the large one.

Tips

State in the report how you weighted, so others can recalculate the result.

You may use our prompts freely, including at work. Please do not republish them as your own collection. We give no warranty for AI results; check them yourself. Terms of use

Calculate weighted averages correctly

Result preview

Fill in

Your prompt

Where this prompt works

How to use it

Why does this work?

Tips

Also a good fit

Find cardinality explosions in metrics New

Estimate customer lifetime value simply New

Read a retention cohort table New