# I need my LARGE formula to ignore zeros

#### ricardo9316

##### New Member
 A B 100 0 80 0 60 0 40 0 20 0

<tbody>
</tbody>

I'm trying to set up a formula that would give me the 5 largest values in B1:B5 while ignoring zeros in A1:A10

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### ghrain22

##### Active Member
Large(If(\$A\$1:\$A\$10 <> 0, \$A\$1:\$A\$10,""),1)
Large(If(\$A\$1:\$A\$10 <> 0, \$A\$1:\$A\$10,""),2)
Large(If(\$A\$1:\$A\$10 <> 0, \$A\$1:\$A\$10,""),3)
....
With Ctrl + Shft + Enter

#### VBA Geek

##### MrExcel MVP
<b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">{=LARGE(<font color="Blue">IF(<font color="Red">A\$1:A\$10<>0,A\$1:A\$10</font>),ROWS(<font color="Red">B\$1:B1</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### jasonb75

##### Well-known Member
Try

=IFERROR(1/(1/LARGE(\$A\$1:\$A\$10,ROWS(\$B\$1:\$B1))),"")

#### VBA Geek

##### MrExcel MVP
I think this will leave empty rows in case you have ngative nums

if you wanna avod using array formula, perhaps

B2: =LARGE(A\$2:A\$11,(COUNTIF(B\$1:B1,">0")=COUNTIF(A\$2:A\$11,">0"))*COUNTIF(A\$2:A\$11,0)+ROWS(B\$2:B2))

<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]-->
 [FONT="]My Values[/FONT]​ [FONT="]Large[/FONT]​ [FONT="]100[/FONT]​ [FONT="]100[/FONT]​ [FONT="]0[/FONT]​ [FONT="]80[/FONT]​ [FONT="]80[/FONT]​ [FONT="]20[/FONT]​ [FONT="]0[/FONT]​ [FONT="]-5[/FONT]​ [FONT="]-5[/FONT]​ [FONT="]-100[/FONT]​ [FONT="]0[/FONT]​ [FONT="]-100[/FONT]​ [FONT="]0[/FONT]​ [FONT="]20[/FONT]​ [FONT="]0[/FONT]​

<tbody>
</tbody>
if you can use helper cells for COUNTIF(A\$2:A\$11,">0") and COUNTIF(A\$2:A\$11,0), it can be mcuh faster than the array formula

#### Peter_SSs

##### MrExcel MVP, Moderator
If using Excel 2010 or later, perhaps this, copied down.

Excel Workbook
AB
1100100
2080
38040
4-1020
5-60-10
60
740
80
920
100
11
Large List

Replies
7
Views
349
Replies
3
Views
484
Replies
1
Views
154
Replies
5
Views
728
Replies
2
Views
450

1,191,629
Messages
5,987,772
Members
440,109
Latest member
mitra2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back