Home 
Search 
Today's Posts 
#1




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




MODE Function
Actually, your distribution not only has one mode it has three...it is
trimodal 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




MODE Function
Anita,
If your six values are in cells A1:A6, the following array formula  entered using CtrlShiftEnter  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 linefeeds 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




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




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 CtrlShiftEnter  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 linefeeds 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




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 CtrlShiftEnter  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 linefeeds 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 
Display Modes  


Similar Threads  
Thread  Forum  
Changing worksheet cells from within a function  Setting up and Configuration of Excel  
Automatically up date time in a cell  Excel Discussion (Misc queries)  
Hyperlinks using R[1]C[1] and offset function in its cell referenc  Excel Worksheet Functions  
Conversion  Excel Worksheet Functions  
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE.  Excel Worksheet Functions 