Expression for Avg that does not include Null values

[DJRhino1175]

Here is what I have so far

[[LineSpeed(Meters)1]+[LineSpeed(Meters)2]+[LineSpeed(Meters)3]+[LineSpeed(Meters)4]+[LineSpeed(Meters)5]+[LineSpeed(Meters)6]+[LineSpeed(Meters)7]+[LineSpeed(Meters)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(Meters)1] Use either LineSpeed1 or if you insist [[LineSpeedMeters1]

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!TotalLineSpeedMeters =
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]

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!TotalLineSpeedMeters =
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?

[PhilOfWalton]

This is air code it may need cleaning up a bit

Function GetAverage() as Single
    Dim Total as Single
    Dim Divisor as Integer
    Dim i as Integer
 
    ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
    For i  = 1 to 8
        If Not IsNull(Me.Controls("LineSpeed" & Cstr(i))) then
             Divisor = Divisor + 1
        End If
    Next i
 
    If Divisor = 0 then          ' All blank
        GetAverage = Null
        Exit Function
    End if
 
    ' Now get the totals using a similar method
    For i  = 1 to 8
        If Not IsNull(Me.Controls("LineSpeed" & Cstr(i))) then
             Total = Total + Me.Controls("LineSpeed" & cstr(i))
        End If
    Next i
 
    GetAverage = Total / Divisor
Exit Function
 

Then on the Current of the form and on After Update of all 8 fields use

    Me!TotalLineSpeed = GetAverage()
 

Phil

[NeoPa]

Kevin.

You said you were using a calculated field in a table. If you're using VBA instead then the question's completely different.

I would just add that, unlike Phil, I strongly recommend using brackets [] around field names in SQL or expressions regardless of their being absolutely necessary. It immediately tells the reader that the contents are a reference to an object and not any part of the syntax itself. When using SQL I always surround any references in the square brackets.

[DJRhino1175]

NeoPa,

I am using a calculated field in the table itself. It would not let me use the NZ() function, so I kicked around the idea of maybe using VBA instead. If this is the better route fine as long as it puts the data in the table field.

Now I built VBA code Per what Phil suggested. Unfortunately its not working for me.

[DJRhino1175]

I put this in the After update in all 8 Linespeed fields and the TotalLineSpeed Field:

=[Me]![TotalLineSpeed]=GetAverage()

This is the code that's behind it:

Function GetAverage() As Single
    Dim Total As Single
    Dim Divisor As Integer
    Dim i As Integer
 
    ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
    For i = LineSpeed1 To LineSpeed8
        If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
             Divisor = Divisor + 1
        End If
    Next i
 
    If Divisor = 0 Then          ' All blank
        GetAverage = Null
        Exit Function
    End If
 
    ' Now get the totals using a similar method
    For i = 1 To 8
        If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
             Total = Total + Me.Controls("LineSpeed" & CStr(i))
        End If
    Next i
 
    GetAverage = Total / Divisor
End Function

I had to put End function as it gave me an error using Exit Function

[DJRhino1175]

Is it an issue that I'm pulling the lineSpeed data form a query?

Here is the code for how I get the data from the LineSpeed:

Private Sub DrawingNumber1_AfterUpdate()
Me!Section1 = DrawingNumber1.Column(1)
Me![LineSpeed(Meters)1] = DrawingNumber1.Column(3)
End Sub

I have the same code for each line except for the "DrawingNumber(Number) is different.

[NeoPa]

Hi Kevin.

I hear you. I might suggest that skipping so quickly from one attempt across to a completely different approach is not advisable. It may be frustrating, but perseverence until you know, because you understand, that it can't work or even that it may work but is nevertheless inadvisable, is a more sensible course of action.

You may well ask why that's important. It's important because :

1. Logic works that way. You need a firm foundation before you move on to the next step. You may skip across and find problems with the other approach. That leaves you nowhere if you haven't progressed the first to a point of solidity. No-one likes to have to start over from scratch.
2. It makes it very laborious for people to work with you. At this stage I suspect you can benefit greatly from willing assistance, and we're generally more than willing to assist.

So I guess the question becomes, now that you've put energy into both approaches, which would you prefer to push on with?

Before you answer that though, another consideration that may be preferable to both is what about not storing the data at all, but instead providing it within a query when reading the records from the table?

If the latter then we can show you (in a separate thread that you'll create for the purpose) how to write a function procedure that will take an unspecified number of Variant parameters and return the average of only those that are not Null. The query would simply return a separate value returned from this procedure with all the fields passed to it.

[DJRhino1175]

I think I like the approach we are on now, I think it will help me learn a little more VBA as I have not done a ton of VBA. I have done a lot of simple databases with simple calculations and such. I apologize if I'm frustrating anyone, I don't mean to. I only change the approached as a calculated field in the table itself seem to be very limited. I like the data going in to the table as it makes it easier to verify the data at times. I noticed now that I put the above code in my linespeeds are rounded off on the form and data table, but its correct in the data table I'm pulling it from.

[NeoPa]

OK Kevin. That's a clear answer with which we can work. I'll leave you with Phil for now but keep an eye on how things progress.

Using VBA still gives the opportunity for that clever use of procedure parameters I mentioned earlier.

[PhilOfWalton]

Right, Kevin, it looks as if it''s back to me.

Sorry about the Exit Function instead of the End Function. Well spotted, but I did warn you it was air code.

So let's get back to first principles.
What is the table layout for your data and can you provide some sample data, or better still is the database stripped of any sensitive information?

Phil

[DJRhino1175]

No problems with the Exit/End function, something I was able to figure out somewhat quickly. Database has no sensitive data or any data for that matter. I went through and removed all the calculated fields from the table and put the calculations in the description so I wouldn't need to refigure them out. I even stripped out the code you gave me, but saved it to a text doc. I attached the database as a zip file.

[PhilOfWalton]

I'm far too polite to remark on the design of your database, but when you have time, look up Normalisation and Relationships. You will have problems the way it is designed.

So to sort out your problem

At the top of every module you ever write have

Option Compare Datbase
Option Explicit
 

The Option Explicit will give compile errors if things aren't declared properly.

At the top of the Entry form, under your new Option Explcit, copy the following code (it's slightly different from the air code.)

Function GetAverage() As Single
 
    Dim Total As Single
    Dim Divisor As Integer
    Dim i As Integer
 
    ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
    For i = 1 To 8
        If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
             Divisor = Divisor + 1
        End If
    Next i
 
    If Divisor = 0 Then          ' All blank
        GetAverage = Null
        Exit Function
    End If
 
    ' Now get the totals using a similar method
    For i = 1 To 8
        If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
             Total = Total + Me.Controls("LineSpeed" & CStr(i))
        End If
    Next i
 
    GetAverage = Total / Divisor
 
End Function
 

Remove the After Update event in the TotalLineSpeed field

Add this to the OnCurrent of the Form

Private Sub Form_Current()
 
    TotalLineSpeed = Round(GetAverage, 2)
 
End Sub
 

I note that LineSpeed 1,2,3,4,5,6,7,8 are all locked, so they cannot be updated. If this is an error, you need to add the following code to all 8 fields (adjusting for the correct field name:-

Private Sub LineSpeed1_AfterUpdate()
 
    TotalLineSpeed = Round(GetAverage, 2)
 
End Sub
 

Phil

[DJRhino1175]

Phil,

I added the "Option Explicit" I forgot to go into the options to do this by default when I upgraded.

Also removed the code from the total field.
I have all the fields that are calculation fields(Green Label/White Textbox) locked so data doesn't get entered in those fields.

So before I start adding this code should I change how my table is? If so please explain. None of my tables truly relate that I can tell. The operators table just fields a drop down list and the Linespeeds table is feeding a query so when I select a "DrawingNumber" it automatically inputs the Section and lineSpeed. I'm more of a beginner. I'm self taught, and for some reason have a heck of a time understanding VBA. I have been looking for classes near me and there just isn't any, the problem with living in the sticks.

Looking at the code you supplied is like looking at Klingon to me, so I'll need a little more assistance as How I need to write it as just coping and pasting it doesn't work. I don't want you to write it for me either just maybe more of an explanation as I don't know what certain things mean.

Sorry for being a huge pain, but thanks for all your help so far.

[PhilOfWalton]

Good. It seems as if we are on the same wavelength.

I am more than pleased to help, but there needs to be a lot of discussion, as I need to know what your DB if for, and how you envisage it working.
I find typing very boring & tiring. So if you want to talk about it on Skype, send me a private message, and I will give you my contact details.

Phil

[DJRhino1175]

Could do Skype, but have no Mic, but can give you the number I'm at and we can use the phone to talk it through. I feel a voice conversation would definitely be good to help with understanding.

I built this based of an Excel sheet that is being used now, but with access I can remove some of the redundancies and errors that can be typed in. I to hate typing.

[NeoPa]

Very pleased to see this progressing so well. I don't need to interfere as you're in very good hands.

However, as an Admin and friend of Phil I know things that I can't share about where you both live. Let me just say there's a fair bit of water between the two of you.

Phil. As a Skype user you can make relatively cheap calls to Kevin using Skype. Certainly cheaper than intercontinental telephone calls.

I hope this is some help.