AVERAGEIF
 

I have a worksheet like:
162-1
162-2
162-3
163-1
163-2
..........
I 'd like to average the group of cells with the same first three digit? I
am thinking about AVERAGEIF function but could not work out? Any help would
appreciate?
Thank you!
Tung

AVERAGEIF
 

Hi,

Assuming that your data below is in range C6:C10, you can use the following
formula where B14 has 162.

=SUMPRODUCT((1*(LEFT(C6:C10,3))=B14)*(RIGHT(C6:C10 ,1)))/SUMPRODUCT(1*(1*(LEFT(C6:C10,3))=B14))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tung Nguyen" <Tung wrote in message
...
I have a worksheet like:
162-1
162-2
162-3
163-1
163-2
.........
I 'd like to average the group of cells with the same first three digit? I
am thinking about AVERAGEIF function but could not work out? Any help
would
appreciate?
Thank you!
Tung

AVERAGEIF
 

Assuming that A2:A6 contains the data, C2 contains 162, and C3 contains
163, try...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=AVERAGE(IF(LEFT($A$2:$A$6,LEN(C2))=C2,$A$2:$A$6))

Hope this helps!

http://www.xl-central.com

In article ,
Tung Nguyen <Tung wrote:

I have a worksheet like:
162-1
162-2
162-3
163-1
163-2
.........
I 'd like to average the group of cells with the same first three digit? I
am thinking about AVERAGEIF function but could not work out? Any help would
appreciate?
Thank you!
Tung

AVERAGEIF
 

Misunderstood.... Maybe this is what you're looking for...

=AVERAGE(IF(LEFT($A$2:$A$6,LEN(C2))+0=C2,RIGHT($A$ 2:$A$6,1)+0))

or

=AVERAGE(IF($A$2:$A$6<"",IF(LEFT($A$2:$A$6,LEN(C2 ))+0=C2,RIGHT($A$2:$A$6
,1)+0)))

The latter allows for empty/blank cells. Note that both formulas need
to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

http://www.xl-central.com

In article ,
Domenic wrote:

Assuming that A2:A6 contains the data, C2 contains 162, and C3 contains
163, try...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=AVERAGE(IF(LEFT($A$2:$A$6,LEN(C2))=C2,$A$2:$A$6))

Hope this helps!

http://www.xl-central.com

In article ,
Tung Nguyen <Tung wrote:

I have a worksheet like:
162-1
162-2
162-3
163-1
163-2
.........
I 'd like to average the group of cells with the same first three digit? I
am thinking about AVERAGEIF function but could not work out? Any help would
appreciate?
Thank you!
Tung