MEDIAN, MODE.SNGL and MODE.MULTI Functions

[bobhc]

Good day all

I have a list that I have been using to run the MEDIAN and MODE.SNGL functions but I do not seem to be getting the right results.


31

95

76

24

31

15

24

76

28

18

60

11

95

48

90

52

38

17

The MEDIAN function is returning 38, but I thought that as an uneven list of numbers it would return the actual middle number which is 28!!


The MODE.SNGL is returning 38 but 76 is there twice as well I have tried MODE.MULTI "=MODE.MULT(B62:B79,B62:B79)" but I am sure I am not running it correctly as it returns 38 as well!!

 

[Hui]

Bobhc


From the Excel Help


MODE.MULT() function

Show AllHide All

Returns a vertical array of the most frequently occurring, or repetitive values in an array or range of data.


If you select several cells in a vertical column and enter =MODE.SNGL(A1:A18) Ctrl Enter

Excel will respond with:

31

95

76

24


which are the 4 cells which occur twice

31 is first as it is the first value to occur twice


Same with Mode.Multi()


Median()

Returns the median of the given numbers. The median is the number in the middle of a set of numbers


If you sort the list the middle two numbers are 31 & 38

=MEDIAN(A1:A18) = 34.5 = (31+38)/2

It is the middle value of the sorted list


Hope that helps in some way

 

[bobhc]

Hi Hui

Cleared the mind my thanks. I entered the =MODE.SNGL(A1:A18) (click and drag to choose data cells)close parentheses and then enter, received same result as click enter?

 

[Hui]

Bobhc


My mistake


It doesn't return an Array, if you use =MODE.SNGL(A$1:A$18) you get 4, 31's


It was just coincidence that the 4 repeat values were the first 4 values

 

[bobhc]

HI Hui

I have started a new sheet as I was beginning to think the data may be corrupted.

Have uploaded it, if you would be so good as to take a look and tell me just how I am managing to screw up a simple function I would be obliged.,


https://dl.dropbox.com/u/75495784/MODE.SNGL.xlsx

 

[Hui]

Bobhc


Mode.sngl() is finding the first item that has 2 occurrences in your list, in this case 95

If you sort the list A-Z it changes to 24


If you had 3 x 31's it would show 31 as that is the only value with 3 occurrences


As per my previos post, It was just coincidence that the 4 repeat values were the first 4 values

 

[bobhc]

Me again

I am taking liberty's here but I appreciate your time, I have resorted but I am still getting more results than there are data cells?? I have uploaded the sheet, can you tell me how I an getting so many results


https://dl.dropbox.com/u/75495784/MODE.SNGL_2.xlsx

 

[Hui]

Bobhc


Your problem is that =MODE.SNGL(A1:A17) should be =MODE.SNGL(A$1:A$17)

when you drag =MODE.SNGL(A1:A17) down the range changes in each cell

so in the second cell down it is =MODE.SNGL(A2:A18)

In the third cell down it is =MODE.SNGL(A3:A19) etc

 

[Hui]

Bobhc


What you want is Mode.Mult()

select 5 vertical cells then

=MODE.MULT($A$1:$A$17) Ctrl+Shift+Enter


If you want to extract each one use index:

=INDEX(MODE.MULT($A$1:$A$17),1)

=INDEX(MODE.MULT($A$1:$A$17),2)

=INDEX(MODE.MULT($A$1:$A$17),3) etc

 

[bobhc]

Hi Hui

I am lost, I have entered the function as absolute reference and uploaded, I am going to find a bottle of 15 year old malt and have a drink or two


https://dl.dropbox.com/u/75495784/MODE.SNGL_3.xlsx

 

[Hui]

Bobhc


You have 4 values that occur twice, 24, 31, 76 and 95

24 occurs twice, first and so is displayed


=INDEX(MODE.MULT($A$1:$A$17),2) will show 31

=INDEX(MODE.MULT($A$1:$A$17),3) will show 76 etc


You could do something like =AVERAGE(MODE.MULT($A$1:$A$17))

which is the average of 24, 31, 76 and 95 = 56.5

 

[Hui]

Bobhc

Might be worth looking at http://www.youtube.com/watch?v=ewF8E982bfs

bed time for me

 

[bobhc]

Hi Hui

The INDEX(MODE.MULT works but to use would you not need to know how many duplicates in a column there was,does this not make the MODE,SNGL unusable.

=AVERAGE(MODE.MULT does not seem to doe the job.


https://dl.dropbox.com/u/75495784/MODE.SNGL_4.xlsx