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

[ezertuchev]

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.

I'll appreaciate any help you can provide.

Eduardo Zertuche

 

[baitmaster]

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?

 

[ezertuchev]

Thanks for your answer baitmaster.

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.

Thanks again for your help.

Eduardo Z.

 

[ezertuchev]

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.

 

[baitmaster]

https://www.dropbox.com/s/bcomdez2g9o9qrp/ranked values.xlsx?dl=0