By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Expression for Avg that does not include Null values

100+
P: 100
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

✓ answered by 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
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

Share this Question
Share on Google+
29 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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
  1. 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

PhilOfWalton
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
  1. Nz(a) + Nz(b) + nz(c) ....
  2.  
Then the divisor which adds 1 if the value is not null
Expand|Select|Wrap|Line Numbers
  1. Iif(Not IsNull(a),1) + Iif(Not IsNull(b),1) + Iif(Not IsNull(c),1) ..
  2.  
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

NeoPa
Expert Mod 15k+
P: 31,186
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

PhilOfWalton
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: 100
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

PhilOfWalton
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: 100
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

PhilOfWalton
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: 100
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: 100
Expand|Select|Wrap|Line Numbers
  1. 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: 100
Expand|Select|Wrap|Line Numbers
  1. Private Sub TotalLineSpeedMeters_AfterUpdate()
  2. 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)))
  3. 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: 100
Expand|Select|Wrap|Line Numbers
  1. Private Sub TotalLineSpeedMeters_AfterUpdate()
  2. 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)))
  3. End Sub
Aug 21 '17 #13

100+
P: 100
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: 100
" 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

PhilOfWalton
Expert 100+
P: 1,430
This is air code it may need cleaning up a bit

Expand|Select|Wrap|Line Numbers
  1. Function GetAverage() as Single
  2.     Dim Total as Single
  3.     Dim Divisor as Integer
  4.     Dim i as Integer
  5.  
  6.     ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
  7.     For i  = 1 to 8
  8.         If Not IsNull(Me.Controls("LineSpeed" & Cstr(i))) then
  9.              Divisor = Divisor + 1
  10.         End If
  11.     Next i
  12.  
  13.     If Divisor = 0 then          ' All blank
  14.         GetAverage = Null
  15.         Exit Function
  16.     End if
  17.  
  18.     ' Now get the totals using a similar method
  19.     For i  = 1 to 8
  20.         If Not IsNull(Me.Controls("LineSpeed" & Cstr(i))) then
  21.              Total = Total + Me.Controls("LineSpeed" & cstr(i))
  22.         End If
  23.     Next i
  24.  
  25.     GetAverage = Total / Divisor
  26. Exit Function
  27.  
Then on the Current of the form and on After Update of all 8 fields use
Expand|Select|Wrap|Line Numbers
  1.     Me!TotalLineSpeed = GetAverage()
  2.  
Phil
Aug 21 '17 #16

NeoPa
Expert Mod 15k+
P: 31,186
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: 100
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: 100
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
  1. Function GetAverage() As Single
  2.     Dim Total As Single
  3.     Dim Divisor As Integer
  4.     Dim i As Integer
  5.  
  6.     ' Loop through all the controls with a name starting with "LineSpeed" or LineSpeedMeters - what ever you are using
  7.     For i = LineSpeed1 To LineSpeed8
  8.         If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
  9.              Divisor = Divisor + 1
  10.         End If
  11.     Next i
  12.  
  13.     If Divisor = 0 Then          ' All blank
  14.         GetAverage = Null
  15.         Exit Function
  16.     End If
  17.  
  18.     ' Now get the totals using a similar method
  19.     For i = 1 To 8
  20.         If Not IsNull(Me.Controls("LineSpeed" & CStr(i))) Then
  21.              Total = Total + Me.Controls("LineSpeed" & CStr(i))
  22.         End If
  23.     Next i
  24.  
  25.     GetAverage = Total / Divisor
  26. End Function
I had to put End function as it gave me an error using Exit Function
Aug 22 '17 #19

100+
P: 100
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
  1. Private Sub DrawingNumber1_AfterUpdate()
  2. Me!Section1 = DrawingNumber1.Column(1)
  3. Me![LineSpeed(Meters)1] = DrawingNumber1.Column(3)
  4. End Sub
I have the same code for each line except for the "DrawingNumber(Number) is different.
Aug 22 '17 #20

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

100+
P: 100
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

NeoPa
Expert Mod 15k+
P: 31,186
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

PhilOfWalton
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: 100
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
File Type: zip OEE Database.zip (182.3 KB, 31 views)
Aug 23 '17 #25

PhilOfWalton
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
  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
Aug 23 '17 #26

100+
P: 100
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

PhilOfWalton
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: 100
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

NeoPa
Expert Mod 15k+
P: 31,186
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

Post your reply

Sign in to post your reply or Sign up for a free account.