Excel - Frequency Help!

Question

So here is the question on my question sheet:

5. Below is data on output produced by 20 employees during one week.

1087 924 1265 1086 1134 850 1226 1028 1130 1166 1084 1012 1230 989 1129 792 1205 1182 1155 1160

i) Type the data in one column

ii) Now type class widths in the next column

700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000 2100 2200 2300 2400 2500 2600 

iii) Produce a grouped frequency distribution with class width of 100.

Now the instructions to do this are given but it doesn't seem to work for me. Here are the instructions:

Highlight the cells next to the class widths(i think that's the first column of data) - Formulas - More functions - Statistical - Frequency - Highlight the data( I think thats the first column again) - Then clicks bins array - Highlight class widths - Press control, shift, return( all together)

If anyone can solve this it would be amazing and only takes 5 minutes but it does not work for me and I thank you in advance if you can help me or send it to me if it works for you!

Answer 1

the frequency command takes two arguments.  

=Frequency(data cells, bin cells)

Say you have your data in A1:A20 and your bins in B2:B20

Select C1:C20.  Now go to the formulas tab and select the statistical functions, then choose frequency

Click in the data_array box then select A1:A20 with your mouse

Now Click in the Bins_Array box then select B1:B20 with your mouse

now Hold down the Ctrl and Shift Keys and hit enter.  

This will enter the formula  =Frequency(A1:A20,B1:B20) in cells C1:C20.

This is a multicell array formula.   if you select any cell in C1:C20 it will appear as

{=FREQUENCY(A1:A20,B1:B20)}

The {} are not really there - they are just how Excel marks the formula as being interpreted as an array formula.  This is necessary because Frequency is returning multiple values rather than just 1 value like most functions.

--

Regards,

Tom Ogilvy

Answer 2

Thank you so much for this! :)