Average Ignoring Blank Columns

Question

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.

All 3 columns are "Number" columns? can you add the screenshot of list view (for sample data to test) to your question if possible? — Ganesh Sanap - MVP, 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

Ganesh Sanap - MVP · Accepted Answer · 2022-08-04 10:57:20Z

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:

Documentation: Examples of common formulas in lists

answered Aug 4, 2022 at 10:57

42.8k21 gold badges30 silver badges58 bronze badges

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.
– Ganesh Sanap - MVP
Aug 4, 2022 at 11:41

Add a comment |

1 Answer 1