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

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

=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

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

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

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