473,322 Members | 1,620 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Calculating an Average over multiple fields

Good afternoon,

I am trying to calculate an average over multiple fields. I've tried using the following

=Avg(IIF([Score A] = "NA",Null, Val([Score A]))) + Avg(IIF([Score B] = "NA",Null, Val([Score B]))) + Avg(IIF([Score C] = "NA",Null, Val([Score C])))

The above, [Score A], [Score B], etc. are text fields pertaining to an evaluation rating scale:

NA 1 2 3 4 5

I would like to calculate an average over all the evaluation questions. The AVG formula ignores Null Values which is why i am trying to get the expression to set the value to NULL when NA is entered, meaning that the question does not apply and therefore it will not be included in the AVG calculation. I use VAL because of the fact that i have the field set to text.

Please help, i am not able to find a solution to this problem.

Thanks in advance for your efforts and help.

My operating system is XP and this is for Access 2003.

Rene
Jul 30 '07 #1
2 6313
puppydogbuddy
1,923 Expert 1GB
Good afternoon,

I am trying to calculate an average over multiple fields. I've tried using the following

=Avg(IIF([Score A] = "NA",Null, Val([Score A]))) + Avg(IIF([Score B] = "NA",Null, Val([Score B]))) + Avg(IIF([Score C] = "NA",Null, Val([Score C])))

The above, [Score A], [Score B], etc. are text fields pertaining to an evaluation rating scale:

NA 1 2 3 4 5

I would like to calculate an average over all the evaluation questions. The AVG formula ignores Null Values which is why i am trying to get the expression to set the value to NULL when NA is entered, meaning that the question does not apply and therefore it will not be included in the AVG calculation. I use VAL because of the fact that i have the field set to text.

Please help, i am not able to find a solution to this problem.

Thanks in advance for your efforts and help.

My operating system is XP and this is for Access 2003.

Rene
Try this and see if it helps:

=Avg(IIF([Score A] = "NA","", Val(Nz([Score A],"")))) + Avg(IIF([Score B] = "NA","", Val(Nz([Score B],"")))) + Avg(IIF([Score C] = "NA","", Val(Nz([Score C],""))))
Aug 6 '07 #2
ADezii
8,834 Expert 8TB
Good afternoon,

I am trying to calculate an average over multiple fields. I've tried using the following

=Avg(IIF([Score A] = "NA",Null, Val([Score A]))) + Avg(IIF([Score B] = "NA",Null, Val([Score B]))) + Avg(IIF([Score C] = "NA",Null, Val([Score C])))

The above, [Score A], [Score B], etc. are text fields pertaining to an evaluation rating scale:

NA 1 2 3 4 5

I would like to calculate an average over all the evaluation questions. The AVG formula ignores Null Values which is why i am trying to get the expression to set the value to NULL when NA is entered, meaning that the question does not apply and therefore it will not be included in the AVG calculation. I use VAL because of the fact that i have the field set to text.

Please help, i am not able to find a solution to this problem.

Thanks in advance for your efforts and help.

My operating system is XP and this is for Access 2003.

Rene
Anytime that you are using the IIf() Function in conjunction with possible NULL values, you are literally playing with fire because of the inefficiency of IIf() and the usual NULL problems.
Aug 7 '07 #3

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

Similar topics

6
by: Stephen Miller | last post by:
Firstly, sorry for the long post, but I've included a fair bit of sample data. Im doing a comparision of 10yr Bond prices and CPI adjustments, with an 18 week moving average of the CPI. I'm...
7
by: JLM | last post by:
I have a table that has fieldA, fieldB, fieldC. I want fieldC=fieldA-fieldB. simple enough. the next record I want to be able to do the same on the new value of fieldC. I can do this with SAP...
1
by: jlm | last post by:
I have a form which feeds table (TblEmpLeave) of Employee Leave Time (time taken off for Administrative, Annual, Sick, Compensation leave). I have EmpID, LeaveDate, LeaveType, LeaveHours fields on...
2
by: Wayne Aprato | last post by:
I've read most, if not all, of the posts on moving average and still can't find a simple solution to my problem (if a simple solution exists!) I have a table with 2 fields: Hours and Injuries. I...
5
by: jl2886 | last post by:
I have four fields in my table that I want to average providing the fields have a value, not the null, for each case(id). I also have an average field in my table. I have a form for the table and I...
5
by: ye2127 | last post by:
Hi, I have two fields in my report. One of them is school name(the school name appears multiple times). The other field is class test score. How would I go about calculating the test average...
1
by: ye2127 | last post by:
Hi, I have two fields in my report. One of them is school name(the school name appears multiple times). The other field is class test score. How would I go about calculating the test average for...
1
kmartinenko
by: kmartinenko | last post by:
I have a table with over 12,000 entries. I have created a form (with the help of this forum) that will return the search results based upon the stop and stop time selected. See post...
1
by: cmb3587 | last post by:
My code runs fine for the most part...the only time it fails is when I type in a negative to end the array. I don't want the negative number to be included in the array and I thought that is what...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.