0

I am looking for generating the number of entries which fills into defined min and max values from the given data.

To put it more simple, If the data set is

Roll No: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20

The corresponding Marks are: 47, 23, 47, 45, 26, 41, 35, 39, 41, 32, 32, 29, 27, 28, 26, 41, 23, 24, 20, 26

Then the expected out put should be

Range: number

20-24: 4

25-29: 6

30-34: 1

35-39: 3

40-44: 3

45-50: 3

If required, I should be in a position to change the range (for example, 20-24 changed as 21-25), accordingly it has to calculate number of entries.

Regards

Anil

1 Answer 1

2

It's not sorting, but called binning, or calculating frequencies, you need to use FREQUENCY:

enter image description here

The formula I've used in column E is:
=FREQUENCY($A$2:$A$21,$C$2:$C$7-1)

  • it's an array formula, so you need to select first all the cells it will contain it, enter the formula, then finish it by CTRL+SHIFT+ENTER
  • First frequency calculated is the number of items which are smaller then lowest bound (first cell of second argument), so you either will have a 0 at the beginning, or just exclude first cell from second parameter.

For categories I've used this formula in column D:
=C2&"-"&C3-1
so editing values in column C will automatically update both labels and values.

3
  • Dear Juhaz, I did what you have suggested / formula in E1 and hit CTRL+SHIFT+ENTER. It shows 0. Copy the formula and dragged to E7. but everywhere it shows 0. Any hint / suggestion? Where am i making error? - Anil
    – Anil
    May 2, 2016 at 13:20
  • select E1:E7, type the formula, then press CTRL+SHIFT+ENTER May 2, 2016 at 13:21
  • Thank you. I tried even modifying the "C" column and automatically the numbers have changed.
    – Anil
    May 2, 2016 at 13:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .