About the Data Sheet - The data
in this sheet is related to marks scored by 100 Students in a Statistical
Test.
Based on the data, we will use
Microsoft Excel Statistical functions to analyse the descriptive
statistics.
First Microsoft Excel Statistical
Function we will use is the Count Functions.
COUNT
FUNCTION
=COUNT(value 1,value 2, .....)
The COUNT
Function counts the number of Observations or Items in the Data. The first step
in the Statistical Analysis to check the size of the Data.
In the
Data Sheet, we have Data from A2:A101, A1 being the header of the Data.
=COUNT(A2:A101)
Click Enter
The Result is 100.
COUNTIF Function
=COUNTIF(range,
criteria)
COUNTIF function counts the number of cells within a
range that meets a single criteria which we specify.
Range of
the Data from which the Criteria needs to be compared with. Range in the
Statistics Marks Data Sheet will be A2:A101.
Criteria
can be specified as number like 20 or >20 or Cell where we put the criteria
like B4. In B4 cell we can give number 20 or >20. We can also specify the
Text like Names, etc.
When Text
is used it has to be typed in Double Quotes in Function or Text criteria can be
given a specific Cell.
In the
Data Sheet, we have Data from A2:A101, A1 being the header of the Data.
=COUNTIF(A2:A101,">20") Criteria should
be in Double Quotes.
Click Enter
The Result is 100.
All the Students score above 20.
The Criteria is directly typed in the function.
=COUNTIF($A$2:$A$101,”>20”) Result Number of
Students = 100
=COUNTIF($A$2:$A$101,”>30”) Result Number of Students = 98
=COUNTIF($A$2:$A$101,”>40”) Result Number of Students = 97
=COUNTIF($A$2:$A$101,”>50”) Result Number of Students = 93
=COUNTIF($A2$:$A$101,”>60”) Result Number of Students = 86
=COUNTIF($A$2:$A$101,”>70”) Result Number of Students = 73
=COUNTIF($A$2:$A$101,”>80”) Result Number of Students = 23
=COUNTIF($A$2:$A$101,”>90”) Result Number of Students = 11
=COUNTIF($A$2:$A$101,”>30”) Result Number of Students = 98
=COUNTIF($A$2:$A$101,”>40”) Result Number of Students = 97
=COUNTIF($A$2:$A$101,”>50”) Result Number of Students = 93
=COUNTIF($A2$:$A$101,”>60”) Result Number of Students = 86
=COUNTIF($A$2:$A$101,”>70”) Result Number of Students = 73
=COUNTIF($A$2:$A$101,”>80”) Result Number of Students = 23
=COUNTIF($A$2:$A$101,”>90”) Result Number of Students = 11
The Dollar Sign is put as Absolute Reference and this can be done
by clicking F4 on Keyboard.
Absolute Cell reference has to be fixed else the Data result will
be wrong.
Alternatively the Criteria can be given in a cell & Cell No
can be given in the function.
The Criteria are given in the Cells D5:D12. Double Quotes not
needed.
In the Cells D5:D12, Criteria included are
>20,>30,>40,>50,>60,>70,>80,>90 .
=COUNTIF($A$2:$A$101,D5)
=COUNTIF($A$2:$A$101,D6)
=COUNTIF($A$2:$A$101,D7)
=COUNTIF($A$2:$A$101,D8)
=COUNTIF($A$2:$A$101,D9)
=COUNTIF($A$2:$A$101,D10)
=COUNTIF($A$2:$A$101,D11)
=COUNTIF($A$2:$A$101,D12)
COUNTIFS Function
The COUNTIFS function applies multiple criteria to multiple cells
across multiple ranges and counts the number of times all criteria are met.
=COUNTIFS(criteria_range1,
criteria1, [criteria_range2, criteria2]…)
In the Data Sheet, we have
Data from A2:A101, A1 being the header of the Data. Two criterias are given in
the function below and the Two criterias are "<=" and
">=" in the cells reference in D15:D22 & E15:E22.
=COUNTIFS($A$2:$A$101,D15,$A$2:$A$101,E15)
=COUNTIFS($A$2:$A$101,D16,$A$2:$A$101,E16)
=COUNTIFS($A$2:$A$101,D17,$A$2:$A$101,E17)
=COUNTIFS($A$2:$A$101,D18,$A$2:$A$101,E18)
=COUNTIFS($A$2:$A$101,D19,$A$2:$A$101,E19)
=COUNTIFS($A$2:$A$101,D20,$A$2:$A$101,E20)
=COUNTIFS($A$2:$A$101,D21,$A$2:$A$101,E21)
=COUNTIFS($A$2:$A$101,D22,$A$2:$A$101,E22)
=COUNTIFS($A$2:$A$101,D16,$A$2:$A$101,E16)
=COUNTIFS($A$2:$A$101,D17,$A$2:$A$101,E17)
=COUNTIFS($A$2:$A$101,D18,$A$2:$A$101,E18)
=COUNTIFS($A$2:$A$101,D19,$A$2:$A$101,E19)
=COUNTIFS($A$2:$A$101,D20,$A$2:$A$101,E20)
=COUNTIFS($A$2:$A$101,D21,$A$2:$A$101,E21)
=COUNTIFS($A$2:$A$101,D22,$A$2:$A$101,E22)
The Result is below
The result gives the
number of students who scored between 21 & 30, 31 & 40, 41 & 50, 51
&60, etc. To avoid double counting the class intervals are given as 21
& 30. The Equals sign is also important along with > or < signs.
The COUNTIFS function can
also be used with Text criterias. Multiple criterias can be given in this
function.
No comments:
Post a Comment