insert calculation in to pivot table results

[skinpup]

Count of client	Count of attended	Count of FTA		Sum of Field1
498	327	171		#DIV/0!
741	516	225		#DIV/0!
243	186	57		#DIV/0!
326	218	108		#DIV/0!
609	453	156		#DIV/0!
39	18	21		#DIV/0!
365	292	73		#DIV/0!
2821	2010	811		#DIV/0!

<tbody>
</tbody>

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.