In this SharePoint Online tutorial, I will explain about Average function in SharePoint Online, and also, I will show you how to calculate the SharePoint list column average. Also, we will see a few points to remember while using the SharePoint list average function.
Average Function in SharePoint Online
The average function can only allow the numbers or references that contain numbers in the SharePoint Online list. It calculates the sum of all column items divided by the number of items.
Below represents the SharePoint average function syntax:
We have some limitations to calculating an average in the SharePoint Online list. They are:
- We can not use references from a number column and text column together in SharePoint Online average function.
- We can only have 30 numeric arguments to calculate the average in the SharePoint Online list.
Calculate SharePoint List Column Average
Microsoft has provided that we can calculate an average based on a SharePoint Online list column without using formulas and creating a Calculated column.
Follow the below instructions to calculate the average value in the SharePoint list:
- Open the SharePoint Online list -> Expand the existing column (Price) where we can find the average of the total Price like below:
- Expand the Totals option -> Click on the Average option.
- Now we can see the total average of the Price column will be shown in the SharePoint Online list.
This is how to calculate the average in SharePoint List [without creating the calculated column].
Calculate an Average in a SharePoint list [With Calculated Column]
Here we will see how to calculate an average using three columns in SharePoint online list using a calculated Column.
For example, I have a SharePoint Online list regarding Product Order Details with three currency columns.
- Keyboard Price
- Mouse Price
- Charger Price
I want to calculate the Averages of these three currency columns using the Average function in SharePoint.
- Now, we will create a SharePoint Calculated column (Average Price).
- Click on the +Add column -> Select See all column types -> Click on the Next button like below:
- On the Column settings page, Provide the details below:
- Column name = Provide a new column name (Average Price)
- The type of information in this column is: = Select Calculated (calculation based on other columns)
- In the Additional Column Settings section, provide the description.
- In the formula bar, apply the code as shown below:
=AVERAGE([Keyboard Price],[Mouse Price],[Charger Price])
- Average = This function is used to calculate the average value
- Keyboard Price = Currency data type
- Mouse Price = Currency data type
- Charger Price = Currency data type
- The data type returned from this formula is – choose Number. Choose other data types as per our requirements.
- Number of decimal places: Choose 0 to 5, based on how many decimals should be visible.
- Click on the Ok button.
- The Average Price (calculated column) will be added to our SharePoint Online list.
This is how to create an average function in the SharePoint Online list.
I hope you got an idea of the Average Function in SharePoint Online. And also I have explained how to calculate the SharePoint Online list column average.
You may also like:
- SharePoint list conditional formatting based on a date
- Add Discussion Board to SharePoint Online
- Rename a Document Library in SharePoint
- Add Comments to SharePoint Online List Item
Preeti Sahu is an expert in Power Apps and has more than 6 years of experience working with SharePoint and the Power Platform. As a Power Platform expert for Power BI, Power Apps, Power Automate, Power Virtual Agents, and Power Pages, she is currently employed with TSinfo Technologies. She is the author of the book Microsoft Power Platform A Deep Dive. She also made a big technical contribution to SharePointDotNet.com in the form of articles on the Power Platform. She enjoys traveling and spending time with her family in her spare time.