How to Calculate Average Sales per Month in Excel (2 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Calculate Average Sales per Month with AVERAGE Function

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)

Calculate Average Sales per Month with AVERAGEIF Function

  • 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

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo