|
Average a list with high and low ignored
In the example below, there are 8 of a possible 10 numbers. The amount
varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so:
=(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2) -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
One way:
=trimmean(a1:a10,2/count(a1:a10)) another: =(sum(a1:a10)-min(a1:a10)-max(a1:a10))/(count(a1:a10)-2) Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out -- Dave Peterson |
Average a list with high and low ignored
You could try this variation:
=AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49*pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. *The amount varies but never more than 10. *These numbers represent a value of an equation earlier on in the worksheet. *I need to be able to formulate the average of these numbers after the high and low has been removed. *In this case, 52.28 would not be used nor would 50.81. *I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. *Can anyone assist in this? *Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
thank you very much
"Gary''s Student" wrote: If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so: =(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2) -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
I wanted to ask something just for my own knowledge. Why the -2 at the end
of the formula? "Gary''s Student" wrote: If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so: =(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2) -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
Thank you Dave
"Dave Peterson" wrote: One way: =trimmean(a1:a10,2/count(a1:a10)) another: =(sum(a1:a10)-min(a1:a10)-max(a1:a10))/(count(a1:a10)-2) Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out -- Dave Peterson |
Average a list with high and low ignored
Thank you. You are correct in the range of the formula I use. I normally
would have a possible 12 but the criteria is changing to 10 and I failed to note that in the message. Good catch! "Pete_UK" wrote: You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
I didn't point out that the AVERAGE/IF formula is an array formula, so
you will have to commit with CTRL-SHIFT-ENTER. Also, I see that you've started a new thread with the same question over in .misc. Pete On Feb 6, 5:16*pm, Shu of AZ wrote: Thank you. *You are correct in the range of the formula I use. *I normally would have a possible 12 but the criteria is changing to 10 and I failed to note that in the message. *Good catch! "Pete_UK" wrote: You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: * =MAX(D4:D15) Y1: * =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. *The amount varies but never more than 10. *These numbers represent a value of an equation earlier on in the worksheet. *I need to be able to formulate the average of these numbers after the high and low has been removed. *In this case, 52.28 would not be used nor would 50.81. *I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. *Can anyone assist in this? *Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
Average a list with high and low ignored
We need the -2 because we must divide by the number of samples we are
actually using. For example, if A1 thru A10 had two blank cells, then COUNT(A1:A10) gets us down to 8 (REMEMBER: AVERAGE() ignores blanks!) We are also discarding the high & low value, so we must get down to 6. Therefore the -2 -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: I wanted to ask something just for my own knowledge. Why the -2 at the end of the formula? "Gary''s Student" wrote: If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so: =(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2) -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
I noticed that and intended to send it to worksheet functions first
"Pete_UK" wrote: I didn't point out that the AVERAGE/IF formula is an array formula, so you will have to commit with CTRL-SHIFT-ENTER. Also, I see that you've started a new thread with the same question over in .misc. Pete On Feb 6, 5:16 pm, Shu of AZ wrote: Thank you. You are correct in the range of the formula I use. I normally would have a possible 12 but the criteria is changing to 10 and I failed to note that in the message. Good catch! "Pete_UK" wrote: You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
Average a list with high and low ignored
=AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4: D15))
That won't work. AND doesn't return an array, it returns a single value, either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this case it will *always* return FALSE because at least one value will never be less than the MAX and one value will never be greater than the MIN value. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
I've run into a snag. One of my data sets contained only 2 values of the 10
possible and returned an ISERROR. I suppose it would also do that for 3 values if that was the case. "Gary''s Student" wrote: We need the -2 because we must divide by the number of samples we are actually using. For example, if A1 thru A10 had two blank cells, then COUNT(A1:A10) gets us down to 8 (REMEMBER: AVERAGE() ignores blanks!) We are also discarding the high & low value, so we must get down to 6. Therefore the -2 -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: I wanted to ask something just for my own knowledge. Why the -2 at the end of the formula? "Gary''s Student" wrote: If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so: =(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2) -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
I also had one just come in with 1 and it returned the value contained in the 1
"Shu of AZ" wrote: I've run into a snag. One of my data sets contained only 2 values of the 10 possible and returned an ISERROR. I suppose it would also do that for 3 values if that was the case. "Gary''s Student" wrote: We need the -2 because we must divide by the number of samples we are actually using. For example, if A1 thru A10 had two blank cells, then COUNT(A1:A10) gets us down to 8 (REMEMBER: AVERAGE() ignores blanks!) We are also discarding the high & low value, so we must get down to 6. Therefore the -2 -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: I wanted to ask something just for my own knowledge. Why the -2 at the end of the formula? "Gary''s Student" wrote: If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so: =(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2) -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
I found it only occurs with 2 values. Would an if statement work somehow?
"Gary''s Student" wrote: We need the -2 because we must divide by the number of samples we are actually using. For example, if A1 thru A10 had two blank cells, then COUNT(A1:A10) gets us down to 8 (REMEMBER: AVERAGE() ignores blanks!) We are also discarding the high & low value, so we must get down to 6. Therefore the -2 -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: I wanted to ask something just for my own knowledge. Why the -2 at the end of the formula? "Gary''s Student" wrote: If we have at most 10 values in A1 thru A10, AVERAGE() ignores blanks, so: =(SUM(A1:A10)-MIN(A1:A10)-MAX(A1:A10))/(COUNT(A1:A10)-2) -- Gary''s Student - gsnu200767 "Shu of AZ" wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out |
Average a list with high and low ignored
Okay, thanks for pointing that out Biff - will this do?:
=AVERAGE(IF((D4:D150)*(D4:D15<X1)*(D4:D15Y1),D4: D15)) entered with CSE. Pete On Feb 6, 5:53*pm, "T. Valko" wrote: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4: D15)) That won't work. AND doesn't return an array, it returns a single value, either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this case it will *always* return FALSE because at least one value will never be less than the MAX and one value will never be greater than the MIN value. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: * =MAX(D4:D15) Y1: * =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
Average a list with high and low ignored
That'll work.
-- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Okay, thanks for pointing that out Biff - will this do?: =AVERAGE(IF((D4:D150)*(D4:D15<X1)*(D4:D15Y1),D4: D15)) entered with CSE. Pete On Feb 6, 5:53 pm, "T. Valko" wrote: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4: D15)) That won't work. AND doesn't return an array, it returns a single value, either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this case it will *always* return FALSE because at least one value will never be less than the MAX and one value will never be greater than the MIN value. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
Average a list with high and low ignored
would you both take a look at my response to Garys answer to my question. It
seemed to work great but hits a snag when there are only 2 values. Gary might be off line and didn't see my reply. THANKS "T. Valko" wrote: That'll work. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Okay, thanks for pointing that out Biff - will this do?: =AVERAGE(IF((D4:D150)*(D4:D15<X1)*(D4:D15Y1),D4: D15)) entered with CSE. Pete On Feb 6, 5:53 pm, "T. Valko" wrote: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4: D15)) That won't work. AND doesn't return an array, it returns a single value, either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this case it will *always* return FALSE because at least one value will never be less than the MAX and one value will never be greater than the MIN value. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
Average a list with high and low ignored
would you both take a look at my response to Garys answer to my question. It
seemed to work great but hits a snag when there are only 2 values. Gary might be off line and didn't see my reply. THANKS "Pete_UK" wrote: Okay, thanks for pointing that out Biff - will this do?: =AVERAGE(IF((D4:D150)*(D4:D15<X1)*(D4:D15Y1),D4: D15)) entered with CSE. Pete On Feb 6, 5:53 pm, "T. Valko" wrote: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4: D15)) That won't work. AND doesn't return an array, it returns a single value, either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this case it will *always* return FALSE because at least one value will never be less than the MAX and one value will never be greater than the MIN value. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
Average a list with high and low ignored
In order to drop the lowest and highest values you need at least 3 numbers
in your range. Define *exactly* what should happen if: There are *no* values at all (if that's even possible) There are less than 3 values -- Biff Microsoft Excel MVP "Shu of AZ" wrote in message ... would you both take a look at my response to Garys answer to my question. It seemed to work great but hits a snag when there are only 2 values. Gary might be off line and didn't see my reply. THANKS "T. Valko" wrote: That'll work. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Okay, thanks for pointing that out Biff - will this do?: =AVERAGE(IF((D4:D150)*(D4:D15<X1)*(D4:D15Y1),D4: D15)) entered with CSE. Pete On Feb 6, 5:53 pm, "T. Valko" wrote: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4: D15)) That won't work. AND doesn't return an array, it returns a single value, either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this case it will *always* return FALSE because at least one value will never be less than the MAX and one value will never be greater than the MIN value. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
Average a list with high and low ignored
If there are two, it should average, if there is one, it should return the
value of that one "T. Valko" wrote: In order to drop the lowest and highest values you need at least 3 numbers in your range. Define *exactly* what should happen if: There are *no* values at all (if that's even possible) There are less than 3 values -- Biff Microsoft Excel MVP "Shu of AZ" wrote in message ... would you both take a look at my response to Garys answer to my question. It seemed to work great but hits a snag when there are only 2 values. Gary might be off line and didn't see my reply. THANKS "T. Valko" wrote: That'll work. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Okay, thanks for pointing that out Biff - will this do?: =AVERAGE(IF((D4:D150)*(D4:D15<X1)*(D4:D15Y1),D4: D15)) entered with CSE. Pete On Feb 6, 5:53 pm, "T. Valko" wrote: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4: D15)) That won't work. AND doesn't return an array, it returns a single value, either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this case it will *always* return FALSE because at least one value will never be less than the MAX and one value will never be greater than the MIN value. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
Average a list with high and low ignored
Try this:
=IF(COUNT(D4:D15)=0,"",IF(COUNT(D4:D15)<3,AVERAGE( D4:D15),TRIMMEAN(D4:D15,2/COUNT(D4:D15)))) -- Biff Microsoft Excel MVP "Shu of AZ" wrote in message ... If there are two, it should average, if there is one, it should return the value of that one "T. Valko" wrote: In order to drop the lowest and highest values you need at least 3 numbers in your range. Define *exactly* what should happen if: There are *no* values at all (if that's even possible) There are less than 3 values -- Biff Microsoft Excel MVP "Shu of AZ" wrote in message ... would you both take a look at my response to Garys answer to my question. It seemed to work great but hits a snag when there are only 2 values. Gary might be off line and didn't see my reply. THANKS "T. Valko" wrote: That'll work. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... Okay, thanks for pointing that out Biff - will this do?: =AVERAGE(IF((D4:D150)*(D4:D15<X1)*(D4:D15Y1),D4: D15)) entered with CSE. Pete On Feb 6, 5:53 pm, "T. Valko" wrote: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4: D15)) That won't work. AND doesn't return an array, it returns a single value, either TRUE or FALSE. So, *every* argument must evaluate to TRUE. In this case it will *always* return FALSE because at least one value will never be less than the MAX and one value will never be greater than the MIN value. -- Biff Microsoft Excel MVP "Pete_UK" wrote in message ... You could try this variation: =AVERAGE(IF(AND(D4:D150,D4:D15<X1,D4:D15Y1),D4:D 15)) whe X1: =MAX(D4:D15) Y1: =MIN(D4:D15) By the way, doesn't you range cover more than 10 numbers? Hope this helps. Pete On Feb 6, 4:49 pm, Shu of AZ wrote: In the example below, there are 8 of a possible 10 numbers. The amount varies but never more than 10. These numbers represent a value of an equation earlier on in the worksheet. I need to be able to formulate the average of these numbers after the high and low has been removed. In this case, 52.28 would not be used nor would 50.81. I currently use {=AVERAGE(IF(D4:D150,D4:D15))} to do the average of all but need to remove the high and low automatically. Can anyone assist in this? Thanks in advance. 51.32 51.54 51.74 50.93 52.28 51.07 51.85 50.81 Blank Blank = Average with the high and low taken out- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 03:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com