Conditional Formatting as a new list

[sandrax78]

Is there any way that I can use conditional formatting and instead of highlighting top 10% to have them displayed as a new list.
If that's not possible how can I have highlighted conditional formatting answers pulled to a new list for example: Top 10% list.

I welcome any other ideas beside conditional formatting, pivot tables maybe?

Please see attached

Thanks

[teylyn]

Hi sandra,


I don't see any conditional formatting to highlight anything in your file. What conditional formatting formula did you have in mind?

Try this to list the top ten sales:

in D2 put this formula and copy down to D11

=LARGE($B$2:$B$206,ROW(A1))
This will give you the 10 largest sales figures.

In E2 put this formula and copy down to E11

=INDEX($A$2:$A$206,MATCH(D2,$B$2:$B$206,0))

This will give you the store numbers for each sales figure in column D

hth

[sandrax78]

Teylyn,

Thanks for help but I'm looking for top 10% rather then top 10 largest sales.
I attached a new sheet with my conditional formatting (top 10% highlighted green, bottom 10% highlighted in yellow).

If you look to the right you will see the lists that I would like to populate.

I hope my explanation is clear enough.

Thanks

[teylyn]

Taking your formula used in the CF as a base, in F4 and copy down until you only see blanks

=IF(LARGE($B$2:$B$206,ROW(A1))>LARGE($B$2:$B$206,ROWS(B$1:B$206)/10),LARGE($B$2:$B$206,ROW(A1)),"")

in E4 and copy down as required

=INDEX($A$2:$A$206,MATCH(F4,$B$2:$B$206,0))

Likewise in N4 and copied down

=IF(SMALL($B$2:$B$206,ROW(A1))<SMALL($B$2:$B$206,ROWS(B$1:B$206)/10),SMALL($B$2:$B$206,ROW(A1)),"")

and in M4 copied down as required

=INDEX($A$2:$A$206,MATCH(N4,$B$2:$B$206,0))

[sandrax78]

Teylyn, Thanks a lot for your help but I think there is a small problem.

1. Top 10%-Solved
2.Bottom 10%-Solved
3. Everything between Top 10% and Bottom 10% -unfortunately not solved

Is there an easy solution to this issue?

[teylyn]

I just realised I put the small 10% in the wrong column, but I see you sorted it. Completely overlooked the middle bit. Here is what you can use in column N

=IF(LARGE(B:B,MATCH(MIN(F:F),B:B,0)+ROW(A1)-1)<=MAX(J:J),"",LARGE(B:B,MATCH(MIN(F:F),B:B,0)+ROW(A1)-1))

Copy E or I to M for the Location lookup.

hth

[sandrax78]

Just a last quick question teylyn

Amount in B22 is highlighted but it doesn't show in the top 10% list,
Amount in B187 is highlighted but it doesn't show in the bottom 10% list.

Both of this amounts are showing as Between Top 10% and Bottom 10% List.

I just don't understand why they would be highlighted as top 10% and bottom 10% and they wouldn't show up on the correct lists.

Please see attached spreadsheet to see what exactly I'm talking about

I really appreciate all your help with this question

[teylyn]

Ah,.... a small glitch in trying to reproduce the CF formula in the cell formula. The CF has "greater than or equal to", whereas the cell formula only has the > sign.

Change

F4 =IF(LARGE($B$2:$B$206,ROW(A1))>=LARGE($B$2:$B$206,ROWS(B$1:B$206)/10),LARGE($B$2:$B$206,ROW(A1)),"")

J4 =IF(SMALL($B$2:$B$206,ROW(A1))<=SMALL($B$2:$B$206,ROWS(B$1:B$206)/10),SMALL($B$2:$B$206,ROW(A1)),"")

and copy both formulae down. The formula in column N does not require any correction.

hth