Formulas within a pivot table

A

attaboy

I am trying to create a formula within a pivot; an example of the data is as
below:

Name Value
William 0
Bob 0
William 1
William 1
Bob 0
(0 & 1 are used for Pass & Fail from some other test)
I need to calculate Pass percentage for the two guys. The expected result
would be:
William = 2/3=66.66%
Bob = 0/2 = 0%

I realise that this can be done using sum & count independently in the pivot
data and externally calculating the percentage, but is messy for a large data
range.

Is there a way of doing this using formulas within the pivot.

Thanks
 
E

Eddie Holder

Add your fields in the pivot table data as follows

Row = NAme
Data = passrate
Double click on the data elelement and choose the Options button
From the dropdwon below the options, choose Show data as % of column
Choose OK and complete the pivot table

Hope this helps
 
A

attaboy

Thanks, I tried this but does not seem to work. The result which came was
Bob - 0%
William - 100%

Am I missing something?
 
A

attaboy

Thanks, I tried this but am getting a different result.
Bob - 0%
William - 100%

Am i missing something?
 
J

JM

When you've got the dynamic table created, press the right button over the
button which says something like "Count of...". Select "Field Config .." (I'm
translating into english because my version is spanish), then select
"Average" instead of Count.
 
A

attaboy

Thanks, it works perferctly.

Need another help. If i have two columns of data like

Name Value1 Value2
William 0 0
Bob 0 0
William 1 1
William 1 1
Bob 0 1

With a pivot, i get two result columns (using average); is there a way of
multiplying the two averages of value 1 & value 2 (in a 3rd column) within
the pivot table itself ?

The expected result will be:
Average of Value1 Average of Value 2 Calculated field
Bob 0 0.5
0
William 0.67 0.67
0.44
Grand Total 0.4 0.6
0.24

To repeat the question - How to get the "calculated field" within the pivot.

Sorry if the query looks very long.
 
Top