weighted average in pivot table

864 views
Skip to first unread message

Roger Govier

unread,
Jul 20, 2001, 12:05:06 PM7/20/01
to
Hi Axel

The inclusion of attachments is strongly discouraged within the Newsgroup,
because of the risks of a virus and because of the extra bandwidth used for
those who pay by the minute for their connection. Many people will not even
look at files with attachments. Please note for future postings.

However, I think your problem can be resolved if you add an extra column to
your data, called Weighted Average. Extend the range of your Pivot Table
data to include this new column.
This column should have the formula relating to Quality*Quantity only.
IN the Pivot Table, right click a field, and choose Formulas=>Calculated
Field. Create a Field called Average and insert the formula
=Weighted Average/Quantity
Include this field within your PT data area, as SUM of Average, formatted to
2 decimal places and you will find that you get the correct weighted
averages showing by Country and by Material.

Regards

--
Roger Govier
Technology 4 U
"Axel Scheithauer" <axel.sch...@otto.de> wrote in message
news:089101c1112a$50cc4ef0$97e62ecf@tkmsftngxs04...
> I have a table about the quantities ordered and the
> quality received with the following fields: country,
> material, product group
>
> Now I want a pivot table with the weighted average of the
> quality:
>
> sum(quality*quantity)/sum(quantity)
>
> Pivot-tables don't offer weighted average.
>
> In my example I always get medium quality 2, no matter how
> many high quality items I got.
>
> How can I get the real picture?


Richard Withey

unread,
Jul 21, 2001, 5:16:33 AM7/21/01
to
I'm looking for the same solution. I can do the
calculation externally to the Pivot Table but would like
to do it internally so that data can be refreshed.

Mohamed Alaa

unread,
Dec 1, 2021, 7:14:15 AM12/1/21
to
Hello All,
It's 20 years,
Did anyone know how to do this?
I wanna calculate weighted average through pivot tables,
is there is any solution to do it automated through pivot tables now, or should I calculate it manually same as 20 years ago.
Reply all
Reply to author
Forward
0 new messages