In answer to a posting by Frank Hebbert on 6/24:
Here's something I wrote up for MapInfo's Knowledge Base last time the
question of calculating medians came up. The information below really
ought to be a compiled into nice simple fifty-line MapBasic tool, but I've
been a bit short of time recently ... so I haven't put the document into
the public KB yet.
Issue:
How can the median of a column of data be calculated in MapInfo
Professional?
Discussion:
The median value is not one of the aggregate functions provided with
MIPro.
Here's an Internet definition of "median":
The median of an ordered list is the middle value if the number of
elements is odd. If the number of elements is even, then the median is
the average of the two middle values.
The mean (average) depends on the distribution. If the distribution is
either positively or negatively skewed, then the median will not equal the
mean.
---------------------------------------------------------
It's easy enough to calculate the median in a MapBasic program, or even in
a series of SQL-type statements:
Select (your column) from (your table) Order By (your column) into Temp
Look at the status bar for the resulting browser window.
If the number of records is even, do
Select Avg({your column}) "Median" from Temp where RowID={number
of records}/2 or RowID={number of records}/2+1 Into MedianFinal
If the number of records is odd, do
Select ({your column}) "Median" from Temp where RowID={number of
records+1)/2 Into MedianFinal
Conclusion:
You can write a single Select statement that would work whether or not the
number of records was even or odd, though it's kind of ugly... e.g. you
could copy and paste the code below into the MapBasic window, and it would
give you the median value for the relevant column.
You just need to alter the second and third lines to match your table and
column names before you select the whole mess of code and hit [Enter]:
dim MedianTable as string, ColName as string, n as integer
MedianTable="SalesTable"
ColName="SalesCol"
Run Command "Select " & ColName & " from " & MedianTable & " Order
By " & ColName & " Into Temp NoSelect"
n=TableInfo(Temp, 8)
Run Command "Select Avg(" & ColName & ") ""Median"" from Temp
where RowID=int((n+1)/2) or RowID=int((n+2-(n mod 2))/2) into MedianFinal
NoSelect"
Browse * from MedianFinal
Undim MedianTable Undim ColName Undim n
But that's about the best you can do without putting all this into a
MapBasic tool.
Dave G.
MapInfo Technical Support
