Expression for Avg that does not include Null values

[DJRhino1175]

Here is what I have so far

[[LineSpeed(Meter s)1]+[LineSpeed(Meter s)2]+[LineSpeed(Meter s)3]+[LineSpeed(Meter s)4]+[LineSpeed(Meter s)5]+[LineSpeed(Meter s)6]+[LineSpeed(Meter s)7]+[LineSpeed(Meter s)8]

I need to find the avgerage of the fields that do not include null.

Example:
Add lines 1 and 2 and 3 but do not include the others as there isn't data, then have it divided by the 3 (or what ever the number would be)

Thanks,

Kevin

PS. I forgot to mention this is in a calculated field in the table. I'm using Access 2016.

[NeoPa]

If it's a calculated field then I believe you're constrained to use expressions rather than VBA. It's possible you could get away with calling a VBA function, but I doubt it. Expressions are far more clumsy than VBA of course.

For ease of reading and comprehension I've replaced your long, complicated field names with simple letters, and I've also reduced the number of fields to four, but this is the sort of thing I expect you'll need :

IIf([A]&[B]&[C]&[D] Is Null,0,Nz([A],0)+Nz([B],0)+Nz([C],0)+Nz([D],0)/(IIf([A] Is Null,0,1)+IIf([B] Is Null,0,1)+IIf([C] Is Null,0,1)+IIf([D] Is Null,0,1)))

In the above expression I've handled the case where all items are Null. If this isn't necessary then some of the expression is redundant. You'll notice that even for four principals the expression is long and complicated.

[PhilOfWalton]

I wonder if a 2 stage approach would work.
First the sum which adds 0 if the value is null

Nz(a) + Nz(b) + nz(c) ....

Then the divisor which adds 1 if the value is not null

Iif(Not IsNull(a),1) + Iif(Not IsNull(b),1) + Iif(Not IsNull(c),1) ..

Then a simple division.
Note that if any value is 0, it will ne taken into the average, Average of 2 & Null = 2
Average of 2 & 0 = 1

Phil

[NeoPa]

Hi Phil.

You may notice the extra bit in my suggestion, which was to handle the possibility of all items being Null. A sneaky way to check for this is to append them all together using the &. Only if all are Null will the result be Null. Otherwise all the values are added together using Nz([X],0) then this total is divided by the number of items that prove to be non Null. Very much as you describe in your post.

[PhilOfWalton]

As usual, Neopa, you're spot on. Your method avoids the 0/0 situation. mea Culpa.

Phil

[DJRhino1175]

So should I not factor out the null values as they will not be considered? What number would I divide by? The 8 total fields I have? Was also thinking of creating another field and doing a query that filters out anything less than zero and give me a count. Then dividing my sum of the 8 fields by that number. Which do you think would be the best route?

Thanks

[PhilOfWalton]

To make it simple, consider only the first 2 fields.

I think both NeoPa & myself consider that if no entry has been made, then it shouldn't be included in the average.
Really, we need an answer to the note I made in an earlier posting
Note that if any value is 0, it will be taken into the average
Average of 2 & Null = 2
Average of 2 & 0 = 1

So basically which do you consider to be correct average.
Your original posting where you say "I need to find the avgerage of the fields that do not include null." seems to be asking for the first option.

Incidentally, the field names with brackets in them are not a good idea, and whilst perfectly OK they look very confusing.
So instead of [[LineSpeed(Meter s)1] Use either LineSpeed1 or if you insist [[LineSpeedMeters 1]

For the last part of your question, can these fields ever be negative?

Phil

[DJRhino1175]

Average of 2 & Null = 2 I think would be correct. If 3 out of the 8 have data (Not null, and don't want zeroes) Then I need the average of the 3 entries.

I might need to change how I'm handling this situation. I entered 1 pc of data and I was getting #Num! and #Error! messages in the empty fields. So I'm thinking instead of having calculated fields in the table, I will need to do AfterUpdate() in VBA. I found that you can not do Nz() in calculated fields in the table.

These fields can never be negative or Zero. They will need to be blank or have a positive number.

[PhilOfWalton]

So use Neopa's solution.

Except in exceptional circumstances, never store calculated fields in a table.

Phil

[DJRhino1175]

Ok, Thank you so very much. I'll give it a shot today and see if everything works out for me.

[DJRhino1175]

IIf([LineSpeed(Meters)1]&[LineSpeed(Meters)2]&[LineSpeed(Meters)3]&[LineSpeed(Meters)4]&[LineSpeed(Meters)5]&[LineSpeed(Meters)6]&[LineSpeed(Meters)7]&[LineSpeed(Meters)8] Is Null,0,Nz([LineSpeed(Meters)1],0)+Nz([LineSpeed(Meters)2],0)+Nz([LineSpeed(Meters)3],0)+Nz([LineSpeed(Meters)4],0)+Nz([LineSpeed(Meters)5],0)+Nz([LineSpeed(Meters)6],0)+Nz([LineSpeed(Meters)7],0)+Nz([LineSpeed(Meters)8],0)/(IIf([LineSpeed(Meters)1] Is Null,0,1)+IIf([LineSpeed(Meters)2] Is Null,0,1)+IIf([LineSpeed(Meters)3] Is Null,0,1)+IIf([LineSpeed(Meters)4] Is Null,0,1)+IIf([LineSpeed(Meters)5] Is Null,0,1)+IIf([LineSpeed(Meters)6] Is Null,0,1)+IIf([LineSpeed(Meters)7] Is Null,0,1)+IIf([LineSpeed(Meters)8] Is Null,0,1)))

I get a Compile Error:
Expected: =

[DJRhino1175]

Private Sub TotalLineSpeedMeters_AfterUpdate()
Me!TotalLineSpeedMeters = IIf([LineSpeed1] & [LineSpeed2] & [LineSpeed3] & [LineSpeed4] & [LineSpeed5] & [LineSpeed6] & [LineSpeed7] & [LineSpeed8] Is Null, 0, Nz([LineSpeed1], 0) + Nz([LineSpeed2], 0) + Nz([LineSpeed3], 0) + Nz([LineSpeed4], 0) + Nz([LineSpeed5], 0) + Nz([LineSpeed6], 0) + Nz([LineSpeed7], 0) + Nz([LineSpeed8], 0) / (IIf([LineSpeed1] Is Null, 0, 1) + IIf([LineSpeed2] Is Null, 0, 1) + IIf([LineSpeed3] Is Null, 0, 1) + IIf([LineSpeed4] Is Null, 0, 1) + IIf([LineSpeed5] Is Null, 0, 1) + IIf([LineSpeed6] Is Null, 0, 1) + IIf([LineSpeed7] Is Null, 0, 1) + IIf([LineSpeed8] Is Null, 0, 1)))
End Sub

I got rid of the Error by adding: Me!TotalLineSpe edMeters =
Also as you can see I got rid of the (Meters) in the field names.

But when I start to add data I get a #error in the field name. So I went and add data to all 8 fields, the error goes away but the result is incorrect. I get a 0.00 in stead of 38.875 (sum of 8 linespeeds/8) Not sure what to do now.

[DJRhino1175]

Expression for Avg that does not include Null values

Private Sub TotalLineSpeedMeters_AfterUpdate()
Me!TotalLineSpeedMeters = IIf([LineSpeed1] & [LineSpeed2] & [LineSpeed3] & [LineSpeed4] & [LineSpeed5] & [LineSpeed6] & [LineSpeed7] & [LineSpeed8] Is Null, 0, Nz([LineSpeed1], 0) + Nz([LineSpeed2], 0) + Nz([LineSpeed3], 0) + Nz([LineSpeed4], 0) + Nz([LineSpeed5], 0) + Nz([LineSpeed6], 0) + Nz([LineSpeed7], 0) + Nz([LineSpeed8], 0) / (IIf([LineSpeed1] Is Null, 0, 1) + IIf([LineSpeed2] Is Null, 0, 1) + IIf([LineSpeed3] Is Null, 0, 1) + IIf([LineSpeed4] Is Null, 0, 1) + IIf([LineSpeed5] Is Null, 0, 1) + IIf([LineSpeed6] Is Null, 0, 1) + IIf([LineSpeed7] Is Null, 0, 1) + IIf([LineSpeed8] Is Null, 0, 1)))
End Sub

[DJRhino1175]

I got rid of the Error by adding: Me!TotalLineSpe edMeters =
Also as you can see I got rid of the (Meters) in the field names. But when I start to add data I get a #error in the field name. So I went and add data to all 8 fields, the error goes away but the result is incorrect. I get a 0.00 in stead of 38.875 (sum of 8 linespeeds/8) Not sure what to do now.

[DJRhino1175]

" I get a 0.00 in stead of 38.875 (sum of 8 linespeeds/8) Not sure what to do now." Blonde Moment, I had the wrong record source. No #error! message in the field with one pc of data. I still get 0.00 for the result, doesn't seem to up date the reading as I add data. Do I have the code in the wrong event area?