Method 1 – Calculate the Average Sales per Month with the AVERAGE Function
The sample dataset contains the monthly sales for the last 3 years. We’ll calculate average monthly sales.
The AVERAGE function returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers.
Steps:
- Enter the following formula in cell C18.
=AVERAGE(C5:C16)- Use the fill handle icon for other years.
Method 2 – Get the Average Sales per Month with the AVERAGEIF Function
The next dataset contains the monthly sales made by different sales representatives. We need to calculate the average sales per month made by all employees.
You can do that with the AVERAGEIF function in Excel.
The AVERAGEIF function finds the average (arithmetic mean) for the cells specified by a given condition or criteria.
Steps:
- Enter the name of the month in cell F5 and the following formula in cell G5.
=AVERAGEIF(C5:C16,"="&F5,D5:D16)
- If you want to get the average sales per month for each sales representative, enter their names in cell F5, then apply the following formula in cell G5.
=AVERAGEIF(B5:B16,"="&F5,D5:D16)Download the Practice Workbook
<< Go Back to Sales | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
