#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Anita
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John Michl
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default MODE Function

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" 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?

Any suggestions gratefully received.

Thanks

Anita



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default 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?

Any suggestions gratefully received.

Thanks

Anita

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Anita
 
Posts: n/a
Default 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
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" 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?

Any suggestions gratefully received.

Thanks

Anita






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bernie Deitrick
 
Posts: n/a
Default 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
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" 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?

Any suggestions gratefully received.

Thanks

Anita






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing worksheet cells from within a function James4U2enjoy Setting up and Configuration of Excel 1 October 14th 05 02:16 PM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
Hyperlinks using R[1]C[1] and offset function in its cell referenc Elijah-Dadda Excel Worksheet Functions 0 March 5th 05 04:31 AM
Conversion SVC Excel Worksheet Functions 9 February 28th 05 03:29 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM


All times are GMT +1. The time now is 02:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"