Median and Mean

[MS]

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! :-(

[James]

On Thu, 17 Mar 2005 21:34:44 GMT, "MS" <Em...@Myemail.com> wrote:

>OK, calculating the mean from a set of numbers is easy.
>
>Anyone have formula for calculating the median?

Sort the array of values (ascending or descending, doesn't matter)

Midpoint is the median (if odd number of elements)

If there are an even number of elements, take half-way between the two
values in the middle.

>Ok, ok, I didn't do well at maths! :-(

There is a built in function (statistical) in Excel if you want to
transfer the data there for the calculations....

James

[pietl...@hotmail.com]

http://support.microsoft.com/default.aspx?scid=kb;en-us;95918

[jimfo...@compumarc.com]

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