PERCENTILE(array,0.5)
For example: Array = B152:B156=i.e. the range of the values whose median is to be determined
-----Original Message-----
From: A UK-based worldwide e-mail broadcast system mailing list [mailto:[log in to unmask]] On Behalf Of Nigel Marriott
Sent: 06 July 2011 17:51
To: [log in to unmask]
Subject: SUMMARY: Median function in Excel
Dear Allstatters,
Thank you for the quick response to my question about the Median function in Excel. All the respondents confirmed that it doesn't exist and that you have to code it yourself. The code options I was given are below, I haven't tested them as yet.
SELECT CASE WHEN (SELECT COUNT(*) % 2 FROM filename) = 0 THEN ((SELECT
MAX(Valuename) FROM (SELECT TOP 50 PERCENT Valuename FROM tablename ORDER BY
Valuename)a) + (SELECT MIN(Valuename) FROM (SELECT TOP 50 PERCENT Valuename FROM tablename ORDER BY Valuename DESC)b))/2.0 ELSE (SELECT MAX(Valuename) from (SELECT TOP 50 PERCENT Valuename from Tablename ORDER BY Valuename) a) END MEDIAN
SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2
I use DB2 SQL and have found a way of calculating medians using the following code on page 421 of the DB2 Cookbook v9.7 http://mysite.verizon.net/Graeme_Birchall/id1.html
Have you seen http://www.1keydata.com/sql/sql-median.html ?
Regards
Nigel Marriott
Chartered Statistician
<http://www.marriott-stats.com/> www.marriott-stats.com
Ground Floor, 21 Marlborough Buildings, Bath BA1 2LY, United Kingdom
Tel (mobile) +44 (0)773 4069997
Tel (office) +44 (0)1225 489033
Fax +44 (0)870 6221969
Marriott Statistical Consulting Limited, Company No. 5577275, VAT No.
883304029
Registered in England, Registered Office - Equity House, 4-6 School Road,
Tilehurst, Reading, RG31 5AL
You may leave the list at any time by sending the command
SIGNOFF allstat
to [log in to unmask], leaving the subject line blank.
You may leave the list at any time by sending the command
SIGNOFF allstat
to [log in to unmask], leaving the subject line blank.
|