Average excluding high/low

G

Guest

I have a cross-tab query that lists the percentage of possible points per
person per month. I want to create a report that shows the average
percentage per year (year-to-date) MINUS the highest and lowest. Can anyone
tell me how this can be done -- if at all?

I already display the avg per quarter but I don't know how to exclude the
high/low values for the year-to-date.

Here is a sampling of the data produced byt the crosstab query:
Month Rep1 Rep2 Rep3 Rep4 Rep5 Rep6
Jan 0.888 1 0.733 0.4 0.736 1
Feb 0.916 1 0.8 0.666 0.778 1
Mar 0.727 1 0.615 0.846 0.72 1
Apr 0.968 1 0.88 0.833 0.821 1
May 1 0.875 0.773 0.579 0.868 1

Thanks!
 
G

Guest

You can add a text box in your Year or Report Footer with a control source
like:
=(Sum([Rep1])-Max([Rep1])-Min([Rep1]))/(Count([Rep1])-2)
 
G

Guest

So simple, yet I just couldn't wrap my head around it by myself!!
Works like a charm!
Thanks!!

Duane Hookom said:
You can add a text box in your Year or Report Footer with a control source
like:
=(Sum([Rep1])-Max([Rep1])-Min([Rep1]))/(Count([Rep1])-2)
--
Duane Hookom
Microsoft Access MVP


Molasses26 said:
I have a cross-tab query that lists the percentage of possible points per
person per month. I want to create a report that shows the average
percentage per year (year-to-date) MINUS the highest and lowest. Can anyone
tell me how this can be done -- if at all?

I already display the avg per quarter but I don't know how to exclude the
high/low values for the year-to-date.

Here is a sampling of the data produced byt the crosstab query:
Month Rep1 Rep2 Rep3 Rep4 Rep5 Rep6
Jan 0.888 1 0.733 0.4 0.736 1
Feb 0.916 1 0.8 0.666 0.778 1
Mar 0.727 1 0.615 0.846 0.72 1
Apr 0.968 1 0.88 0.833 0.821 1
May 1 0.875 0.773 0.579 0.868 1

Thanks!
Click to expand...
Click to expand...
 
Top