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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,217,440
Messages
6,136,635
Members
450,022
Latest member
Joel1122331

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top