Solved: How to select top three visible results?

[yasarayhanka]

Hello,
I have three cells that I need to fill with highest value, second highest value and third.

I tried
=Large(array,1)
=large(array,2)
=large(array,3)

but this includes invisible cells too.
my sheet has subtotals included in it so the formula ends up picking my subtotals.
Thanks for the help in advance,

Yasar

[lucas]

but this includes invisible cells too.
my sheet has subtotals included in it so the formula ends up picking my subtotals.
Thanks for the help in advance,

Wouldn't that depend on how your array is populated....which you didn't show us...

[yasarayhanka]

Wouldn't that depend on how your array is populated....which you didn't show us...

Right, :banghead:



when I use Large(array1,1) formula it picks 106,
Also it will be great if I could show the stlyles for those top three numbers as well.

Thanks,
Yasar

[lucas]

What if you remove the subtotals first?

[yasarayhanka]

What if you remove the subtotals first?

That will require me to reformat the whole sheet, so I would rather do not use that option,

I hide the subtotals by a custom filter so I only have the main data visible,
I can copy the data to another sheet and pick the top three with the formula I used, but I was hoping that there was an easier way to just apply the formula on the visible cells,

thanks,
Yasar

[lucas]

I think that method is your best bet...maybe someone else has a better idea...

[p45cal]

For column C, array-entered:

=LARGE(IF((NOT(ISNUMBER(SEARCH("Total",$A$2:$A$17)))*NOT(ISNUMBER(SEARCH("Total",$B$2:$B$17)))*ISNUMBER(C$2:C$17)),C$2:C$17),1) changing the final '1' to '2' and '3' for the other two values.

For column D, array-entered:

=LARGE(IF((NOT(ISNUMBER(SEARCH("Total",$A$2:$A$17)))*NOT(ISNUMBER(SEARCH("Total",$B$2:$B$17)))*ISNUMBER(D$2:D$17)),D$2:D$17),1)

Or, to save the editing, array-enter the following formula in the left three cells of a 3 row by 2 column range (with all three cells selected as you commit the formula), then use the autofill handle to autofill the three cells to the right.

=LARGE(IF((NOT(ISNUMBER(SEARCH("Total",$A$2:$A$17)))*NOT(ISNUMBER(SEARCH("Total",$B$2:$B$17)))*ISNUMBER(C$2:C$17)),C$2:C$17),ROW(1:3))
All the above depend on the word "Total" (case insensitive) being in at least one of columns A and B to exclude the subtotals from the Large function.

To double check, select each cell in turn, and press F2 to ensure it's looking at the right ranges, then Esc to avoid accidentally forgetting to array-enter if it's right.
p45cal

[yasarayhanka]

For column C, array-entered:

=LARGE(IF((NOT(ISNUMBER(SEARCH("Total",$A$2:$A$17)))*NOT(ISNUMBER(SEARCH("Total",$B$2:$B$17)))*ISNUMBER(C$2:C$17)),C$2:C$17),1) changing the final '1' to '2' and '3' for the other two values.

For column D, array-entered:

=LARGE(IF((NOT(ISNUMBER(SEARCH("Total",$A$2:$A$17)))*NOT(ISNUMBER(SEARCH("Total",$B$2:$B$17)))*ISNUMBER(D$2:D$17)),D$2:D$17),1)

Or, to save the editing, array-enter the following formula in the left three cells of a 3 row by 2 column range (with all three cells selected as you commit the formula), then use the autofill handle to autofill the three cells to the right.

=LARGE(IF((NOT(ISNUMBER(SEARCH("Total",$A$2:$A$17)))*NOT(ISNUMBER(SEARCH("Total",$B$2:$B$17)))*ISNUMBER(C$2:C$17)),C$2:C$17),ROW(1:3))
All the above depend on the word "Total" (case insensitive) being in at least one of columns A and B to exclude the subtotals from the Large function.

To double check, select each cell in turn, and press F2 to ensure it's looking at the right ranges, then Esc to avoid accidentally forgetting to array-enter if it's right.
p45cal


P45Cal
Thanks for the formulas, they work great,:hi:
Yasar