AVERAGEIF
This function use for advance average, when you need to calculate average specific product/subject from many product/subject data table. Averageif is a conditional average function. It function calls column 1 cell address for criteria/conditions and column 2 for data calculation. Here, I give an example for your practice:
B2:B10 cells refers fruits name and C2:C10 column refers price per kilogram.
This function use for advance average, when you need to calculate average specific product/subject from many product/subject data table. Averageif is a conditional average function. It function calls column 1 cell address for criteria/conditions and column 2 for data calculation. Here, I give an example for your practice:
A
|
B
|
C
|
D
|
E
|
F
|
|
1
|
Date
|
Fruits
|
Price/KG
|
Average Price (3 Months)
|
Calculation
|
|
2
|
Jan-15
|
Apple
|
120
|
Apple
|
155
|
=AVERAGEIF(B2:B10,
"Apple",C2:C10)
|
3
|
Orange
|
80
|
||||
4
|
Grapes
|
220
|
||||
5
|
Feb-15
|
Apple
|
160
|
Orange
|
100
|
=AVERAGEIF(B2:B10,
"Orange",C2:C10)
|
6
|
Orange
|
120
|
||||
7
|
Grapes
|
200
|
||||
8
|
Mar-15
|
Apple
|
180
|
Grapes
|
200
|
=AVERAGEIF(B2:B10,
"Grapes",C2:C10)
|
9
|
Orange
|
100
|
||||
10
|
Grapes
|
160
|
||||
B2:B10 cells refers fruits name and C2:C10 column refers price per kilogram.
Explain:
The formula
=AVERAGEIF(B2:B10,"Apple",C2:C10)
Means,
= [calculate]
Averageif( [calculate Average, formulla start]
B2:B10 [conditions cells]
"Apple" [find conditions cells who are "Apple"]
C2:C10 [sum data cells who are against "Apple", count data cells who are against "Apple", sum data divide by count data]
) [formulla end]
No comments:
Post a Comment