color coding largest numbers in a list

[Robert L. Salisbury]

I have a list of 60 items that change slightly on a weekly basis. Currently
I highlight(color code) the five with the largest weekly increases. I do
this with the following conditional format formula.

=AND(ISNUMBER($P7),$P7>=LARGE($P$3:$P$60,5))

I would like to highlight(color code) another group of five with the next
largest weekly increases but have been unable to determine the appropriate
formula.

Can anyone help?

 

[Paul]

I have a list of 60 items that change slightly on a weekly basis. Currently
I highlight(color code) the five with the largest weekly increases. I do
this with the following conditional format formula.

=AND(ISNUMBER($P7),$P7>=LARGE($P$3:$P$60,5))

I would like to highlight(color code) another group of five with the next
largest weekly increases but have been unable to determine the appropriate
formula.

Can anyone help?

For the second condition in Conditional Formatting to highlight the next 5,
you can simply use the formula above with 5 replaced by 10:
=AND(ISNUMBER($P7),$P7>=LARGE($P$3:$P$60,10))
The point is that the first condition will be applied to the 5 largest. This
second condition will only apply if the first condition is NOT met, so will
apply to the next 5 rather than all 10.

 

[Bob Phillips]

See my response in .misc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sorry I meant in .programming.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[David McRitchie]

Tom's answer in the programming thread
http://groups.google.com/[email protected]
and Paul's answer in this thread point out that as the second condition
you don't need to weed out the first condition, because if the first
condition is met you do not get to test the second condition.

Which just emphasizes the fact that the poster should only post in
one Excel newsgroup rather than wasting other people's time who
may be trying to answer something already answered better in the
other thread/newsgroup. Conditional Formatting is not a
programming question, questions should be posted the group
that best fits the subject -- it usually doesn't really matter if it is in the
wrong newsgroup, someone will answer it. Especially since
most people do look at questions in excel.misc, excel.programming,
and excel.worksheet.functions beyond those the newsgroups are
more specialized. .

Hints for New Posters:
http://www.cpearson.com/excel/newposte.htm
http://www.cpearson.com/excel/topics.htm -- index to site

More detail on some of the items and references to other netiquette things
http://www.mvps.org/dmcritchie/excel/posting.htm
http://www.mvps.org/dmcritchie/excel/xlnews.htm -- searching newsgroups
http://www.mvsp.org/dmcritchie/excel/xlindex.htm -- index to site

There is no official frequently asked questions (FAQ) but the best
one currently is on Debra Dalgleish's site (contextures.com). There have been many other
attempts in this area. Basically I consider my entire site to be composed
of FAQ or things that I think should have been in Excel.
http://www.contextures.com/tiptech.html -- index for contextures.com
http://www.contextures.com/xlfaqApp.html FAQ Excel applications
http://www.contextures.com/xlfaqMac.html (for Mac)