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

1 comment:

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

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.