Highest 5 numbers in a range turn red

  • Thread starter Thread starter mjones
  • Start date Start date
M

mjones

Hi All,

70 people are voting on 15 products tomorrow night in a serious of
rounds.

Each time I'll end up with 15 numbers in a range. Since it will be on
a presentation, it will be nice if the top ones turn red or highlight
somehow.

Top number will change each time, ie. 10 picked the first round, 5 the
next, etc

I think this is a conditional format, but I have no idea how to do
this.

Any help would be appreciated.

Thanks,

Michele
 
You are correct on the conditional formatting. In general you want to know if
the number in the cell is greater than or equal to the 5th largest number.
Give this a try..

Place your cursor in Cell A1 and then select the entire column. (all of
Column a is highlighted with cell A1 as the active cell). Now select
Format|Conditonal Format -> Formula is
=A1>=LARGE($A:$A,5)
And change the format to red...

Note that you could end up with more than 5 cells highlighted if there is a
tie in fifth spot...
 
Nel post:[email protected],
mjones said:
Hi All,

70 people are voting on 15 products tomorrow night in a serious of
rounds.

Each time I'll end up with 15 numbers in a range. Since it will be on
a presentation, it will be nice if the top ones turn red or highlight
somehow.

Top number will change each time, ie. 10 picked the first round, 5 the
next, etc

I think this is a conditional format, but I have no idea how to do
this.

Any help would be appreciated.

Thanks,

Michele
Click to expand...

Hi Michele,

If your numbers are in the range A1:A15, selece the range (A1 should be the
active cell) and try to use this formula in the conditional formatting (use
the option "Formula is"):

=A1>=LARGE($A$1:$A$15,5)


--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.


Thanks in advance for your feedback.


Ciao


Franz Verga from Italy
 
Nel post:[email protected],
mjones <[email protected]> ha scritto:











Hi Michele,

If your numbers are in the range A1:A15, selece the range (A1 should be the
active cell) and try to use this formula in the conditional formatting (use
the option "Formula is"):

=A1>=LARGE($A$1:$A$15,5)

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy- Hide quoted text -

- Show quoted text -
Click to expand...

Hey, that works great. Thanks.

Since I have to change the number of winners each round, can I put the
# in a cell (A4) and change the last 5 to whatever is in the cell?

But then I actually ended up with 7 red values. That's because there
were two ties. Can I total the number of winners (red values) in the
range in C34? The range is C5 to C32.
 
mjones said:
Hey, that works great. Thanks.

Since I have to change the number of winners each round, can I put the
# in a cell (A4) and change the last 5 to whatever is in the cell?
Click to expand...


Yes. Try it like this:

=A1>=LARGE($A$1:$A$15,$A$4)


But then I actually ended up with 7 red values. That's because there
were two ties. Can I total the number of winners (red values) in the
range in C34? The range is C5 to C32.
Click to expand...

Try this:

=COUNTIF(C5:C32,">="&LARGE(C5:C32,5))

Or:

=COUNTIF(C5:C32,">="&LARGE(C5:C32,A4))
 
Back
Top