Mode When Ignoring Zeroes

[Guest]

I've done some searching; I hope this hasn't been answered elsewhere.

I have a column of data where I want to calculate the modes while ignoring
zeroes that are in the table. So:

Column 1
1
1
1
0
0
0
0
2
3

Mode w/o Zeroes = 1

Any suggestions?

 

[Chip Pearson]

Try the following array formula:

=MODE(IF(A1:A10<>0,A1:A10,FALSE))

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula enclosed in curly
braces { }.

See http://www.cpearson.com/excel/ArrayFormulas.aspx for much more
information about array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Thanks. Smacking my head on this one; I was fiddling with a similar, flawed
formula; now I'm straightened out.

 

[David Biddulph]

=MODE(IF(A1:A9<>0,A1:A9,"")) entered as an array formula
(Control-Shift-Enter)