How to Calculate Average in SharePoint Online List

In this Online SharePoint tutorial, we will learn How to Calculate Average in SharePoint Online List. Also, we will discuss the below topics such as:

• Calculate Average in SharePoint List [Without Creating Calculated Column]
• How to Calculate Average in SharePoint Online list in Excel
• Working with Calculate Average in SharePoint Online list Date
• How to Calculate Average in SharePoint Online list Sum
• Calculate Average in SharePoint Online list Limit

Also, Read: SharePoint Online List Version History

Calculate Average in SharePoint Online List

First of all, let me explain what does mean to Calculate Average in SharePoint Online List.

• For example, I have a SharePoint Online list Order Forms. This SharePoint list has various columns with different data types as shown below:
  • Customer ID = Single line of text
  • Furniture ID = Single line of text
  • Order Date = Date and Time
  • Delivery Date = Date and Time
  • Payment = Choice
  • Order Quantity = Number
• Among all these columns, I have two more columns i.e. Price (Currency) and Discount (Number).
• Now I would like to calculate the average value of these two number columns. For this, we will create a SharePoint Calculated Column.
• By using the SharePoint Calculated column, we can do a kind of mathematical operations like Addition, Subtraction, Multiplication, Division, Average, etc.

Syntax:

• Below represents the SharePoint Average function syntax:

=AVERAGE([Price],[Discount])

Where we can use various columns with different data types like the below:

1. Average = This function is used to calculate the SharePoint average value
2. Price = SharePoint Number column
3. Discount = SharePoint Number column

Note:

Average function can only allow the numbers or references that contain numbers in the SharePoint Online list.

Refer to the screenshot below that how the SharePoint list (Order Forms) looks like:

Follow the steps below to create a SharePoint Calculated column and calculate the average value:

• To create a Calculated column, Click on the +Add column -> Select 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 Value)
  • The type of information in this column is: = Select Calculated (calculation based on other columns)

• In the Additional Column Settings section, apply the code below inside the formula bar:

=AVERAGE([Price],[Discount])

1. Average = This function is used to calculate the average value
2. Price = Currency data type
3. Discount = Number data type

• By default, the data type returned from this formula is – Single line of text. And also we choose other data types as per our requirements.
• Click on the Ok.

• Now in the SharePoint list, the calculated column (Average Value) has been added with the calculated values as in the image below.

This is how to create an average function in the SharePoint Online list.

Check out: SharePoint Phone Number Validation [With Examples]

Calculate Average in SharePoint List [Without Creating Calculated Column]

Similarly, we can calculate the average value without creating a Calculated column or without using any formulas in the SharePoint Online list.

Follow the below instructions to calculate the average value in the SharePoint list:

• Open the SharePoint Online list (Order Forms) -> Expand the existing column (Price) where we can find the average of the total Price like below:

• In the Price column window -> Select the Totals option -> Click on the Average option.

• Now we can see the total average calculated value of the Price column as shown in the figure below.

This is how to calculate average in SharePoint List [without creating calculated column].

Have a look: SharePoint Online List Calendar View Color Coding

Calculate Average in SharePoint Online list in Excel

Likewise, in this section, we will learn to Calculate Average in SharePoint Online list in Excel. Follow the below steps to create the average function in Excel:

• Open the SharePoint Online list (Order Forms) from Excel. To view the SharePoint list in Excel, export and import it in an Excel sheet. To know more, check this detailed post: How to Export SharePoint Online List to Excel
• In Excel, Create a new column (Average Value).

• In the Average Value field, apply the code below inside the formula bar:

=AVERAGE([Price],[Discount])

• Once you applied the formula, the average calculated column (Average Value) will appear with all the calculated values as shown below.

This is how to calculate average in the SharePoint Online list using Excel.

Calculate Average in SharePoint Online list Date

In the SharePoint Online list, we can Calculate Average in SharePoint Online list Date. Follow the below steps to calculate the average:

For example, In the SharePoint list (Order Forms), there are two Date columns i.e. Order Date and Delivery Date. Now I would like to calculate the number of days between these two dates using the Average function.

• To work around this, we will create a SharePoint Calculated column (No Of Average Dates).

• In the Additional Column Settings section, apply the code below inside the formula bar:

=AVERAGE([Delivery Date]-[Order Date])

Where,

1. Average = This function is used to calculate the day difference between two dates
2. Delivery Date = Date and Time column
3. Order Date = Date and Time column

• Click on the Ok button.

• Now in the SharePoint list (Order Forms), we can see the Average calculated column (No Of Average Dates) will appear with all the date difference values as below.

This is how to calculate average dates in the SharePoint Online list.

Read: How to Change SharePoint Online Team Site to Microsoft Teams

Calculate Average in SharePoint Online list Sum

In this section, we will discuss how to Calculate Average in SharePoint Online list Sum. Follow the below instructions:

For example, In the SharePoint list (Careers), there are three currency columns i.e. Monthly Salary, EPF, and Medical Allowances. Now I would like to calculate the Sum of Averages between three currency columns using the Average function.

• To work around this, we will create a SharePoint Calculated column (Sum Of Average).

• In the Additional Column Settings section, apply the code below inside the formula bar:

=AVERAGE([Monthly Salary ]+[EPF]+[Medical Allowances])

Where,

1. Average = This function is used to calculate the Sum of Averages between three currency columns
2. Monthly Salary = Currency column
3. EPF = Currency column
4. Medical Allowances = Currency column

• Click on the Ok button.

• Now in the SharePoint list (Careers), we can see the Average calculated column (Sum Of Average) will appear with Sum of Averages as shown below:

This is how to Calculate Average in SharePoint Online list Sum.

Calculate Average in SharePoint Online list Limit

In this section, we will discuss about calculate Average in SharePoint Online list Limit such as:

• We can not use a choice column to calculate the average function even though they are numbers in a SharePoint Online list.
• Calculate Average function is not possible for the SharePoint Online Lookup column.
• We can not use references from a Number column and Text column together in SharePoint Online average function.
•  We can only have 30 numbers or 30 reference columns to calculate the average in the SharePoint Online list Calculated column.

This is Calculate Average in SharePoint Online list Limit.

Moreover, you may like some more SharePoint Online tutorials:

• SharePoint Calculated Column Today [With Various Examples]
• SharePoint Document Library Check in and Check out
• How to Copy a SharePoint List
• SharePoint Online Document Library Permissions [Detailed Tutorial]

Conclusion

From this SharePoint Online tutorial, we learned how to Calculate Average in SharePoint Online List. And also we covered the below topics such as:

• Calculate Average in SharePoint List [Without Creating Calculated Column]
• How to Calculate Average in SharePoint Online list in Excel
• Working with Calculate Average in SharePoint Online list Date
• How to Calculate Average in SharePoint Online list Sum
• Calculate Average in SharePoint Online list Limit