Sunday, 31 May 2015

How to calculate Average between two Dates in Excel

Excel AverageIf Function to calculate Average between two Dates
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

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts