Taking the Average of values in a report except for null values

[accessuser10]

Hi all,



I have a list of numbers in a report that I want to take the average of in that sections footer. The problem that I am having is that there are also null values that are causing me to get an error message when I try to calculate the average. Is there a formula I could use to take the average of every number I have in that field except for the nulls?

Thanks

[June7]

Aggregate Avg function ignores Nulls. How are you trying to get the average that is causing an error?

[rpeare]

I'm guessing you're trying to average a calculated value or the denominator of your expression is 0

you can try something like

=iif(count(denominatorfield) = 0, 0, sum(numeratorfield)/count(denominatorfield))

[orange]

accessuser10

What was the error message you received?

[June7]

Issue with IIf is that all parts must be able to be evaluated, even if it isn't the part returning value, otherwise will still error. Division by 0 cannot be evaluated.

This should return Null if the Count = 0

=Sum(numeratorfield) / IIf(Count(denominatorfield) = 0, Null, Count(denominatorfield))

However, still don't know what the OP is doing.

[accessuser10]

I have created a form where a reviewer can type in a score for a project that an employee is working on. I then have a report that shows the average of all the projects grouped by employees. However some scores that are entered are just left as null because the employee is not evaluated on that review category for a particular project. So for example when the three values I want to take the average of are 100, a null value, and 90, I get an error that says Data Type Mismatch in Criteria Expression. However when I take the average without null values I do not get this error. Let me know if I am making sense or you need further detail

[rpeare]

You've restated the problem and I think we all understand what it is, we just don't know how you have things set up. perhaps if you supply a sample database it would be easier to give you direction.

[June7]

Still don't know how you are doing this calculation. At least show the expression that is erroring.