Highlighting Min Median Max and Percentile90

[PSL]

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! :-)

Test.xlsx

[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))

[PSL]

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,

[Bob Phillips]

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

[PSL]

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,

[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.

[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.

[p45cal]

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?

[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.

[PSL]

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 :-)

[Bob Phillips]

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

=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))))