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.
Read More: How to Calculate Total Sales in Excel
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)Read More: How to Calculate Percentage of Sales in Excel
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.
Related Articles
- How to Calculate Sales Mix with Formula in Excel
- How to Calculate Sales Conversion Rate Using Formula in Excel
- How to Use Sales Run Rate Formula in Excel
- Gross Sales Formula in Excel
- How to Use Sales Revenue Formula in Excel
- How to Calculate Projected Sales in Excel
- How to Calculate Annual Sales in Excel
- How to Calculate Average Sales in Excel
