Section:Sorting Numbers

[HighlandMick]

Hi everyone

Im new to this forum, and I have a largish project to do with excel.

I know only the basics of working with excel. I really could use some proper help.

The next problem I have is this :

I have a set of six numbers, each on the ame row, and each in their own cell.
in numerical order :-)
What I want to do is classify these numbers into groups, and then count the number of "numbers" in each group. for example, the groups are
1-10
11-20
21-30
31-40
41-50

I have a row of six numbers

6,15,20,35,45,50

I want to group these numbers into the right group (according to value)

Then count how many of each number fell into each group

in this example, I would have
1 number in group 1
2 numbers in group 2
0 numbers in group 3
1 number in group 4
2 numbers in group 5

and the preferable output of this grouping would be

12012 in its own cell adjacent to the group of six numbers.

How can I achieve this using Excel ?

Hope that makes sense. My head is about spinning :-)

Michael

[royUK]

Can you attach a small example workbook?

[modytrane]

Look at the attached example.
You might get some ideas about formulas.
Otherwise, attach your workbook as suggested by Roy.
modytrane

[HighlandMick]

Hi

I have attatched the file, and added info.

Hope that makes sense to you.

thanks :-)

Michael

[modytrane]

SEE THE ATTACHED FILE.
Sheet 1, COULMN I will give you the answer you are looking for.
Just drag the formula as far as you want to go.
Enter six numbers in columns C thru' H and answer will appear in Col. I.
Hope it helps.

I am sure one of the Gurus on this forum will be bale to shorten the formula or make it more efficient. But this works, so you have soemthing to work with.
modytrane.

[shg]

If you don't mind adding a little helper function,

Please Login or Register  to view this content.

Then you can use the formula

=Cat(FREQUENCY(A1:F, {10,20,30,40,50}))

[Rolland Arriza]

It took me 3 hours to figure out how sort numbers from a set of numbers.
To start, let us look first our sample image below (data from our field work):
test1.jpg

You see from the first table (Post 1900s Male) that the ages were sorted from lowest to highest. You can clearly see that there were no person in the "age class: 0-0.99".

Now, to count how many times the number or set of number occur in a range: We will use the "COUNTIFS" function. its COUNTIFS.
Take a look at the image below:
test1.jpg

function: =COUNTIFS(range, the number you want to be counted)
Example, if you want to count how many times the numbers: 11,12,13,14,15
the formula would be: =COUNTIFS(range, ">=11",range,"<=15")

The ">=11" means the numbers equal or greater than 11. So number 11 is included. If you put ">11", then all numbers greater than 11...so number 11 is excluded. Next, the "<=15" means the numbers equal or greater than 15. Do I need to explain again?

Here's the last image. this was the example above.
test1.jpg