Conditional Formatting based on top values

[Cecilia]

Hello--

I have an array of values (below). I'd like to do a conditional format to
highlight those values that comprise the top 30% of the sum.

I know I'd probably use a formula in the conditional formatting but not sure
where to go from there. I'm using Excel 2003.

73 3 4 4 47
16 8 1
27 46 7
172 210 21 44

 

[David Biddulph]

In CF, use Formula Is
=PERCENTRANK($A$2:$A$11,A2)>=70%
if A2:A11 is your array.

 

[ShaneDevenshire]

Hi,

I just want to make sure I'm on the same page as you:

For the numbers you gave us the sum is 683, the 30% of this value is 204.9.
So the value 210 is the only value that should be highlighted.

Is this what you want?

 

[Cecilia]

That is correct.

I tried the percentrank formula and it didn't work.

 

[ShaneDevenshire]

Hi Cecilla,

Here is a solutions that's pretty good.

1. Suppose you name the range containing the data Data.
2. In an empty cell enter the formula =SUMPRODUCT(LARGE(Data,ROW(A$1:A1)))
3. Copy this formula far enough so that there is at least enough formulas to
equal the number of entries in the Data range. You can always have more
formulas, in which case all the extra ones will return #NUM errors but that
doesn't cause a problem.
4. Name this range RT (for running totals)
5. Highlight your Data range and choose Format, Conditional Formatting, pick
Formula is from the first drop down and enter the following formula:
=A1>=LARGE(Data,MATCH(SUM(Data)*0.3,RT,1)+1)

In this case A1 is the top left corner of the range Data.

Unfortunately, this approach requires a separate running totals range for
every data range.

You could also solve your problem with VBA.

Cheers,
Shane Devenshire