by The average function draws a lot from Excel, where we can have one or more columns (or cells in Excel) and return the average of all numbers. So let’s check how to use it efficiently.
Usage
It follows a simple pattern.
- One or more column
For this article, we’ll build a new List with the following structure:
The calculated will contain the following formula:
=AVERAGE([Number1],[Number2])
will return
7.5
Now let’s try with a choice column. We can have numbers in it so we can try to do the average of all of the selected ones, with the following formula:
=AVERAGE(Choices)
will return
#DIV/0!
Unfortunately, it’s impossible to use Choice columns to calculate the average, even if they are numbers.
Now let’s check if we put a text column in the middle of the other ones and see what happens.
=AVERAGE([Number1],[Number2],[Text])
will return
7.5
The return makes sense since the Text field is empty. So now, let’s put a number there.
We would expect an error because we’re using a text field, but SharePoint ignores the field, even if there’s a string instead of a number.
Finally, let’s try only with one number.
=AVERAGE(Number1)
will return
5
The average is calculated even if there’s only one number.
Limitations
The average function is limited to 30 parameters, so you can only have 30 numbers or reference columns to calculate the average.
Recommendations:
Here are some things to keep in mind.
Don’t hardcode values in the formula.
I didn’t mention this in the examples, but you can have the following formula:
=AVERAGE(Number1,10)
It rarely makes sense to have a fixed number in the formula. Since the formula is not visible in the list, only the result, you may have unexpected results since there is one (or more) extra value in the calculation.
“Hidden” Validation Columns
Since SharePoint works with columns, you can create hidden “validation” columns with the functions validating the data. After that, you can expose to the user the “message” columns that have useful error messages or actions that they can take to solve the problem.
Always deal with errors.
Leaving them for the user to see is horrible, but also you can bring trouble to your own code. If you don’t validate a column, it can be used in other calculations, and the error will cascade. You can have an error in one column, and the issue could be in whole different columns, so deal with issues where they happen.
Don’t nest it.
There’s no real reason to do it, but if you find yourself in a situation where you have nested Average functions in a formula, you should review it and make everything more straightforward.
Sources:
Microsoft’s Average Function Reference
Introduction to SharePoint formulas and functions
Back to the SharePoint’s list formula reference.
Photo by Vedrana Filipović on Unsplash
