Grow does not yet support the SUMPRODUCT spreadsheet function, so it can be difficult to calculate weighted averages.

However, using SQL, it is relatively easy.

In this example, we will be calculating the average price paid for supplies. We purchased a different number of the same object at various price points and we want to calculate the weighted average price. We have this data in a Google Spreadsheet.

After connecting our Google Spreadsheet (or you connect and select your data), the next step is to add a Master Report.

Once you have a master report, enter the SQL. The formula for weighted average in SQL, follows the general form of: SUM(number*weight)/SUM(weight) 

So in our case the SQL to calculate weighted average would be:

SELECT Price, `# bought`,  
(select SUM(Price*`# bought`)/SUM(`# bought`) FROM report1) AS result
FROM report1;

This gives us the result we want.

We are using a subquery to put the weighted average on each row. If you just wanted one number of it on your chart, you could use this SQL:

SELECT Price, `# bought`,  
(SUM(Price*`# bought`)/SUM(`# bought`)) AS result
FROM report1; 

Which would only give the result once.

And you are done! If you have any questions please email or chat in on the site.

Note: We are working on a feature that will allow you to calculate weighted averages without using SQL. It is not in the near future, but it is definitely on the roadmap.

Did this answer your question?