Manual calculation of the median from a series of grades

[JC21]

I have an Excel spreadsheet with students who received a certain grade on an assessment. They are listed in alphabetical order, as follows:

I would like to perform the following statistical dispersion operations: median, Q1, and Q3 on this series of grades WITHOUT using Excel's built-in functions. This first requires that the data series be sorted in ascending order. However, I want the data series to remain in its original order, and only have the three values corresponding to: the median, Q1, and Q3 appear in three cells next to my table. How can I perform such operations on my original table while keeping it unchanged? Should I create a sort of "temporary" table where the data is sorted in ascending order and then perform the operations? Is there a formula in Excel to do this?

Thank you for your help

 

[AlphaFrog]

I'm confused. The Median and Quartile worksheet functions do not require the data to be sorted.

 

[JC21]

"Oh really? How can you calculate the median of a series of values 'manually' without sorting these values in ascending order?"

 

[shift-del]

Hi

=MEDIAN(range)

 

[JC21]

I said i didn't want to use "built-in" fonctions from Excel...

 

[shift-del]

If you are using Excel there is no reason why you wouldn't use all the available functions.

 

[JC21]

Reason or not I don't want to use the Excel built-in functions, so is there a way to do what I want without using them ? That's all I want to know

 

[shift-del]

Then how did you calculate Min, Max and Avg?

 

[shift-del]

You need to specify which functions are "allowed" and which are "not allowed".

 

[offthelip]

if you are allowed to write your own EXCEL function here is a UDF that will calculate the MEDIAN: ( without any sorting!!)

Function mymedian(Myrng As Range) As Variant
inarr = Myrng
ub = UBound(inarr, 1)
lb = LBound(inarr, 1)

If ub Mod 2 = o Then
 For i = 1 To ub
   temp1 = inarr(i, 1)
   lowcnt = 0
   hicnt = 0
  For j = 1 To ub
    If j <> i Then
     If inarr(j, 1) > temp1 Then
      hicnt = hicnt + 1
     End If
     If inarr(j, 1) < temp1 Then
      lowcnt = lowcnt + 1
     End If
    End If
  Next j
  If (lowcnt - hicnt) = 1 Then
     botv = inarr(i, 1)
  End If
  If (lowcnt - hicnt) = -1 Then
     topv = inarr(i, 1)
  End If
 Next i
  mymedian = (botv + topv) / 2
Else
 
 For i = 1 To ub
   temp1 = inarr(i, 1)
   lowcnt = 0
   hicnt = 0
  For j = 1 To ub
    If j <> i Then
     If inarr(j, 1) > temp1 Then
      hicnt = hicnt + 1
     End If
     If inarr(j, 1) < temp1 Then
      lowcnt = lowcnt + 1
     End If
    End If
  Next j
  If lowcnt = hicnt Then
   mymedian = temp1
   Exit For
  End If
 Next i
End If


End Function