How to get two middle values in a range

  • Thread starter Thread starter daniel.bash
  • Start date Start date
D

daniel.bash

Hi!

I want to get two middle values from below range. I want the 1st
middle value to be given in cell A1 and the 2nd middle value to be
given in cell A2.



Range:
1, 2, 5, 3, 2, 4, 5, 2, 2, 3, 4, 2

Range sorted:
1
2
2
2
2
2 (1st middle value between 2 and 2)
3 (2nd middle value between 2 and 3)
3
4
4
5
5

Does anyone know what formula to use. I have been using quartile 2 and
quartile 3. Quartile 2 gives me the correct 1st middle value but
quartile 3 gives me incorrct 2nd middle value.

Thanks in advance.
 
Hi!

I want to get two middle values from below range. I want the 1st
middle value to be given in cell A1 and the 2nd middle value to be
given in cell A2.

Range:
1, 2, 5, 3, 2, 4, 5, 2, 2, 3, 4, 2

Range sorted:
1
2
2
2
2
2 (1st middle value between 2 and 2)
3 (2nd middle value between 2 and 3)
3
4
4
5
5

Does anyone know what formula to use. I have been using quartile 2 and
quartile 3. Quartile 2 gives me the correct 1st middle value but
quartile 3 gives me incorrct 2nd middle value.

Thanks in advance.
Click to expand...

How is the 1st middle value between 2 and 2?
Do you want the middle value or the middle cell, there are 12 cells,
you have selected the two middle cells that are the 6th and the 7th
cell.
The middle value for all the cells would be 2.5 so maybe you could
explain a little better .....
 
How is the 1stmiddlevalue between 2 and 2?
Do you want themiddlevalue or themiddlecell, there are 12 cells,
you have selected the twomiddlecells that are the 6th and the 7th
cell.
Themiddlevalue for all the cells would be 2.5 so maybe you could
explain a little better .....- Hide quoted text -

- Show quoted text -
Click to expand...


Hi,

Sorry for my poor explanation.

I have 12 cells with different values in them and I want the two
middle values:

Example:

1
2
3
4
5
6 1st middle value
7 2nd middle value
8
9
10
11
12

However my range is more like:
5
7
3
1
2
3
3
3
5
2
1
2

and sorted:
1
1
2
2
2
3 1st middle value
3 2nd middle value
3
3
5
5
7

What I need is the 1st middle value and the 2nd middle value to be
returned into two different cells. Hope it is more clear now.

/Daniel
 
Hi!

I want to get two middle values from below range. I want the 1st
middle value to be given in cell A1 and the 2nd middle value to be
given in cell A2.



Range:
1, 2, 5, 3, 2, 4, 5, 2, 2, 3, 4, 2

Range sorted:
1
2
2
2
2
2 (1st middle value between 2 and 2)
3 (2nd middle value between 2 and 3)
3
4
4
5
5

Does anyone know what formula to use. I have been using quartile 2 and
quartile 3. Quartile 2 gives me the correct 1st middle value but
quartile 3 gives me incorrct 2nd middle value.

Thanks in advance.
Click to expand...

I'm not sure exactly what you mean by "middle value" and "2nd middle value"

I will assume you mean the "median value".

There can be one or two median values depending on the distribution of numbers
in the set.

These formulas will return those values -- if there is only one median, both
formulas should return the same value; if there are two, the first will return
the lower; and the second the higher value.

=SMALL(rng,COUNTIF(rng,"<="&MEDIAN(rng)))
=LARGE(rng,COUNTIF(rng,">="&MEDIAN(rng)))

--ron
 
My reading of your problem is: of the 12 values you want the two that would
be in the middle if the range were to be sorted. Here is a UDF that does
that; Note it is called by selecting two cells entering a formula like
=MYCENTRE(A1:L1) and committing with CTRL+SHIFT+ENTER since it is an array
formula - it returns more than one value.

Function MyCentre(myrange)
Dim mytable(1 To 100)
Dim myholder(1 To 2)
last = myrange.Count
For j = 1 To last
mytable(j) = myrange(j)
Next j
For j = 1 To last - 1
For k = j To last
If mytable(j) > mytable(k) Then
temp = mytable(k)
mytable(k) = mytable(j)
mytable(j) = temp
End If
Next k
Next j

mymid = last / 2
myholder(1) = mytable(mymid)
myholder(2) = mytable(mymid + 1)
MyCentre = myholder
End Function

best wishes
 
Back
Top