will autopopulate the group depending on the top 3 results

**Elainefish** · 06-18-2013, 04:32 AM

i made a post on this few days back and Fotis1991 helped me with the formula :-)
(old post : http://www.excelforum.com/excel-form...html?p=3273127)

Fotis 1991 suggested this formula:
=INDEX($B$2:$B$12;MATCH(LARGE(INDEX($C$2:$C$12+(ROWS($C$2:$C$12)-ROW($C$2:$C$12))/10^5;0);ROWS($1:1));INDEX($C$2:$C$12+(ROWS($C$2:$C$12)-ROW($C$2:$C$12))/10^5;0);0))

this is exactly what I needed
but the problem is if the ranges are all zero, it is showing the the first top 3 texts (please see F13:F15)
how can i make the formula not showing anything?

question_top3_will not show if ranges are zero.xlsx

**benishiryo** · 06-18-2013, 04:44 AM

hi there again, try:

Formula:

Please Login or Register  to view this content.

**Fotis1991** · 06-18-2013, 04:44 AM

A simple way could be this..

=IF(G13=0;"";INDEX($F$2:$F$12;MATCH(LARGE(INDEX($G$2:$G$12+(ROWS($G$2:$G$12)-ROW($G$2:$G$12))/10^5;0);ROWS($1:1));INDEX($G$2:$G$12+(ROWS($G$2:$G$12)-ROW($G$2:$G$12))/10^5;0);0)))

**Pete_UK** · 06-18-2013, 04:49 AM

You could change the formula in F13 to this:

=IF(COUNTIF(G$2:G$12,"<>0")=0,"",INDEX($F$2:$F$12,MATCH(LARGE(INDEX($G$2:$G$12+(ROWS($G$2:$G$12)-ROW($G$2:$G$12))/10^5,0),ROWS($1:1)),INDEX($G$2:$G$12+(ROWS($G$2:$G$12)-ROW($G$2:$G$12))/10^5,0),0)))

(changes in red), then copy down.

If you don't want to see the zeros in column G, you can do this in G13:

=IF(F13="","",LARGE(G$2:G$12,ROWS($1:1)))

and copy that down.

Hope this helps.

Pete