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/excelaverageifandaverageifsfunctions.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

21May15

=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/excelaverageifandaverageifsfunctions.html
Thanks,
TAMATAM
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your open Feedback/Comments/Query.