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.


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)

Calculate Average Sales per Month with AVERAGE Function

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)

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 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

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