Conditional Formatting based on top values
 

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

Conditional Formatting based on top values
 

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

"Cecilia" wrote in message
...
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

Conditional Formatting based on top values
 

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?
--
Thanks,
Shane Devenshire

--
Thanks,
Shane Devenshire


"Cecilia" wrote:

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

Conditional Formatting based on top values
 

That is correct.

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

"ShaneDevenshire" wrote:

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?
--
Thanks,
Shane Devenshire

--
Thanks,
Shane Devenshire


"Cecilia" wrote:

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

Conditional Formatting based on top values
 

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


"Cecilia" wrote:

That is correct.

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

"ShaneDevenshire" wrote:

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?
--
Thanks,
Shane Devenshire

--
Thanks,
Shane Devenshire


"Cecilia" wrote:

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