SharePoint 2007: Calculated column - formula for calculation based on column containing NA

Question

In a SharePoint list, I have 6 numeric columns that can contain numbers in the range (1,4). However, one or all of these columns may contain "NA" value.

There is a separate calculated column which contains the rounded average of the above 6 column values. The issue is -

1) The numeric columns does not allow any "NA"/"NULL"/"#N/A" value to be entered, so I cannot indicate to SharePoint that that particular value should not be included in the calculation.

2) If one of the numeric column values has NA, then I am forced to leave it as blank. But SharePoint includes this column to calculate average. For example, in this case, the AVERAGE() formula should consider only 5 columns instead of 6 columns.

Because of this, my resulting average value sometimes does not match the expected result.

Is there any way I can solve this issue?

Answer 1

In your calculated column, you could use the following formula:

=SUM([Column 1],[Column 2],[Column 3],[Column 4],[Column 5])/COUNT([Column 1],[Column 2],[Column 3],[Column 4],[Column 5])

It uses SUM() to total the columns and divide it by the number of columns that contain numeric values using COUNT(). This will have a divide by zero error all the columns don't contain numeric values. You could therefore use the following formula to check if the count of numeric columns is zero:

=IF(COUNT([Column 1],[Column 2],[Column 3],[Column 4],[Column 5])=0,"NA",SUM([Column 1],[Column 2],[Column 3],[Column 4],[Column 5])/COUNT([Column 1],[Column 2],[Column 3],[Column 4],[Column 5]))

Answer 2

If the column types are Number and a blank is acceptable for "na" then this will work:

=AVERAGE(Column1,Column2,Column3,Column4,Column5,Column6)

You should also add a validation rule to each column to only allow blank, 1, 2, 3 or 4.

If "na" is needed, or preferred, then the column types should be "Single Line of Text", with a default of "na" and a validation rule that only accepts "na", "1", "2", "3" or "4".

=sum(
  if(isnumber(value(Column1)),value(Column1),0),
  if(isnumber(value(Column2)),value(Column2),0),
  if(isnumber(value(Column3)),value(Column3),0),
  if(isnumber(value(Column4)),value(Column4),0),
  if(isnumber(value(Column5)),value(Column5),0),
  if(isnumber(value(Column6)),value(Column6),0))
/
  (
    if(iserror(Column1/1),0,1) +
    if(iserror(Column2/1),0,1) +
    if(iserror(Column3/1),0,1) +
    if(iserror(Column4/1),0,1) +
    if(iserror(Column5/1),0,1) +
    if(iserror(Column6/1),0,1)
  )

Here's the validation for the Single Line of Text version: