Conditional Formatting to get top counts in ranges

[Dynamo418]

Hi All,

Relative Excel 2007 novice here. I am trying to set up a spreadsheet which allows people to sort and highlight groups of numbers based on their preferences.

I first set up a macro to sort the data columns into the appropriate format (by % of sales).

I want the user to be able to input 5 numbers (which would be the # size of 5 groups), and excel to highlight and divide one column based on that input. I'm thinking (in pseudo-code) along the lines of:

x1 = 5, x2 = 10. If(Row >/= x1 and </= x2, Highlight Cells Red). And so forth.

Is there a way to do this using conditional formatting, or something? Attached is a basic rubrik that I am working with.

[NBVC]

I am not sure I fully understand...

Which cells would be coloured ultimately if that is the input in X1 and X2?

And if the input was, say, 2 and 7, which cells would be coloured?

And is there only 2 inputs? You first mentioned 5 inputs.. not sure what exactly that means... can you elaborate with examples?

[Dynamo418]

Sorry, let me be more clear.

We need to create a ‘tool’ for others to use which groups a column of numbers based on their (the users) preference. They will input 5 numbers (which will be the size of the 5 groups the column is divided into), and the if statement will highlight each group a different color.

I have tried to mess around with If statements, but do not know the command for something like ‘row number’. In pseudo-code, I want something like this:

USER INPUT: X1 = 5, x2 = 10, x3 = 15, x4 = 20, x5 = 25.

If(RowNumber >/= X1 and </= X2, Highlight the cells in that range in Column B RED). And so on for the other groups.

So if the user enters 5, 10, 15, 20, 25, then the cells in Column B between (and including) row 5 and row 10 will be red. The cells between 10 and 15 will be green. And so on. The numbers they input are the sizes of the groups. So each group in this example would be 5 'locations' (or cells).

In real terms, the list will be a list of location ID numbers, and they will be grouping them based on their needs (for instance: I want the 5 groups to be the Top 5 stores, then the next highest 10 stores, etc.).

In basic terms, it's quite simple: highlight cell groups based on user preference. But I'm not well versed in Excel commands.

Is this more clear? Thanks VERY much for your help!

[NBVC]

Ok,

So assuming you want to highlight column B based on the numbers in column A matching the numbers inputted in X1:X5


then select column B and go to Home|Conditional Formatting, Manage Rules

Then from the top area select Use a formula to determine which cells to format

Enter formula: =AND($A1>=$X$1,$A1<=$X$2)

Click Format and choose from the Fill tab.

Then click Ok.

Click New Rule and repeat as above with formula

=AND($A1>$X$2,$A1<=$X$3)

click Format to choose another column and repeat the steps changing the formula to next set of references in column X.


Note: If you really meant row numbers, and not the values in column A, then change the $A1 references to ROW($A1)

[Dynamo418]

Thanks v. much. That works (I did need to use the row()).

I have one more question, arguably much more difficult.

As you can see in the attachment, I have both location and % of total. Your advice helped me group things by the number of locations selected. That's good.

I also need users to be able to make groupings based on the % of total. They will, for example, select:

x1: 5. x2: 10 x3: 15 x4:20 x5:50 (where the #s represent percentages).

I then want Excel to Conditionally format that % to total column, highlighting the cells which sum to those numbers. So, the sum of the cells up to 5% will be one color, from 5% to 10% will be another color. It would be easy if I just wanted to format based on the cell number, but I want it based on the SUM of the numbers. Pseudo-Code:

And(Sum(Cells in Column C) <= 5%) = RED, Sum(Cells in Column C) >5% and <=10%) = BLUE.

Attached is a worksheet of how it should look, with no code involved (just highlighting).

Any ideas?? Thanks so much!!

[NBVC]

So are the numbers going to be in some ascending order.. or are they random in nature as per your sample?

If they are random, I am not sure how possible this will be...

[Dynamo418]

No, I used random numbers because I am not allowed to use work data, and wanted a quick example. Sorry; they will be numbers that are ranked from high to low. I think I need something in cond. formatting like:

=SUM($E$1:$E$100<=5)

And use that format but more complicated? I'm not sure. Thanks!

[NBVC]

A little confused as to this.... if you enter 5 in X1, do you want to highlight first everything lower than this or do you want to highlight everything between this and the value in X2?

So if you are listing from highest to lowest... then assuming you want everything lower than and equal to X1, try as your first condition:

=SUM(A2:A$25)<=X1

If you want everything between X1 and X2


=AND(SUM(A2:A$25)>X2,SUM(A2:A$25)<=X2)

where A2:A25 is your list

continue for the remaining conditions....

Note that X1 and X2 are to be formatted the same...

[Dynamo418]

Hmm.

I did mean, everything lower. Two problems I see:

1) It highlights from the bottom up for some reason. See attached spreadsheet.

