Crystal Reports 2013 How do I exclude Null Values in a Crosstab average?

Question

How do I get a crosstab in Crystal Reports 2013 to exclude null values from a crosstab average?

For example
Procedure Name : OCT RETINA
Procedure Count : 8
Procedures with Completion Times Populated : 6
Summary of all 6 Completion Times : 101
I was expecting the average of the Completion Times to be calculated as 101/6. Instead, Crystal Reports is calculating the average as 101/8. It needs to be calculated as 101/6 (without counting null values in the denominator).

Adding another group is not an option because of the very specific way the values are sorted in the detail (hence a crosstab in a footer). The names of the procedures are dynamic so keeping a running total of each procedure's average completion time is not an option either.

Thanks in advance!!!!

Answer 1

One solution would be instead of taking the field create a formula use that in cross tab.

Create a formula @count

if << procedure field>>=<<your value>>
then 1
else 0

Now place the formula and take sum instead of count

Answer 2

Thanks for everyone's feedback! The answer is to use Weighted Average instead of just a plain old Average.

All I had to do was
a. Create a formula field called IncludeProcInDenominator and set the value to if {Command.ProcTime} > 0 then 1
b. Right click on the Average Proc Time calculation in the crosstab and select Edit Summary
c. When the Edit Summary dialog box appears, in the Calculate This Summary drop down, select Weighted average with
d. In the dropdown box underneath that, select @IncludeProcInDenominator

In effect, the only values that are averaged are the ones where @IncludeProcInDenominator = 1

Thanks for everyone's help!