Conditional Format four top four cells

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Using this for conditional formating, however if I have a blank cell it still
changes the cell format according to the rule.
=A1>=LARGE($A$1:$A$5,4)

Data
2
3

5
7
8
Would like the 3,5,7,8 to change cell color based on formula in coditional
Format
Thanks Rob
 
Rob,

Your 'blank' cell is not blank - it has a space or a null string ("") in it.

Try

=AND(A1<>"",A1<>" ",A1>=LARGE($A$1:$A$5,4))

HTH,
Bernie
MS Excel MVP
 
Bernie
Works Great
Thanks
Rob

Bernie Deitrick said:
Rob,

Your 'blank' cell is not blank - it has a space or a null string ("") in it.

Try

=AND(A1<>"",A1<>" ",A1>=LARGE($A$1:$A$5,4))

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Still have one problem it also includes ties.
=AND(A1<>"",A1<>" ",A1>=LARGE($A$1:$A$5,4))
Any thoughts
Rob
 
Rob,

You need a criteria for breaking ties - other columns, first value, last
value, dates, etc....???

For example, you could enter a new column of formulas, with this in cell B1
for example

=AND(A1<>"",A1<>"
",(A1+ROW(A1)/10000)>=LARGE($A$1:$A$5+ROW($A$1:$A$5)/10000,4))

Entered using Ctrl-Shift Enter, then copy down to B2:B5.

Then use

=B1

as the CF formula.

HTH,
Bernie
MS Excel MVP
 
Using this for conditional formating, however if I have a blank cell it still
changes the cell format according to the rule.
=A1>=LARGE($A$1:$A$5,4)

Data
2
3

5
7
8
Would like the 3,5,7,8 to change cell color based on formula in coditional
Format
Click to expand...

Assuming you really meant to look for the 4th largest value from the
array that comprises the first SIX elements of column A, try this:

=ISNUMBER(A1)*(A1>=LARGE($A$1:$A$6,4))
 
Hello dranon

I am trying to have the four largest values format to a yellow cell. I am
using
{=SUM(LARGE(C5:C28,ROW(1:4)))} to add up the four largest values in the
column. I just want the four values it uses to hightlight yellow.

I tried yours seems to still list multi ties.

Any Thoughts
Thanks in advance
Rob
 
What version of Excel are you using?

Excel 2007 has an option to conditionally format a top/bottom n list.

The problem with top/bottom n lists is that there can be more than n values
that meet the criteria when you consider ties. Consider this example:

10
10
10
9
9
9
8
7
6

How many numbers make up the top 4? The generally accepted rule for top n
lists says there are 6 values that make up the top 4. However, some folks
might say there are 8 numbers in that list that make up the top 4.
 
Hello T. Valko

I am using Excel 2003 sp3.
In your list of data below my goal is to have the following result:
10
10
10
9
These four would meet the conditional format and make cell yellow.

Thanks Rob
 
Bernie

Not sure I follow,

Thanks Rob


Bernie Deitrick said:
Rob,

You need a criteria for breaking ties - other columns, first value, last
value, dates, etc....???

For example, you could enter a new column of formulas, with this in cell B1
for example

=AND(A1<>"",A1<>"
",(A1+ROW(A1)/10000)>=LARGE($A$1:$A$5+ROW($A$1:$A$5)/10000,4))

Entered using Ctrl-Shift Enter, then copy down to B2:B5.

Then use

=B1

as the CF formula.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
To rank the values without repeats requires a formula that is too complicated for the CF engine: it
needs to be array entered. So you enter the formulas in a "helper" column, and use the results to
set your CF.

Array enter (enter using Ctrl-Shift-Enter) this formula in B1

=AND(A1<>"",A1<>" ",(A1+ROW(A1)/10000)>=LARGE($A$1:$A$5+ROW($A$1:$A$5)/10000,4))

It adds a very small amount to each value in a repeatable way, so that the ties are broken in a
systematic, predictable way. It returns a TRUE or FALSE that can be used in the CF, based on the CF
formula of

=B1

Then apply CF the way that you normally do.

HTH,
Bernie
MS Excel MVP
 
Ok, so you're not interested in the ties. You just want the *first 4* values
that meet the criteria to be formatted. With this data A1:A4 will formatted.

A1 = 10
A2 = 10
A3 = 10
A4 = 10
A5 = 10

Select the range of cells A1:A5
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=RANK(A1,A$1:A$5)+COUNTIF(A$1:A1,A1)-1<=4
Click the Format button
Select the desired style(s)
OK out
 
T.Valko

Thank you
Perfection
Thanks Rob


T. Valko said:
Ok, so you're not interested in the ties. You just want the *first 4* values
that meet the criteria to be formatted. With this data A1:A4 will formatted.

A1 = 10
A2 = 10
A3 = 10
A4 = 10
A5 = 10

Select the range of cells A1:A5
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=RANK(A1,A$1:A$5)+COUNTIF(A$1:A1,A1)-1<=4
Click the Format button
Select the desired style(s)
OK out
Click to expand...
 
Hello dranon

I am trying to have the four largest values format to a yellow cell. I am
using
{=SUM(LARGE(C5:C28,ROW(1:4)))} to add up the four largest values in the
column. I just want the four values it uses to hightlight yellow.

I tried yours seems to still list multi ties.
Click to expand...

Rob, you aren't listening to what you are being told. Excel can't
determine how to break ties. Only you can determine how to break
ties. Unless you are willing (and able) to give Excel the means to
break a tie, Excel will fail you.

So, go back and read what Bernie wrote. You might even TRY it. And
then ask a specific question, rather than just proclaim your inability
to understand what somebody has spent their time crafting FOR YOU.
 
Hello
dranon

Please note I am trying Bernie's way. Believe me I appreciate Bernie and
yourself allot!!! I am a good amateur at Excel and trying to get better. This
format is a great thing.

Thanks Rob
 
Hello
Bernie
Works Great!!! You help and time are greatly appreciated…..

Thank you , Thank You
Rob
 
Back
Top