Hi

I have a list of numbers that I want to find the MODE in:

0
0
1
2
0
0
3
3
2
2
2
0
0

But I want to be able to ignore the Zeros
How do i go about doing this so the mode function gives me the #2 only
Thank you


--
Dev4me
------------------------------------------------------------------------
Dev4me's Profile: http://www.excelforum.com/member.php...o&userid=29709
View this thread: http://www.excelforum.com/showthread...hreadid=498091

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

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dev4me" <(E-Mail Removed)> wrote in
message news(E-Mail Removed)...
>
> Hi
>
> I have a list of numbers that I want to find the MODE in:
>
> 0
> 0
> 1
> 2
> 0
> 0
> 3
> 3
> 2
> 2
> 2
> 0
> 0
>
> But I want to be able to ignore the Zeros
> How do i go about doing this so the mode function gives me the #2 only
> Thank you
>
>
> --
> Dev4me
> ------------------------------------------------------------------------
> Dev4me's Profile:
http://www.excelforum.com/member.php...o&userid=29709
> View this thread: http://www.excelforum.com/showthread...hreadid=498091
>

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

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

Biff

"Dev4me" <(E-Mail Removed)> wrote in
message news(E-Mail Removed)...
>
> Hi
>
> I have a list of numbers that I want to find the MODE in:
>
> 0
> 0
> 1
> 2
> 0
> 0
> 3
> 3
> 2
> 2
> 2
> 0
> 0
>
> But I want to be able to ignore the Zeros
> How do i go about doing this so the mode function gives me the #2 only
> Thank you
>
>
> --
> Dev4me
> ------------------------------------------------------------------------
> Dev4me's Profile:
> http://www.excelforum.com/member.php...o&userid=29709
> View this thread: http://www.excelforum.com/showthread...hreadid=498091
>

Thanks guys it worked!

What does it mean by entering as Array formula with Ctrl shift Enter

--
Dev4m
-----------------------------------------------------------------------
Dev4me's Profile: http://www.excelforum.com/member.php...fo&userid=2970
View this thread: http://www.excelforum.com/showthread.php?threadid=49809

Thanks guys it worked!

What does it mean by entering as Array formula with Ctrl shift Enter?


--
Dev4me
------------------------------------------------------------------------
Dev4me's Profile: http://www.excelforum.com/member.php...o&userid=29709
View this thread: http://www.excelforum.com/showthread...hreadid=498091

It means that when you enter the formula, don't just hit the Enter key, but
press Ctrl-Shift-Enter together.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dev4me" <(E-Mail Removed)> wrote in
message news(E-Mail Removed)...
>
> Thanks guys it worked!
>
> What does it mean by entering as Array formula with Ctrl shift Enter?
>
>
> --
> Dev4me
> ------------------------------------------------------------------------
> Dev4me's Profile:
http://www.excelforum.com/member.php...o&userid=29709
> View this thread: http://www.excelforum.com/showthread...hreadid=498091
>