Help with calculating a Mean Average

[Andrew Bibby]

I am trying to develop a cross-tab query that calculates the average cost of
different prescribed high-cost drugs for paediatric and adult patients which
I have achieved. However as a small proportion of the data subjects are
either prescribed a very high or very low dose of the drugs proportionate to
their weight I would like to exclude them from the calculation.

In MS Excel I would have achieved this by using the formula =trimmean(array,
x%) Is there a way of achieving this in MS Access??

TIA

 

[Michel Walsh]

HI,



Build the crosstab on a query (rather than directly on the table) and have
that query removing the tails of the distribution:


SELECT TOP 90 PERCENT *
FROM originalTable
ORDER BY ABS(f2-(SELECT AVG(f2) FROM originalTable)) ASC


If there is more than one drug, another technique is probably preferable.

Hoping it may help,
Vanderghast, Access MVP