Counting null values as Zero

[missypooh1969]

I have columns 1 - 5, which contain counts. Some are null if none exist. I used Nz on each column to place a zero in the null fields. I am trying to count the zero. I am trying to add columns 3 - 5 in each row then divide by another row to receive what I am calling a rating.

Without the Nz, the query would only count if rows 3 - 5 all contained a value. If any contained a null value, then the rating would be null. That's when I added the Nz function. (A3: Nz([3],0)

Now with the Nz function, there are zeros in place of any nulls, but my rating is all messed up. Have no idea where the answer is coming from. I am using:


Rating: ([A5]+[A4]+[A3])/[Total]. Not real sure how the system is evaluating the new zeros. Anyone have any ideas?

[June7]

What do you mean by 'messed up'? What is the result of the expression?

[missypooh1969]

Here's a copy of what I receive. Satisfaction_Rating: ([A5]+[A4]+[A3])/[Total Of Surveys]

Total Of Surveys	A1	A2	A3	A4	A5	Satisfaction_Rating
2	0	0	1	0	1	5050.00%
1	0	0	0	0	1	10000.00%
59	0	0	21	8	30	52238.98%
1	0	0	0	0	1	10000.00%
1	0	0	0	0	1	10000.00%
3	0	1	0	1	1	3666.67%

[JoeM]

Note that by default, the NZ function returns a Text value, not a Number. So you may need to coerce your underlying calculations to numeric results like this:
A3: Nz([3],0)+0

Otherwise, it is treating [A3] + [A4] + [A5]
as text concatenation where 1 + 0 + 1 = 101
instead of numeric addition where 1 + 0 + 1 = 2

[June7]

I tested an expression in VBA immediate window using Nz on each term and the result is correct.

But Joe's explanation does fit. Because 101 / 2 * 100 = 5050.

If you want to provide db for analysis, follow instructions at bottom of my post.

[missypooh1969]

Thank you so much!!!! All day on this one and it was such a simple solution! Thanks so much for answering and providing your wisdom, JoeM! Thanks June7 for listening. I think you were there, but Joe beat you to the punch! So, thanks to both of you!

[JoeM]

You're welcome!

I had actually seen your question earlier when it was unanswered, but got pulled away on a project before I could reply. I deal with a lot of "weird" data issues in my line of work, so this one was pretty familiar to me.

I don't quite have the level of expertise as June (she has helped me out on a lot more threads than I have helped her), but hope to get there some day!