+ Start a Discussion
MartinCMartinC 

how to handle Null values in report summary

Greetings everyone,
 
I have an object that contains student test data for a given year.  Each student takes maybe a dozen test per year.
The fields are StudentID, Year, Test1, Test2, Test3... .
I created a report that shows the averages for each test for all students in a given school/district (loved how easy that step was).
 
The problem I am facing is that students will be missing some of the tests, so some of the values will be NULL.
 
It appears that on reports Salesforce treats NULLs same as zeros, which of course messes up the calculation of an average score, which in the case of results like {20, Null, 10} is calculated as 10 instead of 15.  I know that XL and Access do not include NULLs in averages, and I am at a loss on how to work around this in Salesforce.
Does anybody know how I can create a 'Average' report summary field that excludes NULL values?
 
If someone has a solution to this I'd be jumping up and down!
 
Thanks,
Martin
highweihighwei

I'm also looking for a way to handle this.  I did find a request for this in the Ideas site which we can vote for this functionality.

 

http://ideas.salesforce.com/article/show/75927

 

Are there any other way to do this within salesforce?

Megan PietruszkaMegan Pietruszka
I believe this is still not possible (unless you create a new custom formula field on the object and use that as a workaround) but the idea to vote for is now https://success.salesforce.com/ideaView?id=08730000000BpPYAA0