Highlighting Min Median Max and Percentile90

Printable View

10-29-2012, 11:54 PM
PSL

1 Attachment(s)

Highlighting Min Median Max and Percentile90

Hi,

I've been trying to figure this out for a while now. Few hours to be exact. I have a list of codes and their respective values. For each code I need to pick out the Min, Median, Max and Percentile 90 value.

So was trying to write a code to highlight the positions for each code.

Now, Percentile 90 (and even median in case of even number of values) might not be a value from the list given. In such cases I would need to highlight the value closest to the actual percentile.

Eg. if values are 100, 120, 135, 160, 195, 210, 215 - Percentile 90 would mathematically be 212. Here I would select the closest value ie. 210.

I have attached a text file.

Here there are 2 columns. One with Codes (A, B, C) and the other with values. In the actual scenario I might have multiple columns/Conditions.

Would be great if someone could help me out! :-)

Attachment 9011
10-30-2012, 01:16 AM
Bob Phillips

MIN, MAX and MEDIAN are just straight-forward formulae, for PERCENTILE90 you can use this array formula

=MAX(IF(C3:C64>PERCENTILE(C3:C64,0.9),C3:C64))
10-30-2012, 01:28 AM
PSL

Quote:

Originally Posted by xld

MIN, MAX and MEDIAN are just straight-forward formulae, for PERCENTILE90 you can use this array formula

=MAX(IF(C3:C64>PERCENTILE(C3:C64,0.9),C3:C64))

Hi,

I know the formula's! :-)

However, with a long list (going into 100 different codes and 100000+ values) I needed a code to highlight the required values (say, in yellow) so I can delete all the other values.

The formula would give me the values which I would have the look up individually and delete the rest.

Therefore the VBA

Cheers,
10-30-2012, 01:46 AM
Bob Phillips

So if you know it, use that formula with conditional formatting.
10-30-2012, 02:01 AM
PSL

Quote:

Originally Posted by xld

So if you know it, use that formula with conditional formatting.

Well, tried that. But couldn't really figure out how conditional formatting would be used in this case. Especially with multiple conditions in the real case. Say Code 'A'and Sub-code 'X'.

Using an array to find the values and using 'Match' to find the position in the list. And highlighting them individually. Was wondering if this could be done faster since I'm generally looking at a large number of rows of data.

Cheers,
10-30-2012, 06:42 AM
p45cal

For Max and Min it's straightforward. For Median and percentile it's a little more complicated, for sub-codes it will be more difficult still. This on the file you attached:
Conditional formatting
Cell Nr.: / Condition
C3 1. / Formula is =$C3=MAX(IF($B$3:$B$64=$B3,$C$3:$C$64))
C3 2. / Formula is =$C3=MEDIAN(IF($B$3:$B$64=$B3,$C$3:$C$64))
C3 3. / Formula is =$C3=MIN(IF($B$3:$B$64=$B3,$C$3:$C$64))

You can apply the above conditional format formulae while the whole range is selected (as long as C3 is the active cell). It will only highlight some medians, if they happen to coincide with an actual value.

I have no time to explore this right now, but if I get time I'll come back.
10-30-2012, 07:03 AM
Bob Phillips

This will work for PERCENTILE90 for your dataset

=$D3=MAX(IF(IF(
($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64)
<PERCENTILE(IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64),0.9),
IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64)))

expanding to thousands of rows might be slow.
10-30-2012, 07:17 AM
p45cal

Quote:

Originally Posted by xld

This will work for PERCENTILE90 for your dataset

=$D3=MAX(IF(IF(
($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64)
<PERCENTILE(IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64),0.9),
IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64)))

xld, that formula refers to column D - what's in it?
10-30-2012, 07:54 AM
Bob Phillips

Oh sorry, should have explained. I inserted a new column C with a sub-code, so the previous C moves to D. The OP will need to adapt the the real situation which was not shown to us.
10-31-2012, 01:58 AM
PSL

Quote:

Originally Posted by xld

This will work for PERCENTILE90 for your dataset

=$D3=MAX(IF(IF(
($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64)
<PERCENTILE(IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64),0.9),
IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64)))

expanding to thousands of rows might be slow.

Hi,

Thanks for this. I didn't imagine this approach was feasible! Testing it out on a few actual data sheets. On first look it seems to be picking the lower data point rather than the closest one. Eg. If the Percentile 90 is mathematically 166 the formula is highlighting the next lower value ie 134. Whereas 185 is mathematically closer.

Will check across a larger data set!

Thanks :-)
10-31-2012, 04:32 AM
Bob Phillips

Quote:

Originally Posted by PSL

On first look it seems to be picking the lower data point rather than the closest one. Eg. If the Percentile 90 is mathematically 166 the formula is highlighting the next lower value ie 134.

You are correct, that is exactly what t is doing.

For the closest, try

Code:

=ABS(D3 -PERCENTILE(IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64),0.9)) =MIN(IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),ABS($D$3:$D$64-PERCENTILE(IF(($B$3:$B$64=$B3)*($C$3:$C$64=$C3),$D$3:$D$64),0.9))))