Statistical Function in Excel

 

Statistical Function

Average Function

 

=AVERAGE(C2:C9)

 

	A	B	C
1	Name	Product	Qty.
2	Ronak	Keybaord	7
3	Ram	Mouse	6
4	Deepak	Laptop	3
5	Rahul	PC	8
6	Nikunj	Laptop	4
7	Vivek	Mouse	8
8	Salini	Keybaord	2
9	Rahul	Laptop	4
		Average	5.25

 

Averagea Function

=AVERAGEA(C1:C9)

 

	A	B	C
1	Month	Product	Qty.
2	Jan	Keybaord	7
3	Feb	Mouse	6
4	Mar	Laptop	No Sale
5	Apr	PC	8
6	May	Laptop	4
7	Jun	Mouse	No Sale
8	Jul	Keybaord	2
9	Aug	Laptop	4
Average Qty. Including the No sale			3.875

 

Averageif Function

 

=AVERAGEIF(A2:A8,A11,C2:C8)

 

=AVERAGEIF(B2:B8,A14,C2:C8)

 

	A	B	C
1	Year	Product	Sale
2	2016	T-shirt	5000
3	2017	Jeans	2100
4	2016	Shirt	4000
5	2017	T-shirt	1300
6	2016	Shirt	1450
7	2017	Jeans	4000
8	2016	Shirt	3210
9
10	Year	Total Average Sale
11	2017	2466.666667
12
13	Product	Total Average Sale
14	Jeans	3050

 

Averageifs Function

 

Qty. Average-

 

=AVERAGEIFS(D2:D8,A2:A8,A11,B2:B8,B11)

 

Total Sale Average-

 

=AVERAGEIFS(E2:E8,A2:A8,A11,B2:B8,B11,D2:D8,D11)

Qty. Average-

 

=AVERAGEIFS(D2:D8,A2:A8,A14,B2:B8,B14)

 

	A	B	C	D	E
1	Year	Product	Product Size	Qty.	Sale
2	2016	T-shirt	L	12	5000
3	2017	Jeans	XL	15	2100
4	2016	Shirt	M	16	4000
5	2017	T-shirt	L	20	1300
6	2016	Shirt	M	30	1450
7	2017	Jeans	XL	50	4000
8	2016	Shirt	L	40	3210
9
10	Year	Product Name	Qty. Average	Total Sale Average
11	2016	T-shirt	12	5000
12
13	Year	Product Name	Qty. Average
14	2017	Jeans	32.5