Most frequent item in a list (excluding zeros)
 

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é

Most frequent item in a list (excluding zeros)
 

Hi!

Try this:

Array entered:

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

Biff

"Andre Croteau" wrote in message
...
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é

Most frequent item in a list (excluding zeros)
 

Hello Biff,

Thanks, works perfect!

André

"Biff" wrote in message
...
Hi!

Try this:

Array entered:

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

Biff

"Andre Croteau" wrote in message
...
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é

Most frequent item in a list (excluding zeros)
 

You're welcome. Thanks for the feedback!

Biff

"Andre Croteau" wrote in message
...
Hello Biff,

Thanks, works perfect!

André

"Biff" wrote in message
...
Hi!

Try this:

Array entered:

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

Biff

"Andre Croteau" wrote in message
...
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é