I need my LARGE formula to ignore zeros

ricardo9316 · Aug 28, 2014

A	B
100
0
80
0
60
0
40
0
20
0

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

ghrain22 · Aug 28, 2014

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 · Aug 28, 2014

<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 · Aug 28, 2014

Try

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

VBA Geek · Aug 29, 2014

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))

[FONT=&quot]My Values[/FONT]	[FONT=&quot]Large[/FONT]
[FONT=&quot]100[/FONT]	[FONT=&quot]100[/FONT]
[FONT=&quot]0[/FONT]	[FONT=&quot]80[/FONT]
[FONT=&quot]80[/FONT]	[FONT=&quot]20[/FONT]
[FONT=&quot]0[/FONT]	[FONT=&quot]-5[/FONT]
[FONT=&quot]-5[/FONT]	[FONT=&quot]-100[/FONT]
[FONT=&quot]0[/FONT]
[FONT=&quot]-100[/FONT]
[FONT=&quot]0[/FONT]
[FONT=&quot]20[/FONT]
[FONT=&quot]0[/FONT]

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 · Aug 29, 2014

If using Excel 2010 or later, perhaps this, copied down.

Excel Workbook

A

B

1

100

2

0

80

3

80

40

4

-10

20

5

-60

-10

6

0

7

40

8

0

9

20

10

0

11

Large List

I need my LARGE formula to ignore zeros

ricardo9316

New Member

Excel Facts

ghrain22

Active Member

VBA Geek

MrExcel MVP

jasonb75

Well-known Member

VBA Geek

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

I need my LARGE formula to ignore zeros

ricardo9316

New Member

Excel Facts

ghrain22

Active Member

VBA Geek

MrExcel MVP

jasonb75

Well-known Member

VBA Geek

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock