Want to ignore blank values for calculations

[Guest]

I have a simple database form used to collect numerical values(p1, p2 ....up
to p8). I want to perform some calculations on the values that are input but
all the boxes are not always used so I want to ignore unfilled boxes. As a
simple example: I would like to calculate the average of the input numbers
and if only 3 out of the 8 boxes are filled, I get an error. My control is:
=avg([p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]+[p8]). I want blanks to be ignored.

 

[Douglas J Steele]

Try using the Nz function to convert the Null values to zeroes:

=avg(Nz([p1],0)+Nz([p2],0)+Nz([p3],0)+Nz([p4],0)+Nz([p5],0)+Nz([p6],0)+Nz([p
7],0)+Nz([p8],0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a simple database form used to collect numerical values(p1, p2 .....up
to p8). I want to perform some calculations on the values that are input but
all the boxes are not always used so I want to ignore unfilled boxes. As a
simple example: I would like to calculate the average of the input numbers
and if only 3 out of the 8 boxes are filled, I get an error. My control is:
=avg([p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]+[p8]). I want blanks to be

ignored.

 

[John Spencer]

Well, the biggest problem is that your data is not normalized. Your table
really should look more like
RowNumberField (Some way to identify what was a row in your original table)
ValueName (p1, p2, etc)
FieldValue (value you currently store in p1, p2, etc.)

If you are trying to generate an average of those eight values for the row.
You will have to use something along the lines of

= (Nz(P1,0) + Nz(P2,0) ...+Nz(P3,0)) / ( IIF(P1 is null,0,1) + IIF(P2 is
Null,0,1) ...IIF(P8 is Null,0,1))

You could normalize the data using a union all query and then use that for
your calculations. You would need another field in the original table to
uniquely identify each row. An autonumber field would work fine.

Sample UNION ALL query
SELECT AutonumberField, P1 as FieldValue, "P1" as ValueName
FROM YourTable
UNION ALL
SELECT AutonumberField, P2, "P2"
FROM YourTable
UNION ALL
....
SELECT AutonumberField, P8, "P8"
FROM YourTable

Now using that saved query as a basis, you could do

SELECT AutonumberField
, Avg(FieldValue) as AverageValue
, Count(FieldValue) as FieldsWithValue
, Min(FieldValue) as MinValue
, Max(FieldValue) as MaxValue
FROM [TheSavedQuery]
GROUP BY AutoNumberField

If you wanted the average for P1 to P8 in the entire set
SELECT ValueName
, Avg(FieldValue) as AverageValue
FROM [TheSavedQuery]
GROUP BY ValueName

 

[Guest]

I keep getting #error as the result. Also, the values shouldn't be zero but
ignored. If they are converted to zero than the blanks will pull the average
down.

Try using the Nz function to convert the Null values to zeroes:

=avg(Nz([p1],0)+Nz([p2],0)+Nz([p3],0)+Nz([p4],0)+Nz([p5],0)+Nz([p6],0)+Nz([p
7],0)+Nz([p8],0))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a simple database form used to collect numerical values(p1, p2 .....up
to p8). I want to perform some calculations on the values that are input but
all the boxes are not always used so I want to ignore unfilled boxes. As a
simple example: I would like to calculate the average of the input numbers
and if only 3 out of the 8 boxes are filled, I get an error. My control is:
=avg([p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]+[p8]). I want blanks to be

ignored.

 

[Guest]

The table I use has the date as my row identifier followed by the values of
p1, p2 ... p8. The table currently has several hundred rows of data with
more added each day. The form works fine if I put a value in each form box
and calculate the average by =avg([p1]+[p2]...+[p8]/8. If I leave one box
blank then #error.

Well, the biggest problem is that your data is not normalized. Your table
really should look more like
RowNumberField (Some way to identify what was a row in your original table)
ValueName (p1, p2, etc)
FieldValue (value you currently store in p1, p2, etc.)

If you are trying to generate an average of those eight values for the row.
You will have to use something along the lines of

= (Nz(P1,0) + Nz(P2,0) ...+Nz(P3,0)) / ( IIF(P1 is null,0,1) + IIF(P2 is
Null,0,1) ...IIF(P8 is Null,0,1))

You could normalize the data using a union all query and then use that for
your calculations. You would need another field in the original table to
uniquely identify each row. An autonumber field would work fine.

Sample UNION ALL query
SELECT AutonumberField, P1 as FieldValue, "P1" as ValueName
FROM YourTable
UNION ALL
SELECT AutonumberField, P2, "P2"
FROM YourTable
UNION ALL
....
SELECT AutonumberField, P8, "P8"
FROM YourTable

Now using that saved query as a basis, you could do

SELECT AutonumberField
, Avg(FieldValue) as AverageValue
, Count(FieldValue) as FieldsWithValue
, Min(FieldValue) as MinValue
, Max(FieldValue) as MaxValue
FROM [TheSavedQuery]
GROUP BY AutoNumberField

If you wanted the average for P1 to P8 in the entire set
SELECT ValueName
, Avg(FieldValue) as AverageValue
FROM [TheSavedQuery]
GROUP BY ValueName

I have a simple database form used to collect numerical values(p1, p2
....up
to p8). I want to perform some calculations on the values that are input
but
all the boxes are not always used so I want to ignore unfilled boxes. As
a
simple example: I would like to calculate the average of the input numbers
and if only 3 out of the 8 boxes are filled, I get an error. My control
is:
=avg([p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]+[p8]). I want blanks to be
ignored.

 

[BruceM]

John's solution should work. Have you tried it? Nz is a function for
dealing with null values. The 0 after the comma means that if a field is
null, replace the null with 0 for purposes of the calculation. The part of
the formula after the slash (the denominator) assigns a value of 0 if the
field is null, and 1 if it is not. If five fields contain values and five
contain nothing, the denominator is five. The total of the values in all of
the fields (remember that null fields will not affect the total since they
have been assigned the value 0) divided by the number of fields containing a
value will give you the average.

The table I use has the date as my row identifier followed by the values
of
p1, p2 ... p8. The table currently has several hundred rows of data with
more added each day. The form works fine if I put a value in each form
box
and calculate the average by =avg([p1]+[p2]...+[p8]/8. If I leave one box
blank then #error.

Well, the biggest problem is that your data is not normalized. Your
table
really should look more like
RowNumberField (Some way to identify what was a row in your original
table)
ValueName (p1, p2, etc)
FieldValue (value you currently store in p1, p2, etc.)

If you are trying to generate an average of those eight values for the
row.
You will have to use something along the lines of

= (Nz(P1,0) + Nz(P2,0) ...+Nz(P3,0)) / ( IIF(P1 is null,0,1) + IIF(P2 is
Null,0,1) ...IIF(P8 is Null,0,1))

You could normalize the data using a union all query and then use that
for
your calculations. You would need another field in the original table to
uniquely identify each row. An autonumber field would work fine.

Sample UNION ALL query
SELECT AutonumberField, P1 as FieldValue, "P1" as ValueName
FROM YourTable
UNION ALL
SELECT AutonumberField, P2, "P2"
FROM YourTable
UNION ALL
....
SELECT AutonumberField, P8, "P8"
FROM YourTable

Now using that saved query as a basis, you could do

SELECT AutonumberField
, Avg(FieldValue) as AverageValue
, Count(FieldValue) as FieldsWithValue
, Min(FieldValue) as MinValue
, Max(FieldValue) as MaxValue
FROM [TheSavedQuery]
GROUP BY AutoNumberField

If you wanted the average for P1 to P8 in the entire set
SELECT ValueName
, Avg(FieldValue) as AverageValue
FROM [TheSavedQuery]
GROUP BY ValueName

I have a simple database form used to collect numerical values(p1, p2
....up
to p8). I want to perform some calculations on the values that are
input
but
all the boxes are not always used so I want to ignore unfilled boxes.
As
a
simple example: I would like to calculate the average of the input
numbers
and if only 3 out of the 8 boxes are filled, I get an error. My
control
is:
=avg([p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]+[p8]). I want blanks to be
ignored.

 

[Guest]

Yes it works now. Your explanation helps me understand how it works so I was
able to find an error in my typing.

Thanks

John's solution should work. Have you tried it? Nz is a function for
dealing with null values. The 0 after the comma means that if a field is
null, replace the null with 0 for purposes of the calculation. The part of
the formula after the slash (the denominator) assigns a value of 0 if the
field is null, and 1 if it is not. If five fields contain values and five
contain nothing, the denominator is five. The total of the values in all of
the fields (remember that null fields will not affect the total since they
have been assigned the value 0) divided by the number of fields containing a
value will give you the average.

The table I use has the date as my row identifier followed by the values
of
p1, p2 ... p8. The table currently has several hundred rows of data with
more added each day. The form works fine if I put a value in each form
box
and calculate the average by =avg([p1]+[p2]...+[p8]/8. If I leave one box
blank then #error.

Well, the biggest problem is that your data is not normalized. Your
table
really should look more like
RowNumberField (Some way to identify what was a row in your original
table)
ValueName (p1, p2, etc)
FieldValue (value you currently store in p1, p2, etc.)

If you are trying to generate an average of those eight values for the
row.
You will have to use something along the lines of

= (Nz(P1,0) + Nz(P2,0) ...+Nz(P3,0)) / ( IIF(P1 is null,0,1) + IIF(P2 is
Null,0,1) ...IIF(P8 is Null,0,1))

You could normalize the data using a union all query and then use that
for
your calculations. You would need another field in the original table to
uniquely identify each row. An autonumber field would work fine.

Sample UNION ALL query
SELECT AutonumberField, P1 as FieldValue, "P1" as ValueName
FROM YourTable
UNION ALL
SELECT AutonumberField, P2, "P2"
FROM YourTable
UNION ALL
....
SELECT AutonumberField, P8, "P8"
FROM YourTable

Now using that saved query as a basis, you could do

SELECT AutonumberField
, Avg(FieldValue) as AverageValue
, Count(FieldValue) as FieldsWithValue
, Min(FieldValue) as MinValue
, Max(FieldValue) as MaxValue
FROM [TheSavedQuery]
GROUP BY AutoNumberField

If you wanted the average for P1 to P8 in the entire set
SELECT ValueName
, Avg(FieldValue) as AverageValue
FROM [TheSavedQuery]
GROUP BY ValueName


I have a simple database form used to collect numerical values(p1, p2
....up
to p8). I want to perform some calculations on the values that are
input
but
all the boxes are not always used so I want to ignore unfilled boxes.
As
a
simple example: I would like to calculate the average of the input
numbers
and if only 3 out of the 8 boxes are filled, I get an error. My
control
is:
=avg([p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]+[p8]). I want blanks to be
ignored.

 

[John Spencer]

Then if the DATE is unique within the database, you could use the
normalizing union query I proposed to as the base query. You will have to
enter the union query in the SQL text grid.

SELECT [DateField], P1 as FieldValue, "P1" as ValueName
FROM YourTable
UNION ALL
SELECT [DateField], P2, "P2"
FROM YourTable
UNION ALL
....
SELECT [DateField], P8, "P8"
FROM YourTable
Save the above as "qryBase" or whatever name you wish to use

Now using that saved query as a basis, you could do

SELECT [DateField]
, Avg(FieldValue) as AverageValue
, Count(FieldValue) as FieldsWithValue
, Min(FieldValue) as MinValue
, Max(FieldValue) as MaxValue
FROM [qryBase]
GROUP BY [DateField]

Which would give you for each date
--The average of the filled-in fields
--A count of the number of fields with a value
--The minimum value
--The maximum value

The table I use has the date as my row identifier followed by the values
of
p1, p2 ... p8. The table currently has several hundred rows of data with
more added each day. The form works fine if I put a value in each form
box
and calculate the average by =avg([p1]+[p2]...+[p8]/8. If I leave one box
blank then #error.

Well, the biggest problem is that your data is not normalized. Your
table
really should look more like
RowNumberField (Some way to identify what was a row in your original
table)
ValueName (p1, p2, etc)
FieldValue (value you currently store in p1, p2, etc.)

If you are trying to generate an average of those eight values for the
row.
You will have to use something along the lines of

= (Nz(P1,0) + Nz(P2,0) ...+Nz(P3,0)) / ( IIF(P1 is null,0,1) + IIF(P2 is
Null,0,1) ...IIF(P8 is Null,0,1))

You could normalize the data using a union all query and then use that
for
your calculations. You would need another field in the original table to
uniquely identify each row. An autonumber field would work fine.

Sample UNION ALL query
SELECT AutonumberField, P1 as FieldValue, "P1" as ValueName
FROM YourTable
UNION ALL
SELECT AutonumberField, P2, "P2"
FROM YourTable
UNION ALL
....
SELECT AutonumberField, P8, "P8"
FROM YourTable

Now using that saved query as a basis, you could do

SELECT AutonumberField
, Avg(FieldValue) as AverageValue
, Count(FieldValue) as FieldsWithValue
, Min(FieldValue) as MinValue
, Max(FieldValue) as MaxValue
FROM [TheSavedQuery]
GROUP BY AutoNumberField

If you wanted the average for P1 to P8 in the entire set
SELECT ValueName
, Avg(FieldValue) as AverageValue
FROM [TheSavedQuery]
GROUP BY ValueName

I have a simple database form used to collect numerical values(p1, p2
....up
to p8). I want to perform some calculations on the values that are
input
but
all the boxes are not always used so I want to ignore unfilled boxes.
As
a
simple example: I would like to calculate the average of the input
numbers
and if only 3 out of the 8 boxes are filled, I get an error. My
control
is:
=avg([p1]+[p2]+[p3]+[p4]+[p5]+[p6]+[p7]+[p8]). I want blanks to be
ignored.