1

Really struggling with formula on a calculated column on my SharePoint list for some reason!

I am trying to get the average of 3 columns sometimes one will be blank some times there will be no blanks.

Here is the formula I have:

=IF(COUNT([Column1],[Column2],[Column3])="","",SUM([Column1],[Column2],[Column3])/COUNT([Column1],[Column2],[Column3]))

On this test Column 2 is empty and this returns #DIV/0!

Any help would be gratefully received.

3
  • All 3 columns are "Number" columns? can you add the screenshot of list view (for sample data to test) to your question if possible? Aug 4, 2022 at 10:35
  • Hey, @GaneshSanap, Yes number columns, apologies, its a simple scoring system of 1,2 or 3. on some occasions it will be blank though.
    – E.Keogh
    Aug 4, 2022 at 10:43
  • Try my answer given below. Let me know if it works for you. Aug 4, 2022 at 10:58

1 Answer 1

0

If all 3 columns are of type "Number", use below formula:

=IF(COUNT([Column1],[Column2],[Column3])=0,0,SUM([Column1],[Column2],[Column3])/COUNT([Column1],[Column2],[Column3]))

I just tried it & it works for me:

enter image description here

Documentation: Examples of common formulas in lists

3
  • Thank you very much, I am however still getting a zero in average. I will list some more detail below. Column one and Column Three a choice of 1,2,3 Column two is a calculated column which has divided by two a choice column of 1,2,3,4,5,6 that gives an answer of 1,2 or 3 When Column Two is blank on mine I get 0 as the average using your formula
    – E.Keogh
    Aug 4, 2022 at 11:29
  • I have changed choice to Number and fixed it all Thank you very much @ganeshSanap
    – E.Keogh
    Aug 4, 2022 at 11:37
  • When you use the choice columns, retuned value of column will be text and not number. In such cases, use VALUE function like: VALUE([Column1]) and VALUE([Column2]). But it's always better to use the Number columns for calculations. Aug 4, 2022 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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