Forum Discussion

slohombre's avatar
slohombre
Brass Contributor
Mar 29, 2021

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

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!

 

 

access
  • GustavBrock's avatar
    GustavBrock
    MVP
    Apr 04, 2021

    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:

    • isladogs's avatar
      isladogs
      MVP
      Apr 04, 2021

      Many thanks to both slohombre and GustavBrock 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;

Resources