2) It highlights things below the lowest number as well.

The first issue makes no sense to me..

[Dynamo418]

Here is the attachment*

[NBVC]

You have a 3 in your conditional format.. should it be 3% to match your entries?

and none will be highlighted at this point, since your lowest number is 0.3 which is 30%

Maybe, I misunderstood... It would highlight from the bottom up.. it will highlight everything up to what compoundly adds up to less than your input value... so all values that add up starting from bottom A44 upwards to less that 3% or whatever...

[Dynamo418]

That was just a sample, and the entries are numbers, not percents anyway.

In my sample there are numbers highlighted: they add up to 3.46, which is odd, and the highlighting starts below the bottom number, which is also odd (blank cells highlighted).

But what I want is highlighting from the top down. For instance, the first group is 5%: this should highlight the 'top' 5% of sales. Currently it is highlighting the 'bottom' 5% of sales.

[NBVC]

Do you mean then?

=AND(A1<>"",ROW(A1)<=COUNT($A:$A)/5+ROW(A$1)-1)

this will highlight the top 5 % of your total count of entries...

it's add a bit of extra robustness, so that you can insert rows above without affecting the outcome...

[Dynamo418]

No, I don't want 5% of the count, I want the top 5% of the total. It's ranked from highest percentage to smallest percentage in # format, and I need to highlight the user's input. So, the user will say: "I want 5 groups: the top 5%, the next highest 10%, the next highest 25%, the next highest 40%, and the final 20%", and it will highlight those groups as a % of the total. Does that make sense?

So, almost like your instructions (not the most recent, before that) but that was bottom to top, and I need top to bottom.

[NBVC]

I think the terminology is confusing as to what you need. Maybe it's the paint (we are having the offices painted...so maybe getting to my head)...

Please repost the last book with actual inputs entered in X1 and X5 and show which cells should be highlighted based on those inputs... then tell me why specifically.

[Dynamo418]

See attached. The % of total numbers are just numbers, but they represent percents. So the first cell is 1.29%.

I estimated the group sizes, highlighting based on what looked like the right amount. No calculation involved.

Obviously the groups will never be exact, but in my sales column the %s are very small so it will be quite close to the user input.

Why? Well, these sales %s correspond to store locations; they are going to group stores based on their sales, so the top sales stores will be grouped into a 'Top Seller' group, and so forth, for inventory allocation.

Thanks!

[Dynamo418]

File is attached

[NBVC]

You got me on this one, I am afraid... It sounds/looks simple, but I can't figure out your logic... I think it's maybe me.. but I really can't wrap my head around it today....

If enter this formula in adjacent cell: =A3/SUM(A:A) and copy down.. I get % of total for each value....

If I do a pivot table and list as a percentage of total I get the same thing..

Hopefully maybe somebody sees what I fail to.

[Dynamo418]

Should I start a new thread? It seems like we (you) are really close to figuring this out!

I'll try and explain one more time, just in case. I think you are getting held up on whether they are percents or numbers and such, but that's not the issue for me. Let's agree that in my attachment, the numbers are %s. So, the first number (14) is 14% of the total. As you can see, these numbers add up to 100 (100%).

The method you used earlier does group things by the percentage of the total, but it doesn't do it how I would like. I want them grouped from top to bottom. For instance, if someone wants the first group (Group1) to be the top 5% of sales, this will highlight the cells that sum up to no greater than 5. Can you see how this would be useful? The user could see which locations are associated with the top 5% of sales. Then he would input the next number - say 10%. And so on.

I know this would use conditional formatting, I am just struggling with how to do it. Your advice was similar to this (I modified it to fit my spreadsheet):

G4 = 5
G5 = 10

=SUM(E13:$E$1755)<$G$4 (GREEN)
=AND(SUM(E13:E$1755)>$G$4,SUM(E13:E$1755)<($G$5+$G$4)) (YELLOW)

This is 2 of the 5 boxes. It's close to what I want: the cells which sum to less than 5 are green, and the cells that are between 5 and 15% (the 'NEXT 10%') are Yellow. However, it does this backwards.


Does this make sense? I feel like I'm almost there, but something is missing. Thanks!

[NBVC]

Maybe we have a winner????

[Dynamo418]

Yes!!!!

Thank you so much for your help. I'll run and delete the new thread I started. Thanks for hanging in there with me, I know that I'm not great at explaining things sometimes!!

Thanks again! I really appreciate your help.

[NBVC]

Great! Glad it worked out in the end...

I think it was the paint fumes...

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save