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.

Improve this question
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? Commented 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. Commented Aug 4, 2022 at 10:43
  • Try my answer given below. Let me know if it works for you. Commented Aug 4, 2022 at 10:58

1 Answer 1

Reset to default
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

Improve this answer
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 Commented Aug 4, 2022 at 11:29
  • I have changed choice to Number and fixed it all Thank you very much @ganeshSanap Commented 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. Commented Aug 4, 2022 at 11:41

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.