I have data from a system in a SQL database.
Via a ODBC, I connect Excel to the tables in de SQL database.
One of the tables has the following columns: Datetime, Product No., quantity, average and standard deviation.
To produce a report I use a pivot table, so far so good.
Now I would like to show results of the individual lines at the bottom of a row. This would be the sub or grand total.
But a sub or grand total of a average or standard deviation is not correct, only the total of Quantity would be correct.
Is there a way to calculate the weight average on the sub or grand total line at the bottom of a pivot table?
The weighed Average would be line 1 N x Avg + line 2 n x Avg etc / total n.
If I have the solution for the average I also have the solution for the standard deviation, but this formula is a bit more complex.
Bookmarks