Link to home
Start Free TrialLog in
Avatar of twkyoscr
twkyoscr

asked on

Cross Tab-Need to Exclude Zero Values from Average Formula

Trying to calculate Average of Annualized Salary Change amounts but it is off because zero amounts are being calculated in the average.  Any help would be most appreciated.
Avatar of GJParker
GJParker
Flag of United Kingdom of Great Britain and Northern Ireland image

Show details of your crosstab setup and the calculations your using.
Avatar of Mike McCracken
Mike McCracken

The only way to affect summaries in the cross tab is to not include the 0 data in the report.  I assume th e0s are because of lack of data for a given row or column.

mlmcc
Avatar of twkyoscr

ASKER

Mlmcc,
The problem that I am having with the cross tab is that I cannot seem to keep a placeholder for null values.  What I ended up doing is writing a union (in Peoplesoft Query) to also inlcude those that had no adjustment so that the report would still have each manager listed.  I excluded those that had no adjustment from the counts, but the average is now the issue.  I created a running total which I entered a formula under evaluate to do the following: {@Annualized ChgAmtRnd}<>0
However the average amount is correct for some managers and off slightly for others.  
crystal-issue.bmp
SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks everyone for your assistance!  After applying your suggestions and tweaking the data a bit, I ended up with the correct results!
You're welcome.  Glad I could help.

 James