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.

New contributor
E.Keogh is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
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 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 at 10:43
  • Try my answer given below. Let me know if it works for you. Aug 4 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 at 11:29
  • I have changed choice to Number and fixed it all Thank you very much @ganeshSanap
    – E.Keogh
    Aug 4 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 at 11:41

Your Answer

E.Keogh is a new contributor. Be nice, and check out our Code of Conduct.

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.