Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

How to get a Weighted Average instead of an average of averages.

Brass Contributor

Hi, all. I am wanting to get a weighted average instead of an average of averages. See my excel example attached. 

 

How would one go about getting a weighted average instead of an average of averages?

 

Thanks!

 

 

4 Replies

@slohombre : Like this:

SELECT 
    Week, 
    Yield, 
    Acres, 
    [Yield]/[Acres] AS YieldPerAcre, 
    (Select Sum(Acres) From Crop) AS TotalAcres, 
    (Select Sum(Yield) From Crop) AS TotalWeight, 
    [TotalWeight]/[TotalAcres] AS WeightedAverage
FROM 
    Crop;

Output:

crop.png

Many thanks to both @slohombre and @Gustav Brock for providing such clear explanations.

One point possibly worth mentioning.

It would be better to display the total weight(32) & total acres (19.5 not 20) as e.g Double numbers rather than integer.

The weighted average is correctly calculated as 32/19.5=1.641 rather than 32/20=1.6.

However, when doing aggregate queries, Access may not correctly calculate expressions based on other calculated expressions. Sometimes you will need to run the query twice for the correct results

I would therefore recommend either calculating the weighted average as a separate query or using the actual expressions for both Total Weight and Total Acres in the Weighted Average expression.

 

SELECT 
    Week, 
    Yield, 
    Acres, 
    [Yield]/[Acres] AS YieldPerAcre, 
    Sum(Acres) AS TotalAcres, 
    Sum(Yield) AS TotalWeight, 
    Sum(Yield)/Sum(Acres) AS WeightedAv
    FROM Crop;

@isladogs It's a question about formatting. You can have two decimals all over, if you prefer so:

 

crop2.png

@Gustav Brock
Yes I realised that but hopefully you can see why I mentioned it.
However, my main point was about reusing calculated expressions in another field in the sql