Question about sorting

Aielrond

New Member
Joined
Aug 29, 2005
Messages
9
GUys,

You have helped me immensely in the past since I am no excel expert, I just have to use it once in awhile in my job.

Situation: I have a set of Data that is scores of an audit.

Format: Data is straight forward 92.33 type stuff

Task: Enter the data in excel and then sort it against certain criteria. Specifically if it falls above 95 it goes here, 90-94 it goes here, 85-89 it goes here, etc. This data needs to fall into a bar chart that shows the number of data points in each bar and the average score of that bar. Based on the sorting above.

To you guys this may sound and be easy! I am a pretty simple guy when it comes to excel and I dont really know where to begin. Can you be of help? I would pay someone to do it, but the data is VERY proprietary and I cant give it to anyone. I will only have to do this once a year and I can save the spreadsheet and just plug in new info next year. :)

ANy ideas?

Thanks in advance!

Allen
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Allen

If you are looking for counts of numbers that fall into ranges, then have you looked at the FREQUENCY function?

Say your data is in the range A1:A50 then in the range B1:B5 enter 0,20,40,60,80,100. Highlight the range C1:C5 then enter the formula

=frequency(a1:a50,b1:b5) then holding down the ctrl/shift keys, press enter. This will give you the count of numbers in the ranges.

HTH

Tony
 

Aielrond

New Member
Joined
Aug 29, 2005
Messages
9
I dont think I explained it to well last night, so my apologies to everyone.

I have two sets of numbers in two columns.

84.85 1.73
85.67 1.37
92.1 1.09

Now, what I need to do is compare the numbers and then create a bar chart out of the results. So if your number is 85-89 in column one I then need it to plug in the number average of all the numbers that fall within that stated range based on the number in column two. I guess I am saying if I have 300 scores in column 1 that are 90-94 then the average of the number following it in column two is (blank) and create a bar chart out of that. I have about 500 data points. I do have a scrrenshot of the bar chart that I can provide, even if I cant provide the data points. It at least tells you how the end result looks and that might give you what you need to get me rolling on the excel part.

Does that help?

Allen
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Allen

How about using a combination of SUMPRODUCT and COUNTIF to work out the averages??? You will have to nominate the upper and lower bounds that you want to use, but this would be one way to do it.


Tony
 

Aielrond

New Member
Joined
Aug 29, 2005
Messages
9
Tony,

Is that the easist and most effective way to do it? Is it something a novice like myself can accomplish fairly easy? If so, where is the best place to start to learn those two functions?

Allen
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Allen

PM me with an email address and I'll send you a sample file.


Tony
 

Forum statistics

Threads
1,078,373
Messages
5,339,816
Members
399,334
Latest member
Grace Ford

Some videos you may like

This Week's Hot Topics

Top