434,661 Members | 1,917 Online
Need help? Post your question and get tips & solutions from a community of 434,661 IT Pros & Developers. It's quick & easy.

# Expression for Avg that does not include Null values

 100+ P: 163 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. Aug 18 '17 #1

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
Expand|Select|Wrap|Line Numbers
1. Option Compare Datbase
2. Option Explicit
3.
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.)

Expand|Select|Wrap|Line Numbers
1. Function GetAverage() As Single
2.
3.     Dim Total As Single
4.     Dim Divisor As Integer
5.     Dim i As Integer
6.
7.     ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
8.     For i = 1 To 8
9.         If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
10.              Divisor = Divisor + 1
11.         End If
12.     Next i
13.
14.     If Divisor = 0 Then          ' All blank
15.         GetAverage = Null
16.         Exit Function
17.     End If
18.
19.     ' Now get the totals using a similar method
20.     For i = 1 To 8
21.         If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
22.              Total = Total + Me.Controls("LineSpeed" & CStr(i))
23.         End If
24.     Next i
25.
26.     GetAverage = Total / Divisor
27.
28. End Function
29.
Remove the After Update event in the TotalLineSpeed field

Add this to the OnCurrent of the Form

Expand|Select|Wrap|Line Numbers
1. Private Sub Form_Current()
2.
3.     TotalLineSpeed = Round(GetAverage, 2)
4.
5. End Sub
6.
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:-

Expand|Select|Wrap|Line Numbers
1. Private Sub LineSpeed1_AfterUpdate()
2.
3.     TotalLineSpeed = Round(GetAverage, 2)
4.
5. End Sub
6.
Phil

29 Replies

 Expert Mod 15k+ P: 31,487 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 : Expand|Select|Wrap|Line Numbers 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. Aug 19 '17 #2

 Expert 100+ P: 1,430 I wonder if a 2 stage approach would work. First the sum which adds 0 if the value is null Expand|Select|Wrap|Line Numbers Nz(a) + Nz(b) + nz(c) ....   Then the divisor which adds 1 if the value is not null Expand|Select|Wrap|Line Numbers 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 Aug 19 '17 #3

 Expert Mod 15k+ P: 31,487 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. Aug 19 '17 #4

 Expert 100+ P: 1,430 As usual, Neopa, you're spot on. Your method avoids the 0/0 situation. mea Culpa. Phil Aug 19 '17 #5

 100+ P: 163 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 Aug 21 '17 #6

 Expert 100+ P: 1,430 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 Aug 21 '17 #7

 100+ P: 163 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. Aug 21 '17 #8

 Expert 100+ P: 1,430 So use Neopa's solution. Except in exceptional circumstances, never store calculated fields in a table. Phil Aug 21 '17 #9

 100+ P: 163 Ok, Thank you so very much. I'll give it a shot today and see if everything works out for me. Aug 21 '17 #10

 100+ P: 163 Expand|Select|Wrap|Line Numbers 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: = Aug 21 '17 #11

 100+ P: 163 Expand|Select|Wrap|Line Numbers 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. Aug 21 '17 #12

 100+ P: 163 Expand|Select|Wrap|Line Numbers 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 Aug 21 '17 #13

 100+ P: 163 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. Aug 21 '17 #14

 100+ P: 163 " 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? Aug 21 '17 #15

 Expert 100+ P: 1,430 This is air code it may need cleaning up a bit Expand|Select|Wrap|Line Numbers 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 Expand|Select|Wrap|Line Numbers     Me!TotalLineSpeed = GetAverage()   Phil Aug 21 '17 #16

 Expert Mod 15k+ P: 31,487 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. Aug 22 '17 #17

 100+ P: 163 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. Aug 22 '17 #18

 100+ P: 163 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: Expand|Select|Wrap|Line Numbers 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 Aug 22 '17 #19

 100+ P: 163 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: Expand|Select|Wrap|Line Numbers 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. Aug 22 '17 #20

 Expert Mod 15k+ P: 31,487 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 :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. 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. Aug 22 '17 #21

 100+ P: 163 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. Aug 22 '17 #22

 Expert Mod 15k+ P: 31,487 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. Aug 22 '17 #23

 Expert 100+ P: 1,430 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 Aug 22 '17 #24

100+
P: 163
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.
Attached Files
 OEE Database.zip (182.3 KB, 41 views)
Aug 23 '17 #25

 Expert 100+ P: 1,430 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 Expand|Select|Wrap|Line Numbers 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.) Expand|Select|Wrap|Line Numbers 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 Expand|Select|Wrap|Line Numbers 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:- Expand|Select|Wrap|Line Numbers Private Sub LineSpeed1_AfterUpdate()       TotalLineSpeed = Round(GetAverage, 2)   End Sub   Phil Aug 23 '17 #26

 100+ P: 163 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. Aug 23 '17 #27

 Expert 100+ P: 1,430 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 Aug 23 '17 #28

 100+ P: 163 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. Aug 23 '17 #29

 Expert Mod 15k+ P: 31,487 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. Aug 24 '17 #30