Using Automation (Access 97) to call MS Excel Median 2

[KMITCH]

...
Set obj = CreateObject("Excel.Application&quot
MsgBox obj.Application.Median(1,2,5,7,8)
obj.Quit
Set obj = Nothing
...
The code above works fine to return the Median of the
set of numbers hardcoded. What I really need though
is the ability to use Automation to calculate the Median
of a set of numbers in the Access database. Let's say
I had 50,000 rows worth of values to calculate the
Median on. I would like to be able to pass the recordset
to the above code somehow.

Any help appreciated.

Thanks,
Keith

 

[kathryn]

I really don't think that I would use Excel to do this. A median is the central number when the list is sorted, if the number of items is odd, and the average of the two central numbers when the number of items is even.

I would probably do something like this.

Function FindMedian() as Double

dim rst as recordset
dim intCount as integer
dim dblValue1 as double
dim dblValue2 as double

set rst = currentdb.openrecordset("NameOfQueryWhichSortsTableOnColumnWithValues&quot

rst.movelast
intCount as rst.recordcount
if intCount Mod 2 = 0 then
rst.absoluteposition = intcount/2 - 1
dbvalue1 = rst!ValueColumn
rst.movenext
dbvalue2 = rst!valuecolumn
FindMedian = (dbvalue1+dbvalue2)/2
else
rst.absoluteposition = (intcount + 1) /2
FindMedian = rst!ValueColumn
endif

rst.close
set rst = nothing

End Function

You could pass the recordse to the function, also...

Function FindMedian(rst as Recordset) as Double


You could copy your table to Excel and do the calc in Excel and return the value, but with table sizes as large as you mentioned, that could be really slow.

Hope this helps.

 

[KMITCH]

Wow! Thanks Kathryn.
This seems like a much better solution than
trying to force fit to Excel. Will try it.

-Keith