How to Calculate Average by Month

To calculate average with given criteria we can apply AVERAGIFS function. If the criteria or condition is a period for example one or more months, we can provide month information and then calculate average based on the given month. There are some date and time functions to return months or dates can represent a month. In this article, we will show you how to calculate average by month with AVERAGEIFS function, we use logical expressions with EOMONTH function to represent month.

In this article, we will introduce you the syntax, arguments and basic usage of above-mentioned functions, and use them to build a formula, and let you know the calculation steps of the formula.

EXAMPLE

How to Calculate Average by Month 1

Refer to the left-hand side table, we can see some kinds of fruits are listed in “Fruit” column. Numbers in “Amount” column represent the sales on different dates. Dates belong to different months are recorded in “Date” column. In the right-hand side table, we want to calculate the average of amounts based on given months. In Month column, E2 and E3 are actually dates. For the following calculation we enter dates 05/20/2021 and 06/20/2021 into E2 and E3, and they are the earliest date shown in “Dates” column for May and June. But for looking clearly, we just show them in mmm-yyyy format and hide the date part.

How to Calculate Average by Month 2

To calculate the average of numbers based on given criteria, we need to apply AVERAGEIFS function here. In this case, our criteria is a whole month not a given period with start date and end date, so we can enter “DATE(2021,5,1)” as start date and “DATE(2021,5,31)” as end date. We can also apply EOMONTH function to return the last date of the given month.

Before creating a formula to calculate average value, we can name range references.

Select range B2:B13, in name box enter “Dates”, then press Enter.

Select range C2:C13, in name box enter “Amounts”, then press Enter.

How to Calculate Average by Month 3 How to Calculate Average by Month 4

CREATE A FORMULA with AVERAGEIFS & EOMONTH FUNCTIONS

1. STEPS

Step 1: In F2, enter the =AVERAGEIFS(Amounts,Dates,”>=”&E2,Dates,”<=”&EOMONTH(E2,0))

How to Calculate Average by Month 5

You can enter named range like “Amounts” into your formula directly, or you can also select the range by dragging the handle as well.

Step 2: Press Enter after typing the formula.

How to Calculate Average by Month 6

Pick up amounts from May, they are 5000 in C2, 4000 in C3, 6000 in C6, 5500 in C7, 8500 in C10, and 9000 in C11. Total 6 numbers. The average is (5000+4000+6000+5500+8500+9000)/6=6333.33. The formula works correctly.

2. FUNCTION INTRODUCTION

AVERGAEIFS function can be seen as AVERAGE+IFS. It returns the average of some numbers in range based on one or more given conditions or criteria.

Syntax:

=AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)

For AVERAGEIFS functions, it can handle wildcards like asterisk ‘*’ and question mark ‘?’; it also supports logical operations like ‘>=’,’<=’. If we need entering wildcards or logical operators to build criteria, they should be enclosed into double quotes (““).

EOMONTH function returns the last date for a given date.

Syntax:

 =EOMONTH(start_date, months)

Argument months is a serial number, n months represents n months before or after the start date. For example, if A1 is 05/20/2021, we enter =EOMONTH(A2, 1), it returns 06/30/2021 (set cell in proper date format). If we enter =EOMONTH(A2, -1) (negative value in months), it returns 04/30/2021. If we enter =EOMONTH(A2, 0), it returns 05/31/2021.

3. ALL ARGUMENTS

AVERAGEIFS – average range

Named range “Amounts” is the average range in this case.

In the formula bar, select “Amounts”, press F9, values in this range are expanded in an array.

How to Calculate Average by Month 7

AVERAGEIFS – criteria range1/criteria range2

Named range “Dates” is the criteria range. We have only one criteria range in this case.

In the formula bar, select “Dates”, press F9, numbers which represent corresponding dates in this range are expanded in an array.

How to Calculate Average by Month 8

AVERAGEIFS – criteria range1

The first criteria is “>=”&E2. Date 5/20/2021 is recorded in E2, in logical expression “>=”&E2, E2 is converted to a five digits number which represents the date 5/20/2021. To concentrate operator “>=” and E2, we use “&” to connect them.

In the formula bar, select “>=”&E2, press F9, number 44336 is displayed instead of the date in E2.

How to Calculate Average by Month 9

AVERAGEIFS – criteria range2

The second criteria is “<=”&EOMONTH(E2,0). EOMONTH function returns the last day of one month which is decided by the entered date. For E2 date is 5/20/2021, the second argument months is 0, so EOMONTH() returns the last day of May, it is 05/31/2021.

In the formula bar, select “<=”&EOMONTH(E2,0), press F9, number 44336 is displayed instead of the date 05/31/2021.

How to Calculate Average by Month 10

4. EXPLANATION

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=AVERAGEIFS({5000;4000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500},{44336;44337;44367;44369;44336;44337;44367;44369;44336;44337;44367;44369},">=44336",{44336;44337;44367;44369;44336;44337;44367;44369;44336;44337;44367;44369},"<=44347")

Now for the criteria range, we keep the numbers which can meet our two conditions “>=44336” and “<=44347”. So only numbers marked in bold meet our conditions.

{44336;44337;44367;44369;44336;44337;44367;44369;44336;44337;44367;44369}

For the qualified numbers, we mark “True” in the array, otherwise, “False” will be recorded. Then the original criteria range only contains “True” and “False”.

{True;True;False;Flase;True;True;False;Flase;True;True;False;Flase}

In logical expression, “True” is coerced to “1” and “False” is coerced to “0”. So, values in the array are converted to numbers:

{1;1;0;0;1;1;0;0;1;1;0;0}

After above steps, in current calculation step, we have two arrays.

{5000;4000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500}

– average range

{1;1;0;0;1;1;0;0;1;1;0;0}

– reference

Some numbers in average range are excluded in next calculation step if number’s corresponding value in reference range is 0. So only below numbers are kept and participate in next calculation step “calculate average”.

{5000;4000;0;0;6000;5500;0;0;8500;9000;0;0}

Calculate average of these number:

(5000+4000+6000+5500+8500+9000)/6=6333.33

SUMMARY

1.AVERAGEIFS function returns the average of some numbers refer to one or more given condition or criteria.

2. AVERAGEIFS can support wildcards, logical expressions.

Related Functions


  • Excel AVERAGEIFS function
    The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….

 

 

Related Posts
If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Fix #N/A Error For VLOOKUP From Another Sheet
vlookup from anther sheet not working1

This post will show you how to fix the #N/A error why it occurs when you extract values from another sheet using VLOOKUP function in Excel 2016,2013,2010 or other Excel versions. How can you correct a #N/A error in VLOOKUP ...

Sidebar