Question about sorting

[Aielrond]

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]

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]

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]

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]

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]

Allen

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


Tony

 

[Aielrond]

Sent you a PM with my email. Thanks for the help.