[2003] Averaging a bunch of cells

  • Thread starter Thread starter Bob Flaminio
  • Start date Start date
B

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...
 
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(A1:A100="A",B1:B100,FALSE))
 
try this ARRAY formula which must be entered/edited with control+shift+enter
CSE
=AVERAGE(IF(H1:H8="a",I1:I8))
 
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
 
=(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
 
Thanks for the quick response -- worked like a champ!

-Bob


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

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


Bob Flaminio said:
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...
Click to expand...
Click to expand...
 
Back
Top