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

1 comment:

  1. Excellent post. I will be dealing with some of these issues as well..

    ReplyDelete

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts