MODE Function

I have a spreadsheet containing 6 cells say with the following values ...

1 2 3 1 2 3

I want to work out the MODE and if there isn't one, then work out an
average, but the problem is if there is no mode it displays the first cell
value (i.e in the above example that would be 1). Surely this isn't right?

Any suggestions gratefully received.

Thanks

Anita

Actually, your distribution not only has one mode it has three...it is
tri-modal and the MODE function is returning the first mode found which
also happens to start in the first cell.

I don't have time to work it out now but you may be able to construct
an array formula that counts the number of occurences of each value,
then counts the number of times the maximum count is hit. If the MAX
of the counts is = 1 then an if formula could show, "No Mode". If the
MAX of counts is > 1 AND the count of MAXs is >1 then the IF statement
could print, "Multiple Modes", Else it could print the single mode.

Good luck.

- John

Anita,

If your six values are in cells A1:A6, the following array formula - entered using
Ctrl-Shift-Enter - will help you on your way:

=IF(SUM(IF(COUNTIF(A1:A6,A1:A6)=COUNTIF(A1:A6,MODE(A1:A6)),1))/COUNTIF(A1:A6,MODE(A1:A6))=1,TEXT(MODE(A1:A6),"0.0")
& " Mode",TEXT(AVERAGE(A1:A6), "0.00") & " Average")

Remove any line-feeds inserted by your browser/mail reader.

HTH,
Bernie
MS Excel MVP


"Anita" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet containing 6 cells say with the following values ...
>
> 1 2 3 1 2 3
>
> I want to work out the MODE and if there isn't one, then work out an
> average, but the problem is if there is no mode it displays the first cell
> value (i.e in the above example that would be 1). Surely this isn't right?
>
> Any suggestions gratefully received.
>
> Thanks
>
> Anita

MODE returns a correct value for 1 (along with 2) is the most frequent
numeric value given: 1 2 3 1 2 3.

If such a value does not exist, that is, there is no value with a
frequency of occurrence greater than one, MODE would return #N/A.

Maybe you're looking for something like:

=IF(ISNUMBER(MODE(Range)),MODE(Range),AVERAGE(Range))

Anita wrote:
> I have a spreadsheet containing 6 cells say with the following values ...
>
> 1 2 3 1 2 3
>
> I want to work out the MODE and if there isn't one, then work out an
> average, but the problem is if there is no mode it displays the first cell
> value (i.e in the above example that would be 1). Surely this isn't right?
>
> Any suggestions gratefully received.
>
> Thanks
>
> Anita

Hi

Thanks - I've tried it and it works out the average but doesn't calculate
the mode correctly if there is only one mode. What am I doing wrong? I
don't really understand what the formula is doing I just typed it in. But I
want it to calculate the mode if there aren't multiples otherwise average.
Hope I'm making sense.

Many thanks

"Bernie Deitrick" wrote:

> Anita,
>
> If your six values are in cells A1:A6, the following array formula - entered using
> Ctrl-Shift-Enter - will help you on your way:
>
> =IF(SUM(IF(COUNTIF(A1:A6,A1:A6)=COUNTIF(A1:A6,MODE(A1:A6)),1))/COUNTIF(A1:A6,MODE(A1:A6))=1,TEXT(MODE(A1:A6),"0.0")
> & " Mode",TEXT(AVERAGE(A1:A6), "0.00") & " Average")
>
> Remove any line-feeds inserted by your browser/mail reader.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Anita" <[email protected]> wrote in message
> news:[email protected]...
> >I have a spreadsheet containing 6 cells say with the following values ...
> >
> > 1 2 3 1 2 3
> >
> > I want to work out the MODE and if there isn't one, then work out an
> > average, but the problem is if there is no mode it displays the first cell
> > value (i.e in the above example that would be 1). Surely this isn't right?
> >
> > Any suggestions gratefully received.
> >
> > Thanks
> >
> > Anita
>
>
>

Anita,

It worked fine for me. If there is only one mode, it returns

TEXT(MODE(A1:A6),"0.0") & " Mode"

which is the mode to one decimal, which may be what leads you to think that the mode is incorrect.
You could change that to just

MODE(A1:A6)

but there wouldn't be anyway to differentiate between mode and average.

HTH,
Bernie
MS Excel MVP


"Anita" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> Thanks - I've tried it and it works out the average but doesn't calculate
> the mode correctly if there is only one mode. What am I doing wrong? I
> don't really understand what the formula is doing I just typed it in. But I
> want it to calculate the mode if there aren't multiples otherwise average.
> Hope I'm making sense.
>
> Many thanks
>
> "Bernie Deitrick" wrote:
>
>> Anita,
>>
>> If your six values are in cells A1:A6, the following array formula - entered using
>> Ctrl-Shift-Enter - will help you on your way:
>>
>> =IF(SUM(IF(COUNTIF(A1:A6,A1:A6)=COUNTIF(A1:A6,MODE(A1:A6)),1))/COUNTIF(A1:A6,MODE(A1:A6))=1,TEXT(MODE(A1:A6),"0.0")
>> & " Mode",TEXT(AVERAGE(A1:A6), "0.00") & " Average")
>>
>> Remove any line-feeds inserted by your browser/mail reader.
>>
>> HTH,
>> Bernie
>> MS Excel MVP
>>
>>
>> "Anita" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a spreadsheet containing 6 cells say with the following values ...
>> >
>> > 1 2 3 1 2 3
>> >
>> > I want to work out the MODE and if there isn't one, then work out an
>> > average, but the problem is if there is no mode it displays the first cell
>> > value (i.e in the above example that would be 1). Surely this isn't right?
>> >
>> > Any suggestions gratefully received.
>> >
>> > Thanks
>> >
>> > Anita
>>
>>
>>