Function to Compute Average without High and Low ValuesLast reviewed: June 3, 1998 |
2.X 3.00 4.00 4.00a 5.00 | 2.20 2.21 3.00 WINDOWS | OS/2 kbusage The information in this article applies to:
SUMMARYThe following is a worksheet function to provide an average of a range without the highest or lowest values: =( SUM(range)-MAX(range)-MIN(range) ) / (COUNT(range)-2) Note that the parentheses are critical so that the function is computed in the right order. The following is the same function as above, incorporated into a function macro: A1: HILOAVERAGE A2: =RESULT(1) A3: =ARGUMENT("range",8) A4: =(SUM(range)-MAX(range)-MIN(range))/(COUNT(range)-2) A5: =RETURN(A4) Note: You must define the macro on the macro sheet, then use a full pathname to the macro sheet on the worksheet. For example: =Macro1.xls!HILOAVERAGRE(A1:A8) By choosing Formula Paste Function, you can paste this function in automatically. |
KBCategory: kbusage |
Last reviewed: June 3, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.