insert calculation in to pivot table results

[skinpup]

[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]Count of client
[/TD]
[TD="class: td1"]Count of attended
[/TD]
[TD="class: td1"]Count of FTA
[/TD]
[TD="class: td1"]

[/TD]
[TD="class: td1"]Sum of Field1
[/TD]
[/TR]
[TR]
[TD="class: td2"]498
[/TD]
[TD="class: td2"]327
[/TD]
[TD="class: td2"]171
[/TD]
[TD="class: td2"]

[/TD]
[TD="class: td2"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: td2"]741
[/TD]
[TD="class: td2"]516
[/TD]
[TD="class: td2"]225
[/TD]
[TD="class: td2"]

[/TD]
[TD="class: td2"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: td2"]243
[/TD]
[TD="class: td2"]186
[/TD]
[TD="class: td2"]57
[/TD]
[TD="class: td2"]

[/TD]
[TD="class: td2"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: td2"]326
[/TD]
[TD="class: td2"]218
[/TD]
[TD="class: td2"]108
[/TD]
[TD="class: td2"]

[/TD]
[TD="class: td2"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: td2"]609
[/TD]
[TD="class: td2"]453
[/TD]
[TD="class: td2"]156
[/TD]
[TD="class: td2"]

[/TD]
[TD="class: td2"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: td2"]39
[/TD]
[TD="class: td2"]18
[/TD]
[TD="class: td2"]21
[/TD]
[TD="class: td2"]

[/TD]
[TD="class: td2"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: td2"]365
[/TD]
[TD="class: td2"]292
[/TD]
[TD="class: td2"]73
[/TD]
[TD="class: td2"]

[/TD]
[TD="class: td2"]#DIV/0!
[/TD]
[/TR]
[TR]
[TD="class: td3"]2821
[/TD]
[TD="class: td3"]2010
[/TD]
[TD="class: td3"]811
[/TD]
[TD="class: td3"]

[/TD]
[TD="class: td3"]#DIV/0!
[/TD]
[/TR]
</tbody>[/TABLE]

Hello

I have copied this out of a pivot i'm working on in excel 2010.
How do I get Field1 to calculate count of FTA/count of client as a %?

I keep getting the divo error when i create the formula in calculated field, I know I can copy the page and do the formula outside the sheet however I want to still be able to double click on numbers and get detailed information for each item on the 'live' sheet.

I have tried searching the forum and internet but keep getting told calculated fields are limited and wont do it.

There must be a way to do this, right?

skinpup

 

[Andrew Poulsom]

Add a field to your source data with a formula like:

=IF(LEN(C2),1/COUNTIF(A:A,A2),0)

 

[skinpup]

i'll give it a go.

thanks.

 

[skinpup]

brilliant! just what i needed.

many thanks

 

[skinpup]

ah on closer inspection it's not right. can you explain what the formula is doing? i think i know what it's trying to do, work out the percentage of FTA against the numbers of clients but no matter how i do it the figures are not right. the figures change depending on how i record the attended column e.g. if I leave in the clients name (as it reports from the data export) it gives a different result say from converting this to a 1 or 0.

what am i doing wrong?

 

[Andrew Poulsom]

If C2 is not empty, it divides 1 by the number of times the entry in A2 appears in column A. Otherwise it returns zero.