the field itself instead of calculated filed in Pivot Tables

[GN0001]

Hello to all,


I would like to get the cell itself in pivot table in stead of calculated fields. Is there anyway to get that?


Thank you,

Guity

 

[Luke M]

Guity,


I don't think you can reference a single cell in a Calculated field. I'd recommend checking out Debra's site, as she literally wrote the book on PivotTables.

http://www.contextures.com/excel-pivot-table-calculated-field.html

 

[GN0001]

Hello Luke,


I am stuck at work, so I thought somebody may have a response for it. What is weight Averages? Are they applicable @ pivot tables?


So, do I need to work with subtotals to find average?

Please advise,

Thank you so much for the answer.

Guity

 

[Hui]

Guity

A weighted average is where you have 2 variables and the average variable of interest is not simply the sum of one variable divided by the total, but is a weighted average based on both variables

eg:


I have 2 gold bars that have a content of 10g/t gold and 20g/t gold

The arithmetic average of the content of gold will be 15g/t gold: (10+20)/2


But if I now tell you that the 10g/t bar weighs 1kg and the 20g/t bar weighs 5 kg

the weighted average is:

[(10x1)+(20*5)]/6

= 18.33 g/t gold


A good way to do weigted averages is to use SumProduct, as it multiples the 2 variables and adds the result

You then need to divide by the sum of the other variable

eg:Average Gold Content =Sumproduct(Weight Range, Gold Range) / Sum(Weight Range)


It is worth noting that a variable may have more than 1 weighting variable and in this case you need to multiply all variables togther and dividen by the sum of the other vaiables multiplied together exluding the variable you are measuring

eg:

=Average Content =Sumproduct(Variable 1 Range, Variable 2 Range, Gold Range) / Sumproduct(Variable 1 Range, Variable 2 Range)