frequency.xlsx
TRANSCRIPT
-
7/29/2019 FREQUENCY.xlsx
1/2
Excel Function Dictionary
1998 - 2000 Peter Noneley
FREQUENCY
Page 1 of 2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1819
20
21
22
23
24
25
26
27
28
29
3031
32
33
34
35
36
37
38
39
40
41
4243
444546
4748495051
A B C D E F G H I
FREQUENCY
Jan Feb Mar
North 5,000 6,000 4,500
South 5,800 7,000 3,000
East 3,500 2,000 10,000
West 12,000 4,000 6,000
Sales 4,000 and below. 4,000 4 {=FREQUENCY(D4:F7,E9:E11)}
Sales above 4,000 up to 6,000 6,000 5 {=FREQUENCY(D4:F7,E9:E11)}
Sales above 6,000 999,999 3 {=FREQUENCY(D4:F7,E9:E11)}
What Does It Do ?
This function compares a range of data against a list of intervals.
The result shows how many items in the range of data fall between the intervals.
The function is entered in the cells as an array, that is why it is enclosed in { } braces.
Syntax=FREQUENCY(RangeOfData,ListOfIntervals)
Formatting
No special formatting is needed.
Example 1
The following tables were used to record the weight of a group of children.
The =FREQUENCY() function was then used to calculate the number of children whose
weights fell between specified intervals.
Weight Kg Number Of Children:
Child 1 20.47 Between 0 - 15 Kg 2Child 2 22.83 Above 15 but less than or equal to 20 Kg 4
Child 3 15.74 Above 20 Kg 3
Child 4 10.80 {=FREQUENCY(C30:C38,C41:C43)}
Child 5 8.28 {=FREQUENCY(C30:C38,C41:C43)}
Child 6 20.66 {=FREQUENCY(C30:C38,C41:C43)}
Child 7 17.36
Child 8 16.67
Child 9 18.01
Kg Weight Intervals
15
20100
Example 2
This example uses characters instead of values.A restaurant has asked 40 customers for their rating of the food in the restaurant.The ratings were entered into a table as a single letter, E, V, A, P or D.The manager now wants to calculate how many responses fell into each category.Unfortunately, the =FREQUENCY() function ignores text entries, so how can the frequency
-
7/29/2019 FREQUENCY.xlsx
2/2
Excel Function Dictionary
1998 - 2000 Peter Noneley
FREQUENCY
Page 2 of 2
5253545556
575859606162636465666768697071
A B C D E F G H I
of text be calculated?
The answer is to use the =CODE() and =UPPER() functions.The =UPPER() forces all the text entries to be considered as capital letters.The =CODE() function calculates the unique ANSI code for each character.
As this code is a numeric value, the =FREQUENCY() function can then be used!
Rating FrequencyExcellent E 6 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}
Very Good V 8 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}Average A 9 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}
Poor P 8 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}Disgusting D 9 {=FREQUENCY(CODE(UPPER(B67:I71)),CODE(UPPER(C60:C64)))}
Customer RatingsV D V A p A D DV P a D A P V dA V E P p E D AA E d V D P a EV e P P A V E D