Calculated field in pivot table, simple Average formula
 

I can't get this formula to work for me, i've formatted it several different
ways and still get the general formula error. The formula works fine outside
of the pivot table on the date, just not in the calculated field. Any ideas
please?
none of these work...

=sum('TotalDollarException')/countif('TotalDollarException',"<0")
or
=('TotalDollarException')/countif('TotalDollarException',"<0")
or
='TotalDollarException'/countif('TotalDollarException',"<0")

Calculated field in pivot table, simple Average formula
 

YOu can't use the Countif function in a pivot table because it requires
a range as its first argument.

In the source table, you could add a column, e.g. TotalDollarCalc.
Use a formula to return an empty string if the TotalDollarException
value is zero:

=IF(E2<0,E2,"")

Then, add the new field to the pivot table, summarized by Average.

Suzanne wrote:
I can't get this formula to work for me, i've formatted it several different
ways and still get the general formula error. The formula works fine outside
of the pivot table on the date, just not in the calculated field. Any ideas
please?
none of these work...

=sum('TotalDollarException')/countif('TotalDollarException',"<0")
or
=('TotalDollarException')/countif('TotalDollarException',"<0")
or
='TotalDollarException'/countif('TotalDollarException',"<0")


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Calculated field in pivot table, simple Average formula
 

Bumber! Ok, thanks, glad it wasnt' something i was doing incorrectly.
Thanks much.

"Debra Dalgleish" wrote:

YOu can't use the Countif function in a pivot table because it requires
a range as its first argument.

In the source table, you could add a column, e.g. TotalDollarCalc.
Use a formula to return an empty string if the TotalDollarException
value is zero:

=IF(E2<0,E2,"")

Then, add the new field to the pivot table, summarized by Average.

Suzanne wrote:
I can't get this formula to work for me, i've formatted it several different
ways and still get the general formula error. The formula works fine outside
of the pivot table on the date, just not in the calculated field. Any ideas
please?
none of these work...

=sum('TotalDollarException')/countif('TotalDollarException',"<0")
or
=('TotalDollarException')/countif('TotalDollarException',"<0")
or
='TotalDollarException'/countif('TotalDollarException',"<0")


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html