MS wrote:
OK, calculating the mean from a set of numbers is easy.
Anyone have formula for calculating the median?
Ok, ok, I didn't do well at maths! :-(
I haven't found a way to do it in a single query, but I found a way
using two queries:
tblValues
ID AutoNumber
Value Double
qryRankForMedian:
SELECT tblValues.Value, (SELECT Count(A.ID) FROM tblValues AS A WHERE
A.Value < tblValues.Value) + (SELECT Count(A.ID) FROM tblValues AS A
WHERE A.Value = tblValues.Value AND A.ID < tblValues.ID) + 1 AS
Ranking, (SELECT Count(*) FROM tblValues) / 2 + 0.5 AS WantRanking FROM
tblValues;
qryMedian:
SELECT Avg(qryRankForMedian.Value) AS Median FROM qryRankForMedian
WHERE ((Abs([Ranking]-[WantRanking])<0.6));
I haven't tested it out completely but it ran on all the examples I
tried.
James A. Fortune