marco to format top 80%
 

I need to create a macro that chooses the top 80% of the cells in a column,
0, and formats them with colour. I really just need the formula, I can do
the macro.

marco to format top 80%
 

I had 50 numbers in A1:A50 and attached this conditional format
(Format|Conditional Format)
=(A1=LARGE($A$1:$A$100,20) ) then red background
to pick out the top 80% of the numbers.
Any help?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"NN" wrote in message
...
I need to create a macro that chooses the top 80% of the cells in a column,
0, and formats them with colour. I really just need the formula, I can do
the macro.

marco to format top 80%
 

Assuming by "top 80%" you mean the largest 80% of numeric values, do you
need to use a macro?

One could use conditional formatting:

CF1: Formula is =A1=PERCENTILE(A:A,0.2)
Format1: <patterns/<desired color

OTOH, if you *have* to use a macro, the Application.Percentile method
should be helpful.

In article ,
"NN" wrote:

I need to create a macro that chooses the top 80% of the cells in a column,
0, and formats them with colour. I really just need the formula, I can do
the macro.

marco to format top 80%
 

80% of value or 80% of number. For instance if the top value is 100 and
there are 50 items, do you want anything over 20 in value, or the top 40
items.

If the latter, use conditional formatting with a formula of
=A1=MAX(A:A)*20%

--

HTH

RP

"NN" wrote in message
...
I need to create a macro that chooses the top 80% of the cells in a
column,
0, and formats them with colour. I really just need the formula, I can
do
the macro.