Remember Me? December 12th 05, 02:52 PM posted to microsoft.public.excel.worksheet.functions
 Anita Posts: n/a 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?

Thanks

Anita December 12th 05, 03:43 PM posted to microsoft.public.excel.worksheet.functions
 John Michl Posts: n/a MODE Function

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 December 12th 05, 03:47 PM posted to microsoft.public.excel.worksheet.functions
 Bernie Deitrick Posts: n/a MODE Function

Anita,

If your six values are in cells A1:A6, the following array formula - entered using

=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")

HTH,
Bernie
MS Excel MVP

"Anita" wrote in message
...
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?

Thanks

Anita December 12th 05, 04:01 PM posted to microsoft.public.excel.worksheet.functions MODE Function

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(Rang e))

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?

Thanks

Anita December 12th 05, 05:13 PM posted to microsoft.public.excel.worksheet.functions
 Anita Posts: n/a MODE Function

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

=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")

HTH,
Bernie
MS Excel MVP

"Anita" wrote in message
...
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?

Thanks

Anita December 12th 05, 05:28 PM posted to microsoft.public.excel.worksheet.functions
 Bernie Deitrick Posts: n/a MODE Function

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

=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")

HTH,
Bernie
MS Excel MVP

"Anita" wrote in message
...
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?

Thanks

Anita

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post James4U2enjoy Setting up and Configuration of Excel 1 October 14th 05 02:16 PM Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM

All times are GMT +1. The time now is 08:00 PM. Copyright ©2004-2019 ExcelBanter.