Most frequent item in a list (excluding zeros)

[Andre Croteau]

Hello,

I have a range of 12 amounts, and I want to find the most frequent used item
but not including the ZERO value

A1 0
A2 0
A3 0
A4 0
A5 0
A6 33
A7 17
A8 17
A9 17
A10 0
A11 0
A12 0

I found an array formula in Chip Pearson's site, and it gives me the correct
result "0", since it appears 8 times

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))


Can this formula be modified so that it EXCLUDES the value ZERO when
examining the data??

Thank you in advance.

André

 

[Biff]

Hi!

Try this:

Array entered:

=MODE(IF(A1:A12<>0,A1:A12))

Biff

 

[Andre Croteau]

Hello Biff,

Thanks, works perfect!

André

Hi!

Try this:

Array entered:

=MODE(IF(A1:A12<>0,A1:A12))

Biff

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Hello Biff,

Thanks, works perfect!

André