Average a criteried range

[Trogg]

Hi all,

I need to take a column of numbers (for example use 1-20) and find the numbers that are (for example sake)>10 and <15 and then give me the average of that extract. The answer in this example would be 12.5 ... I can't seem to get the formula right. Thanks in advance...

 

[SkipVought]

Hi,

Use the SUMIF and COUNTIF functions.

Skip,

_[red][/red]

 

[Trogg]

Can you be more specific... I can't seem get both crtieria (greater than and less than)in the formula. One or the other works but not both in the same formula.

 

[SkipVought]

You're going to have to do your >10 and < 15 criteria in separate statements

Skip,

_[red][/red]

 

[dallen43]

I'm just curious. Would you use a command like this?

=(SUMIF(A1:A22,"<15",A1:A22) - SUMIF(A1:A22,"<11",A1:A22))/(COUNTIF(A1:A22,"<15") - COUNTIF(A1:A22,"<11"))

Is is there a simpler way?

Deb

 

[SkipVought]

<15 and >10
[tt]
=(SUMIF(A1:A22,"<15",A1:A22) - SUMIF(A1:A22,">10",A1:A22))/(COUNTIF(A1:A22,"<15") - COUNTIF(A1:A22,">10"))
[/tt]
or
[tt]
=sumproduct((A1:A22<15)*(A1:A22>10)*(A1:A22))/sumproduct((A1:A22<15)*(A1:A22>10)*(1))
[/tt]



Skip,

_[red][/red]

 

[dallen43]

In my test cells A1:A22 are 1 thru 22 respectively.
I assume that > 10 and < 15 should give the average of 11, 12, 13, and 14. That would be 12.5.

The sumproduct formula give 12.5 correctly.

The sumif/countif formula gives -46.5.

My sumif/countif formula provides the correct answer, but it isn't as nice as your sumproduct formula.

Deb

 

[Trogg]

Cudos to you both!! Both solutions worked. I was trying to do the comparison in one statement which obviously would not work. The boss is happy again!