calculating Average Value with exceptions

[kapiszon]

Hi there,

I have 7 columns and out of them I have to calculate its Average Value.
These columns have values from 1-5, they represent answers in a
questionnaire, 1 for very bad, 5 for very good. It's easy, with a simple
Select SQL query I can add all these fields and then divide by 7 and I get a
result.

But there is a tricky part as well. Sometimes there can be a value 0, which
means that someone didn't provide any answer for that question, and therefore
in calculating the Average we cannot use this 0 value, we have to omit it.
There can be a few 0 answers in those 7 fields, we don't know how many.

How to do it? What sort of VBA code might help to sort it out? Any ideas?

 

[Allen Browne]

The best solution would be to leave the column as Null rather than use zero.
JET will correctly calculate the average of column by counding the non-null
values only.

For example, if you have the values:
2, 3, Null, 4, Null
it will calculate the averate as 3, i.e. (2+3+4)/3, not (2+3+4)/5.

If you cannot use Nulls correctly, you could fudge it by converting the
zeros to nulls. Type an expression like this into the Field row in query
design:
Iif([f]=0, Null, [f])
substituting your field name for f.
You can then average this field.
Choose Average in the Totals row in query design.

 

[John Spencer]

Assuming fields named F1 to F7, you can use the following expression to get
the average for the row.

F1 + F2 + F3 + F4 + F5 + F6 + F7 / Abs(F1<>0 + F2<>0 + F3<>0 + F4<>0 + F5<>0 +
F6<>0 +F7<>0)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

Won't that design require you to modify the formulas (and queries and forms
and reports and ...) every time there's a change in the number of questions?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[kapiszon]

I've tried to do it but I got an error message "ADO error: incorrect syntax
'<'"

Then I tried to modify my currect query and I came up with the following:
SELECT hotelcode, IdRespuesta, (RHabitacion + RInstalaciones +
RLimpieza + RComida + RPersonal + RChekin + RCalidad) / Abs(IIF(RHabitacion =
0, 0, 1)
+ IIF(RInstalaciones = 0, 0, 1) + IIF(RLimpieza = 0,
0, 1) + IIF(RComida = 0, 0, 1) + IIF(RPersonal = 0, 0, 1) + IIF(RChekin = 0,
0, 1) + IIF(RCalidad = 0, 0,
1)) AS Average_Score
FROM reviews.Reviews_respuestas

.... but it didn't work either...

rgds,
Daniel

 

[kapiszon]

I get an error message saying "ADO error: incorrect syntax '=' ", that's
strange, I don't understand this error...

rgds,
Daniel

The best solution would be to leave the column as Null rather than use zero.
JET will correctly calculate the average of column by counding the non-null
values only.

For example, if you have the values:
2, 3, Null, 4, Null
it will calculate the averate as 3, i.e. (2+3+4)/3, not (2+3+4)/5.

If you cannot use Nulls correctly, you could fudge it by converting the
zeros to nulls. Type an expression like this into the Field row in query
design:
Iif([f]=0, Null, [f])
substituting your field name for f.
You can then average this field.
Choose Average in the Totals row in query design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi there,

I have 7 columns and out of them I have to calculate its Average Value.
These columns have values from 1-5, they represent answers in a
questionnaire, 1 for very bad, 5 for very good. It's easy, with a simple
Select SQL query I can add all these fields and then divide by 7 and I get
a
result.

But there is a tricky part as well. Sometimes there can be a value 0,
which
means that someone didn't provide any answer for that question, and
therefore
in calculating the Average we cannot use this 0 value, we have to omit it.
There can be a few 0 answers in those 7 fields, we don't know how many.

How to do it? What sort of VBA code might help to sort it out? Any ideas?

 

[kapiszon]

I sort it out with the following code:

SELECT reviews_Reviews_respuestas.hotelcode,
reviews_Reviews_respuestas.IdRespuesta,
(RHabitacion+RInstalaciones+RLimpieza+RComida+RPersonal+RChekin+RCalidad)/(IIf(RHabitacion=0,Null,1)+IIf(RInstalaciones=0,Null,1)+IIf(RLimpieza=0,Null,1)+IIf(RComida=0,Null,1)+IIf(RPersonal=0,Null,1)+IIf(RChekin=0,Null,1)+IIf(RCalidad=0,Null,1)) AS AverageScore
FROM reviews_Reviews_respuestas

But it works only in Access database file, when I tried to use it in Access
Project file it didn't work, I got an ADO error.

many thanks!

The best solution would be to leave the column as Null rather than use zero.
JET will correctly calculate the average of column by counding the non-null
values only.

For example, if you have the values:
2, 3, Null, 4, Null
it will calculate the averate as 3, i.e. (2+3+4)/3, not (2+3+4)/5.

If you cannot use Nulls correctly, you could fudge it by converting the
zeros to nulls. Type an expression like this into the Field row in query
design:
Iif([f]=0, Null, [f])
substituting your field name for f.
You can then average this field.
Choose Average in the Totals row in query design.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi there,

I have 7 columns and out of them I have to calculate its Average Value.
These columns have values from 1-5, they represent answers in a
questionnaire, 1 for very bad, 5 for very good. It's easy, with a simple
Select SQL query I can add all these fields and then divide by 7 and I get
a
result.

But there is a tricky part as well. Sometimes there can be a value 0,
which
means that someone didn't provide any answer for that question, and
therefore
in calculating the Average we cannot use this 0 value, we have to omit it.
There can be a few 0 answers in those 7 fields, we don't know how many.

How to do it? What sort of VBA code might help to sort it out? Any ideas?

 

[Allen Browne]

Best solution would be to to use Null properly.

Any decent software will not count nulls when averaging values.

 

[John Spencer]

You don't need ABS with modification you made. Also, if you are getting an
ADO error, then you may need to use a Case statement instead of IIF. I don't
work with ADO and therefore my advice may be off base.

SELECT hotelcode
, IdRespuesta
, (RHabitacion + RInstalaciones +
RLimpieza + RComida + RPersonal + RChekin + RCalidad)
/ (IIF(RHabitacion = 0, 0, 1)
+ IIF(RInstalaciones = 0, 0, 1)
+ IIF(RLimpieza = 0, 0, 1)
+ IIF(RComida = 0, 0, 1)
+ IIF(RPersonal = 0, 0, 1)
+ IIF(RChekin = 0, 0, 1)
+ IIF(RCalidad = 0, 0, 1)) AS Average_Score
FROM reviews.Reviews_respuestas

A case statement would look like

(CASE RHabitacion
WHEN 0 THEN 0
ELSE 1
END +
CASE RInstalaciones
WHEN 0 THEN 0
ELSE 1
END +
....)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County