1

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?

0

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]))
0

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.

enter image description here

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)
  )

enter image description here

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

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.