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.
Show details of your crosstab setup and the calculations your using.
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
mlmcc
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
James