Worksheet function?
 

I have file with several thousand rows, and would like to average one of the
columns based on a second rows groupings. What would be the best way to do
that? Thank you.

Example: (I would like to average all the yellows and all the blues and all
the greens, etc.)

Column A Column B
Yellow 1.34
Yellow 8.2
Yellow 6.11
Blue 7.78
Blue 9.27
Blue 15.7
Green 3.5
Green 1.97
Green 6.55

Worksheet function?
 

SUMIF/COUNTIF would get you your answer. I don't believe there is an
AVERAGEIF function.

Dave
--
Brevity is the soul of wit.


"Mike" wrote:

I have file with several thousand rows, and would like to average one of the
columns based on a second rows groupings. What would be the best way to do
that? Thank you.

Example: (I would like to average all the yellows and all the blues and all
the greens, etc.)

Column A Column B
Yellow 1.34
Yellow 8.2
Yellow 6.11
Blue 7.78
Blue 9.27
Blue 15.7
Green 3.5
Green 1.97
Green 6.55

Worksheet function?
 

=AVERAGE(IF(A1:A1000="blue",B1:1000))
array formula you have to commit ctrlshiftenter (not just enter)


"Mike" wrote:

I have file with several thousand rows, and would like to average one of the
columns based on a second rows groupings. What would be the best way to do
that? Thank you.

Example: (I would like to average all the yellows and all the blues and all
the greens, etc.)

Column A Column B
Yellow 1.34
Yellow 8.2
Yellow 6.11
Blue 7.78
Blue 9.27
Blue 15.7
Green 3.5
Green 1.97
Green 6.55

Worksheet function?
 

Yes, there is AVERAGE(IF(your formula))
ctrlshiftenter

"Dave F" wrote:

SUMIF/COUNTIF would get you your answer. I don't believe there is an
AVERAGEIF function.

Dave
--
Brevity is the soul of wit.


"Mike" wrote:

I have file with several thousand rows, and would like to average one of the
columns based on a second rows groupings. What would be the best way to do
that? Thank you.

Example: (I would like to average all the yellows and all the blues and all
the greens, etc.)

Column A Column B
Yellow 1.34
Yellow 8.2
Yellow 6.11
Blue 7.78
Blue 9.27
Blue 15.7
Green 3.5
Green 1.97
Green 6.55

Worksheet function?
 

To be more specific, given the sample range you give below:

=SUMIF(A2:B10,A2,B2:B10)/COUNTIF(A2:A10,A2) will give the average for the
yellows.

Replace the criteria in the SUMIF and COUNTIF functions as necessary.
Assumes that the sample data are in range A1:B10.

Dave

--
Brevity is the soul of wit.


"Mike" wrote:

I have file with several thousand rows, and would like to average one of the
columns based on a second rows groupings. What would be the best way to do
that? Thank you.

Example: (I would like to average all the yellows and all the blues and all
the greens, etc.)

Column A Column B
Yellow 1.34
Yellow 8.2
Yellow 6.11
Blue 7.78
Blue 9.27
Blue 15.7
Green 3.5
Green 1.97
Green 6.55

Worksheet function?
 

Yeah I just saw your post. Interesting. I've always just used SUMIF/COUNTIF
for these situations.

Dave
--
Brevity is the soul of wit.


"Teethless mama" wrote:

Yes, there is AVERAGE(IF(your formula))
ctrlshiftenter

"Dave F" wrote:

SUMIF/COUNTIF would get you your answer. I don't believe there is an
AVERAGEIF function.

Dave
--
Brevity is the soul of wit.


"Mike" wrote:

I have file with several thousand rows, and would like to average one of the
columns based on a second rows groupings. What would be the best way to do
that? Thank you.

Example: (I would like to average all the yellows and all the blues and all
the greens, etc.)

Column A Column B
Yellow 1.34
Yellow 8.2
Yellow 6.11
Blue 7.78
Blue 9.27
Blue 15.7
Green 3.5
Green 1.97
Green 6.55