How to Calculate Average Value in SharePoint List?

I recently worked on a SharePoint project where I was required to calculate the average value in a SharePoint list. I will also explain the average() function in SharePoint Online.

Here, I will show you two different examples:

• We can use the average() function of a modern SharePoint list to calculate the average value of a column.
• I will also explain how to use a calculated column to calculate the average of three columns in a SharePoint list.

Average() Function in SharePoint

The average() function in SharePoint calculates the sum of all column items divided by the number of items. The average() function can only allow the numbers or references that contain numbers in the SharePoint Online list.

We can not use references from a number column and a text column together in the SharePoint Online average function.

We can only have 30 numeric arguments to calculate the average in the SharePoint Online modern list.

Syntax:

Below represents the SharePoint average function syntax:

=AVERAGE([number1],[number2],...)

Check out SharePoint Calculated Column Date Examples

Calculate Average Value in a SharePoint List

By using the default feature, you can calculate the average value of a column in a SharePoint list.

Follow the instructions below 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 that the total average of the Price column will be displayed in the SharePoint Online list.

This is how to calculate the average value of a column in a SharePoint list.

Check out LEN() Function in SharePoint Calculated Column

Calculate Average Value using a SharePoint Calculated Column

Here we will see how to calculate the average of three columns in a SharePoint Online list using a calculated Column.

For example, I have a SharePoint Online list titled ‘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. And in the formula bar, apply the code as shown below:

=AVERAGE([Keyboard Price],[Mouse Price],[Charger Price])

where

1. Average = This function is used to calculate the average value
2. Keyboard Price = Currency data type
3. Mouse Price = Currency data type
4. 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. You can see the average value in the screenshot below:

This is how to get the average value of multiple SharePoint list columns using a calculated column.

Conclusion

I hope you now understand how to get the average value of a SharePoint Online list column. Also, I have explained how to calculate the average value of multiple SharePoint list columns using a SharePoint calculated column.

You may also like the following tutorials:

• SharePoint List Calculated Column Concatenate() Function
• SharePoint List Calculated Column IF Statement

Preeti Sahu

Preeti Sahu is an expert in Power Apps and has over six years of experience working with SharePoint Online and the Power Platform. She is the co-author of Microsoft Power Platform: A Deep Dive book. As a Power Platform developer, she has worked on developing various tools using Power Apps and Power Automate. She also makes Microsoft 365 videos and shares them on YouTube.