Conditional Formating =LARGE()

[Ian Mac]

All,

I have a table of numbers D48:N60 with the following data:


part1

{#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,0.153846153846154,0.394366197183099,#N/A,#N/A,#N/A;#N/A,#N/A,0.324022346368715,0.337142857142857,0.35408560311284,0.306603773584906,0.358024691358025,0.353191489361702,#N/A,#N/A,#N/A;#N/A,#N/A,0.319672131147541,0.302083333333333,0.405405405405405,0.318181818181818,#N/A,0.333333333333333,0.356164383561644,0.366336633663366,#N/A;0.400990099009901,0.333333333333333,#N/A,#N/A,#N/A,0.364864864864865,0.300813008130081,0.419811320754717,0.370044052863436,0.3963133640553,0.5;#N/A,0.341269841269841,0.423076923076923,0.398876404494382,0.437125748502994,0.414634146341463,0.488888888888889,#N/A,#N/A,#N/A,#N/A;#N/A,#N/A,0.413793103448276,0.409356725146199,0.492537313432836,0.459770114942529,0.417508417508417,0.407272727272727,0.25,#N/A,#N/A;#N/A,0.537313432835821,0.446540880503145,0.527363184079602,0.493670886075949,#N/A,#N/A,0.416149068322981,0.377777777777778,0.490740740740741,#N/A}

part2

{#N/A,#N/A,#N/A,#N/A,0.432432432432432,0.471698113207547,0.459349593495935,0.51063829787234,0.534351145038168,0.420212765957447,#N/A;#N/A,0.456043956043956,0.483870967741935,0.47906976744186,0.492890995260663,0.376811594202899,0.666666666666667,#N/A,#N/A,#N/A,0.333333333333333;#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,0.458128078817734,0.465753424657534,0.372549019607843,#N/A,#N/A;#N/A,#N/A,0.460992907801418,0.329608938547486,0.392344497607655,0.431818181818182,#N/A,#N/A,0.416666666666667,0.4,#N/A;#N/A,#N/A,#N/A,#N/A,#N/A,0.370967741935484,0.415584415584416,0.383647798742138,0.314606741573034,0.398601398601399,#N/A;#N/A,0.447368421052632,0.27536231884058,0.325,0.433526011560694,0.489795918367347,0.44,#N/A,#N/A,0.514285714285714,#N/A}

what I need to do is highlight the top 10 percentages, I'm running into problems due to the #N/A's (I think). is there a way round this.

by the way I need the #N/A's because they're used in chart information.

Thanks

[Tim Francis-Wright]

On 2002-05-15 07:47, Ian Mac wrote:
All,

I have a table of numbers D48:N60 with the following data:


part1

{#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,0.153846153846154,0.394366197183099,#N/A,#N/A,#N/A;#N/A,#N/A,0.324022346368715,0.337142857142857,0.35408560311284,0.306603773584906,0.358024691358025,0.353191489361702,#N/A,#N/A,#N/A;#N/A,#N/A,0.319672131147541,0.302083333333333,0.405405405405405,0.318181818181818,#N/A,0.333333333333333,0.356164383561644,0.366336633663366,#N/A;0.400990099009901,0.333333333333333,#N/A,#N/A,#N/A,0.364864864864865,0.300813008130081,0.419811320754717,0.370044052863436,0.3963133640553,0.5;#N/A,0.341269841269841,0.423076923076923,0.398876404494382,0.437125748502994,0.414634146341463,0.488888888888889,#N/A,#N/A,#N/A,#N/A;#N/A,#N/A,0.413793103448276,0.409356725146199,0.492537313432836,0.459770114942529,0.417508417508417,0.407272727272727,0.25,#N/A,#N/A;#N/A,0.537313432835821,0.446540880503145,0.527363184079602,0.493670886075949,#N/A,#N/A,0.416149068322981,0.377777777777778,0.490740740740741,#N/A}

part2

{#N/A,#N/A,#N/A,#N/A,0.432432432432432,0.471698113207547,0.459349593495935,0.51063829787234,0.534351145038168,0.420212765957447,#N/A;#N/A,0.456043956043956,0.483870967741935,0.47906976744186,0.492890995260663,0.376811594202899,0.666666666666667,#N/A,#N/A,#N/A,0.333333333333333;#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,0.458128078817734,0.465753424657534,0.372549019607843,#N/A,#N/A;#N/A,#N/A,0.460992907801418,0.329608938547486,0.392344497607655,0.431818181818182,#N/A,#N/A,0.416666666666667,0.4,#N/A;#N/A,#N/A,#N/A,#N/A,#N/A,0.370967741935484,0.415584415584416,0.383647798742138,0.314606741573034,0.398601398601399,#N/A;#N/A,0.447368421052632,0.27536231884058,0.325,0.433526011560694,0.489795918367347,0.44,#N/A,#N/A,0.514285714285714,#N/A}

what I need to do is highlight the top 10 percentages, I'm running into problems due to the #N/A's (I think). is there a way round this.

by the way I need the #N/A's because they're used in chart information.

Thanks

Yes, the #N/A's are a problem.

I thought that you could use RANK like this in an array formula:
=RANK(IF(NOT(ISNA($D$48:$N$60)),$D$48:$N$60))<=10,
but RANK doesn;t seem to like that sort of dynanism.

So, one way around this is to set up a parallel array in P48:AB60, with P48 being
=IF(ISNA(D48),-99999,D48)
and so on.

Now, set up the conditional formatting in D48:N60 to highlight the cell if this is true:
=(RANK(H10,$P$48:$AB$60)<=10)

HTH

_________________
"Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

[ This Message was edited by: Tim Francis-Wright on 2002-05-15 12:17 ]

[Ian Mac]

On 2002-05-15 12:07, Tim Francis-Wright wrote:

Yes, the #N/A's are a problem.

I thought that you could use RANK like this in an array formula:
=RANK(IF(NOT(ISNA($D$48:$N$60)),$D$48:$N$60))<=10,
but RANK doesn;t seem to like that sort of dynanism.

So, one way around this is to set up a parallel array in P48:AB60, with P48 being
=IF(ISNA(D48),-99999,D48)
and so on.

Now, set up the conditional formatting in D48:N60 to highlight the cell if this is true:
=(RANK(H10,$P$48:$AB$60)<=10)

HTH

_________________
"Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

[ This Message was edited by: Tim Francis-Wright on 2002-05-15 12:17 ]

Tim,

I played with the idea of Arrays and also got no joy out of it.
The IF(ISNA()... works and treat, although not to be ungreatful I changed the conditional part to:

=(RANK(D48,$P$48:$AB$60)<=10)

as for the solution, Many thanks,