Md. Shamim Reza
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.
Download Practice Workbook
You can download the practice workbook from the download button below.
2 Ways to Calculate Average Sales per Month in Excel
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 Average Sales in Excel (5 Simple Approaches)
2. Get Average Sales per Month with 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 Annual Sales in Excel (4 Useful Methods)
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.
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
- Forecast Sales Using Historical Data in Excel (6 Methods)
- Calculate Sales Growth over 5 Years in Excel (3 Ideal Examples)
- How to Calculate Projected Sales in Excel (2 Easy Ways)
- Forecast Sales Using Regression Analysis in Excel (3 Methods)
- How to Forecast Sales Growth Rate in Excel (6 Methods)
- Calculate Sales Growth Percentage in Excel (2 Easy Approaches)
- How to Calculate Percentage of Sales in Excel (5 Suitable Methods)
