Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
I have a column of values that represent job completion times (i.e. 8:45,
9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
Hi,
'This is an array formula which must be entered with CTRL+Shift+Enter and NOT 'just enter. If you do it correct then Excel will put curly brackets around 'the formula{}. You can't type these yourself. If you Edit the ranges 'then you must re-enter as An array Mike "Paul" wrote: I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
If there are no blanks in C3:C26, then:
=(SUM(C3:C26)-MAX(C3:C26)-MIN(C3:C26))/22 Note that the {} are signs for an array formula. The formula above is a normal formula, not an array formula. -- Gary''s Student - gsnu200817 "Paul" wrote: I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
Paul wrote:
I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. You got an answer for (a). As for (b), you could go with something like this, which is NOT an array formula: =(SUM(C3:C26)-MAX(C3:C26)-MIN(C3:C26))/(COUNT(C3:C26)-2) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
=TRIMMEAN(C3:C26,2/COUNT(C3:C26))
"Paul" wrote: I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
If you only want to exclude one instance each of the max and the min:
=TRIMMEAN(C3:C26,2/COUNT(C3:C26)) For example: 3,3,4,5,6,7,7 The above formula will exclude one 3 and one 7. -- Biff Microsoft Excel MVP "Paul" wrote in message ... I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
Teethless mama wrote:
=TRIMMEAN(C3:C26,2/COUNT(C3:C26)) Cool! Never heard of that one. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
I guess I should've include a method that excludes *all* instances of min
and max. Array entered** : =AVERAGE(IF((C3:C26MIN(C3:C26))*(C3:C26<MAX(C3:C2 6)),C3:C26)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... If you only want to exclude one instance each of the max and the min: =TRIMMEAN(C3:C26,2/COUNT(C3:C26)) For example: 3,3,4,5,6,7,7 The above formula will exclude one 3 and one 7. -- Biff Microsoft Excel MVP "Paul" wrote in message ... I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
Sorry for the late response. Interesting function. If I understand it
correctly, I would have to have the data sorted in ascending or descending order for the trim to remove the worst and best times. But the data is in Date order (process date). Nice point to calculate the correct % to remove only the top and bottom records. Thanks. "Teethless mama" wrote: =TRIMMEAN(C3:C26,2/COUNT(C3:C26)) "Paul" wrote: I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
This works great and is fairly easy to understand. Thanks.
"Glenn" wrote: Paul wrote: I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. You got an answer for (a). As for (b), you could go with something like this, which is NOT an array formula: =(SUM(C3:C26)-MAX(C3:C26)-MIN(C3:C26))/(COUNT(C3:C26)-2) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
Again, sorry for my late response (notification email got buried). As I
mentioned above, I think this requires that my list be sorted from max to min or vice versa. It is however in date order so I'm not sure this function would work in my situation. Thanks. "T. Valko" wrote: I guess I should've include a method that excludes *all* instances of min and max. Array entered** : =AVERAGE(IF((C3:C26MIN(C3:C26))*(C3:C26<MAX(C3:C2 6)),C3:C26)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... If you only want to exclude one instance each of the max and the min: =TRIMMEAN(C3:C26,2/COUNT(C3:C26)) For example: 3,3,4,5,6,7,7 The above formula will exclude one 3 and one 7. -- Biff Microsoft Excel MVP "Paul" wrote in message ... I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average exluding max and min values
The data does not need to be sorted.
There's only one way to tell if it works! A1 = 55 A2 = 27 A3 = 99 A4 = 10 A5 = 50 Average excluding 99 and 10: =TRIMMEAN(A1:A5,2/COUNT(A1:A5)) -- Biff Microsoft Excel MVP "Paul" wrote in message ... Again, sorry for my late response (notification email got buried). As I mentioned above, I think this requires that my list be sorted from max to min or vice versa. It is however in date order so I'm not sure this function would work in my situation. Thanks. "T. Valko" wrote: I guess I should've include a method that excludes *all* instances of min and max. Array entered** : =AVERAGE(IF((C3:C26MIN(C3:C26))*(C3:C26<MAX(C3:C2 6)),C3:C26)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... If you only want to exclude one instance each of the max and the min: =TRIMMEAN(C3:C26,2/COUNT(C3:C26)) For example: 3,3,4,5,6,7,7 The above formula will exclude one 3 and one 7. -- Biff Microsoft Excel MVP "Paul" wrote in message ... I have a column of values that represent job completion times (i.e. 8:45, 9:26, etc). I am trying to calculate the average completion time excluding the best and worst times (max and min values). Not sure now where I got this original formula but it works: =AVERAGE(IF(C3:C26<MAX(C3:C26),IF(C3:C26<MIN(C3: C26),IF(C3:C26<"",C3:C26,"")))) The problem seems to be in how the formula gets captured in the cell. I can see that for those cells where it returns a valid value, when I click on the cell (but am not editing yet), the formula bar shows the formula bounded by braces { }. When you edit, they are not visible. For those cells where I do not see the braces, the formula returns #VALUE!. However, even in these instances, if you edit the cell and use the formula button (fx), the dialogue box returns the correct final result. Is the (a) some special way to enter the braces (if I type in the { } myself it makes the cell text and doesn't evaluate the formula)? (b) a better formula? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
exluding missing data values from equation | Excel Worksheet Functions | |||
average values in non-contiguous cells, ignoring 0 values | Excel Worksheet Functions | |||
SUMIF exluding text | Excel Worksheet Functions | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions |