Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 -- Alwyas Trying Eddie "attaboy" wrote: 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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks, I tried this but does not seem to work. The result which came was
Bob - 0% William - 100% Am I missing something? "Eddie Holder" wrote: 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 -- Alwyas Trying Eddie "attaboy" wrote: 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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks, I tried this but am getting a different result.
Bob - 0% William - 100% Am i missing something? "Eddie Holder" wrote: 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 -- Alwyas Trying Eddie "attaboy" wrote: 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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "attaboy" wrote: Thanks, I tried this but am getting a different result. Bob - 0% William - 100% Am i missing something? "Eddie Holder" wrote: 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 -- Alwyas Trying Eddie "attaboy" wrote: 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 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "JM" wrote: 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. "attaboy" wrote: Thanks, I tried this but am getting a different result. Bob - 0% William - 100% Am i missing something? "Eddie Holder" wrote: 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 -- Alwyas Trying Eddie "attaboy" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
pivot table formulas for calculated field or calculated item | Excel Discussion (Misc queries) | |||
Excel 2k Pivot Table refresh scenario | Excel Discussion (Misc queries) | |||
pivot table | Excel Discussion (Misc queries) | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions | |||
Pivot table, dynamic data formula | Excel Discussion (Misc queries) |