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

 

[AhoyNC]

Here are a couple of formula ways to get the MEDIAN.
The formulas in B4 & B5 are the same. The one in B5 just use the LET function. Formula in B6 is just a check using the Excel Median.
As you know you have to sort the data and also determine if you have a even or odd number of samples.
Maybe from this example you can figure out hoew to get the Q1 & Q3.

Median Manual Cal.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Eleve	Arc	Bet	Cas	Cha	Del	Des	El'/	Fou	Fri	Gai	Gil	Her	H ub	Laf	LeP	Lou	Mag	Mar	Mig	Mor	Nav	Pal	Per	Raj	Rou	Sac	Sai	Ver	Vey
2	Controlel	7.5	8	6	7.5	6	6.5	9	9	8.5	10	9	9	7	10	2	7	8.5	10	9	10	5.5	10	4	9.5	4	10	8.5	10	8.5
3
4	Median	8.5
5	Let Function	8.5
6	Excel Median	8.5
7
Sheet4

Cell Formulas
Range	Formula
B4	B4	=IF(ISODD(COUNT(B2:AD2)),INDEX(SORT(B2:AD2,,,TRUE),ROUNDUP(COUNT(B2:AD2)/2,0)),AVERAGE(INDEX(SORT(B2:AD2,,,TRUE),COUNT(B2:AD2)/2),INDEX(SORT(B2:AD2,,,TRUE),COUNT(B2:AD2)/2+1)))
B5	B5	=LET(ct,COUNT(B2:AD2),s,SORT(B2:AD2,,,TRUE),IF(ISODD(ct),INDEX(s,ROUNDUP(ct/2,0)),AVERAGE(INDEX(s,ct/2),INDEX(s,ct/2+1))))
B6	B6	=MEDIAN(B2:AD2)

 

[JC21]

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

I just don't want to use the built-in functions "median" and "quartiles"
so I imagined i had to create a temporary table where values would be ranged in ascending order

 

[Eric W]

Another Median formula, no extra tables or internal sorting:

=LET(c,COUNT(B2:AD2),AVERAGE(SMALL(B2:AD2,SEQUENCE(2-MOD(c,2),,INT((c+1)/2)))))

And incidentally, why don't you want to use those functions?

 

[shift-del]

I just don't want to use the built-in functions "median" and "quartiles"

=AGGREGATE(12,,B2:AD2)]
=AGGREGATE(17,,B2:AD2,1)
=AGGREGATE(17,,B2:AD2,3)]

 

[felixstraube]

How about this?:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Eleve	Arc	Bet	Cas	Cha	Del	Des	El'/	Fou	Fri	Gai	Gil	Her	H ub	Laf	LeP	Lou	Mag	Mar	Mig	Mor	Nav	Pal	Per	Raj	Rou	Sac	Sai	Ver	Vey
2	Controlel	7.5	8	6	7.5	6	6.5	9	9	8.5	10	9	9	7	10	2	7	8.5	10	9	10	5.5	10	4	9.5	4	10	8.5	10	8.5
3
4
5	Median	8.5
6	Q1	7
7	Q3	9.5
Sheet2

Cell Formulas
Range	Formula
B5	B5	=LET(d, B2:AD2, sd, SORT(d, ,,1), n, COLUMNS(d), q, INT(n/2), IF(ISEVEN(n), (INDEX(sd, 1, q)+INDEX(sd, 1, q+1))/2, INDEX(sd, 1, q+1)) )
B6	B6	=LET(d, B2:AD2, n, COLUMNS(d), sd, TAKE(SORT(d, ,,1), 1, INT(n/2)+1), nn, COLUMNS(sd), q, INT(nn/2), IF(ISEVEN(nn), (INDEX(sd, 1, q)+INDEX(sd, 1, q+1))/2, INDEX(sd, 1, q+1)) )
B7	B7	=LET(d, B2:AD2, n, COLUMNS(d), sd, DROP(SORT(d, ,,1), , INT(n/2)), nn, COLUMNS(sd), q, INT(nn/2), IF(ISEVEN(nn), (INDEX(sd, 1, q)+INDEX(sd, 1, q+1))/2, INDEX(sd, 1, q+1)) )

No need for helper cell/tables