#### Aielrond

##### New Member
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?

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

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

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

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

Allen

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

Tony

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

Replies
9
Views
366
Replies
3
Views
311
Replies
8
Views
471
Replies
5
Views
192
Replies
4
Views
852

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.

### Which adblocker are you using?

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

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