This article illustrates how to calculate average sales per month in Excel. You can easily calculate the average sales per month by dividing the total sales in a year by 12. But if your dataset doesn’t show the total sales of a year, then you can use some Excel formulas to get the job done. Follow the article to calculate the average sales per month by using 2 such formulas in Excel.
How to Calculate Average Sales per Month in Excel: 2 Easy Methods
1. Calculate Average Sales per Month with AVERAGE Function
Assume you have the following dataset. It contains the monthly sales for the last 3 years. Now you need to calculate the average sales per month for each year for comparison.
You can easily do that using the AVERAGE function in Excel. Apply the following steps.
The AVERAGE function returns the average (arithmetic mean) of its arguments, which can be numbers or names, arrays, or references that contain numbers.
Steps:
- First enter the following formula in cell C18.
=AVERAGE(C5:C16)- After that, use the fill handle icon for other years.
2. Get Average Sales per Month with the AVERAGEIF Function
Now imagine you have the following dataset instead. It contains the monthly sales made by different sales representatives. Now you 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:
- First of all, enter the name of the month in cell F5 and the following formula in cell G5.
=AVERAGEIF(C5:C16,"="&F5,D5:D16)
- Now if you want to get the average sales per month for each sales representative, then enter their names in cell F5. Then apply the following formula in cell G5.
=AVERAGEIF(B5:B16,"="&F5,D5:D16)Things to Remember
- You need to choose the formula based on the type of your dataset.
- Enter the criteria properly while using the second formula to avoid any erroneous results.
Download Practice Workbook
Conclusion
Now you know how to calculate average sales per month in Excel. Do you have any further queries or suggestions? Please use the comment section below for that. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.
<< Go Back to Sales | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
