Excel AverageIf Function to calculate Average between two Dates
Suppose we have a Sales data table as follows :
Note :
To understand more about the AverageIf and AverageIfs , please go through the below link of this Blog Post.
http://excelkingdom.blogspot.in/2013/03/excel-averageif-and-averageifs-functions.html
Suppose we have a Sales data table as follows :
Prod_Id
|
Sal_Date
|
Net Sales
|
359
|
5/15/2015
|
1472
|
264
|
5/16/2015
|
6266
|
435
|
5/17/2015
|
8877
|
359
|
5/18/2015
|
3881
|
264
|
5/19/2015
|
7190
|
435
|
5/20/2015
|
1778
|
359
|
5/21/2015
|
9394
|
264
|
5/22/2015
|
4185
|
435
|
5/23/2015
|
3560
|
Suppose if we want to find the Average of Net Sales between two dates , we can find generally using AverageIf or AverageIfs Functions.
Also we can calculate the Average between two Dates using an Array based Average and If Combo Formula as shown below :
Cond_1:
|
Cond_2:
|
AVERAGE FORMULA
|
Result
|
5/18/2015
|
21-May-15
|
=AVERAGE(IF(($B$1:$B$10>=E2)*($B$1:$B$10<F2),C1:C10))
|
4283
|
Examples :
Source Data as follows :
Prod_Id
|
Month_No
|
Sales
|
359
|
3
|
1472
|
264
|
5
|
6266
|
435
|
7
|
8877
|
359
|
5
|
3881
|
264
|
4
|
7190
|
435
|
2
|
1778
|
359
|
5
|
9394
|
264
|
3
|
4185
|
435
|
2
|
3560
|
Calculating Average
based of Different Criteria as follows :
Cond_1:
|
Cond_2:
|
AVERAGE FORMULA
|
Result
|
3
|
5
|
{=AVERAGE(IF($B$1:$B$10={3,5},C1:C10))}
|
5039.6
|
359
|
5
|
{=AVERAGE(IF(($B$1:$B$10=5)*($A$1:$A$10=359),
C1:C10))}
|
6637.5
|
">=3"
&<"5"
|
264
|
{=AVERAGE(IF(($B$1:$B$10>=3)*($B$1:$B$10<5)*
($A$1:$A$10=264),C1:C10))}
|
5687.5
|
Note :
To understand more about the AverageIf and AverageIfs , please go through the below link of this Blog Post.
http://excelkingdom.blogspot.in/2013/03/excel-averageif-and-averageifs-functions.html
Thanks,
TAMATAM