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

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