pjd@magnus.acs.ohio-state.edu (Peter J Dotzauer) (05/14/91)
is it possible to tickle Quattro into calculating the median of a column
of cells. The median is obviously not among the implemented statistical
functions.
Perhaps some kind of public domain macro exists for that type of
problem?
--
Peter Dotzauer, Analyt.Cart.& GIS, Dept.of Geogr., OSU, Columbus, OH 43210-1361
TEL +1 614 292 1357 FAX +1 614 292 6213 FIDO 1:226/50 WWIVnet pjd@6450
INTERNET pjd+@osu.edu UUCP ...!osu-cis!osu.edu!pjd+ DECnet mapvxa::pjd
BITNET pjd+@ohstmail IRC Ratzer FreeNet ak061 (CLE) ac833 (YNG)userDHAL@mts.ucs.UAlberta.CA (David Halliwell) (05/16/91)
In article <1991May13.192140.19039@magnus.acs.ohio-state.edu>, pjd@magnus.acs.ohio-state.edu (Peter J Dotzauer) writes: >is it possible to tickle Quattro into calculating the median of a column >of cells. The median is obviously not among the implemented statistical >functions. > >Perhaps some kind of public domain macro exists for that type of >problem? > >-- I used to have a macro floating around that did this sort of thing, but I don't have a copy of it anymore. Basically, what you need to do is: - copy the column to a work area (unless you aren't worried about messing up the original). - sort the column (the copy) - count the number of values in the column. - if there are an odd number of values, the median is the middle one. - if there are an even number of values, average the two "middle" ones. For flexibility, you may want to set up named ranges. This is particularly handy if you want to work with a large number of variable- length data sets. You will have to use a conditional statement either in the formula for the median or in the macro, to handle the odd/even number of values. For example, you may just want to set up the median formula as the average of a range named "middle". The macro then sets "middle" to be the middle one or two values in the data set. However, it is probably easier to have an @IF median function, which uses an index into the column of data, in the form of a lookup table. Warning: named ranges that include only a single cell cannot be redefined in the same manner as multiple-cell ranges: the existing range will be A1 rather than A1..A1, and a macro containing an escape code will not have the same effect. Hope this helps! When you get the macro written, post it :-) Dave Halliwell