weighted average in pivot table

[nasser]

Can I get the weighted average in pivot table?

[Gregg Riemer]

You need to add a calculated field to the report.

1.Click the report.

2.On the PivotTable toolbar, click PivotTable, point to Formulas, and then
click Calculated Field.

3.In the Name box, type a name for the field.

4.In the Formula box, enter the formula for the field.
To use the data from another field in the formula, click the field in the
Fields box, and then click Insert Field. For example, to calculate a weighted
average, you could enter = [Variable 1] * [Weight Variable].

5.Click Add, and then click OK.


"nasser" wrote:

> Can I get the weighted average in pivot table?

[nasser]

Many Thanks Gregg;

I tried but still not finished, Can you give me more details?
I have quantity and price and want to have weighted average
Data
Product Sum of Delivered Qty Average of FOB price / MT
A 2,771 1,205
B 696 1,037
C 30,435 1,198


"Gregg Riemer" wrote:

> You need to add a calculated field to the report.
>
> 1.Click the report.
>
> 2.On the PivotTable toolbar, click PivotTable, point to Formulas, and then
> click Calculated Field.
>
> 3.In the Name box, type a name for the field.
>
> 4.In the Formula box, enter the formula for the field.
> To use the data from another field in the formula, click the field in the
> Fields box, and then click Insert Field. For example, to calculate a weighted
> average, you could enter = [Variable 1] * [Weight Variable].
>
> 5.Click Add, and then click OK.
>
>
> "nasser" wrote:
>
> > Can I get the weighted average in pivot table?

[Andrew C]

Nasser,

Gregg's post worked perfectly for me. In the formula box you would simply
divide the 2 columns provided you have the appropriate columns and rows in
your pivot table. For example, I wanted the weighted time supply average by
ABC class for each warehouse based on the number of items sold and on hand
inventory.

I set this up in the pivot table wizard the following way:

ABC class was a row
Warehouse was a column
Weighted average was a calculated field in the field box (I divided on hand
by sales)

--
Andrew C


"nasser" wrote:

> Many Thanks Gregg;
>
> I tried but still not finished, Can you give me more details?
> I have quantity and price and want to have weighted average
> Data
> Product Sum of Delivered Qty Average of FOB price / MT
> A 2,771 1,205
> B 696 1,037
> C 30,435 1,198
>
>
> "Gregg Riemer" wrote:
>
> > You need to add a calculated field to the report.
> >
> > 1.Click the report.
> >
> > 2.On the PivotTable toolbar, click PivotTable, point to Formulas, and then
> > click Calculated Field.
> >
> > 3.In the Name box, type a name for the field.
> >
> > 4.In the Formula box, enter the formula for the field.
> > To use the data from another field in the formula, click the field in the
> > Fields box, and then click Insert Field. For example, to calculate a weighted
> > average, you could enter = [Variable 1] * [Weight Variable].
> >
> > 5.Click Add, and then click OK.
> >
> >
> > "nasser" wrote:
> >
> > > Can I get the weighted average in pivot table?