# Categorize values in a range according to sum of values and percentages

#### ezertuchev

##### New Member
Hi, I'm new to this forum, and was wondering if you could help me.

I have a column of scores assigned to different areas in my company, and I have to categorize them according to the following criteria:
• Top value (or values if tied) = "M"
• Top 50% = "AA"
• Top 70% = "A"
• Top 90% = "B"
• Remainder = "C"

The sum of all values is 250, so I've worked out the following:
• Top 50% = 125
• Top 70% = 50 (the next 20% of values)
• Top 90% = 50 (the next 20% of values)

So if my top values are 2 areas with 35 points each (70), the "AA" Areas would sum 125 points combined, the "A" Areas would sum the next 50 points combined and so on.

The problem is that I cannot work out a formula that helps me do this automatically (I have to sort the values and manually and select them to see how far down the list do I have to add up to get to the desired value, and so on.

I have to do this process for a whole lot of columns of data, so doing it manually would be extremely time-consuming.

I'm attaching a screen shot of some sample data that is representative of the data I have in my spreadsheet.

Eduardo Zertuche

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome to the board

I'm a little confused by this one but I'd be playing around with RANK for a start as this will tell you where in the scale any individual value is, and the SUMIF will tell you the running totals for each rank

So, as an example, in cell E2 I've got "=RANK.EQ(C2,\$C\$2:\$C\$27)" and in F2 I've got "=SUMIF(\$E\$2:\$E\$27,"<="&A2,\$C\$2:\$C\$27)", both dragged down the list. Note that the formula in F2 uses the values in A for convenience only, these are not actually linked to the order of the Areas (let me know if that doesn't make sense)

This tells me the largest numbers in the list are Areas 22 and 23, and that they sum to 70. Rank 3 and lower sums to 120, rank 5 and lower sums to 180 - so my next question is, do rank 5 items get included within your 125 category or not?

Ideally I would see this in my chart:

As you said, the first two numbers that add up to 70, plus the next two numbers that add up to 120, PLUS the next one that covers the 125, would all exceed 125 to the next nearest number in the sum.

The same with the next category, it would start at 140 and end at 180, the next nearest to 175, and so on.

I hope I'm making any sense. I know it's a tricky one and I've looked this up but can't find an answer related to this.

Eduardo Z.

I forgot to mention, the purple bars are AA, the yellow A, the green B and the blue C.

Also, as you can see, there are values of 20 both on AA and on A, they get sorted alphabetically, so some values that are equal make it to the AA category while others make it to the A category.

Hope this helps.

Eduardo Z.

Replies
8
Views
306
Replies
8
Views
967
Replies
2
Views
2K
Replies
5
Views
584
Replies
4
Views
621

1,196,042
Messages
6,013,046
Members
441,746
Latest member
ArtemisAlex

### 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