Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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]))
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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