[2003] Averaging a bunch of cells

[Bob Flaminio]

I have a spreadsheet of this form:

A 1
B 2
A 5
C 3
C 4
B 2
A 3
B 4

I need a function that would return the average of all cells of a given
label. For example, if the desired label is "A", the function should
return 3 [(1+5+3)/3].

Thanks in advance...

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(A1:A100="A",B1:B100,FALSE))

 

[Don Guillett]

try this ARRAY formula which must be entered/edited with control+shift+enter
CSE
=AVERAGE(IF(H1:H8="a",I1:I8))

 

[Trevor Shuttleworth]

Bob

=SUMIF(A:A,"A",B:B)/COUNTIF(A:A,"A") = 3
=SUMIF(A:A,"B",B:B)/COUNTIF(A:A,"B") = 2.666667
=SUMIF(A:A,"C",B:B)/COUNTIF(A:A,"C") = 3.5

You can change the constant "A" (or "B" or "C") to a cell reference

Regards

Trevor

 

[Bill Kuunders]

=(SUM(IF(A1:A8=C1,B1:B8,0)))/COUNTIF(A1:A8,C1)
entered as an array function
hold cntrl and shift buttons when pushing enter button

c1 is the cell with A

Regards
Bill K

 

[Bob Flaminio]

Thanks for the quick response -- worked like a champ!

-Bob

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(A1:A100="A",B1:B100,FALSE))

I have a spreadsheet of this form:

A 1
B 2
A 5
C 3
C 4
B 2
A 3
B 4

I need a function that would return the average of all cells of a
given label. For example, if the desired label is "A", the function
should return 3 [(1+5+3)/3].

Thanks in advance...