Conditional Format four top four cells

[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

 

[Bernie Deitrick]

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

 

[Rob]

Bernie
Works Great
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

 

[Rob]

Still have one problem it also includes ties.
=AND(A1<>"",A1<>" ",A1>=LARGE($A$1:$A$5,4))
Any thoughts
Rob

 

[Bernie Deitrick]

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

 

[dranon]

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

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

 

[Rob]

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

 

[T. Valko]

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.

 

[Rob]

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

 

[Rob]

Bernie

Not sure I follow,

Thanks 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

 

[Bernie Deitrick]

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

 

[T. Valko]

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

 

[Rob]

T.Valko

Thank you
Perfection
Thanks Rob

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

 

[dranon]

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.

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.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Rob]

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

 

[Rob]

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

Thank you , Thank You
Rob