AVERAGE of 98 cells

[laura.smith]

I am trying to average 98 cells. Here is what my data looks like:
Min
Max
Average

Min
Max
Average

I want to average all 98 mins. Then average all 98 maxs. Then averag
all 98 averages. (This is salary information). So my questions ar
this. Is there a fast way to grab, for example, all 98 mins withou
having to click on them each individually and two I tried to averag
all of them and got an error. It said I could only average 30 numbers
Please help! Thanks

 

[Bernie Deitrick]

Laura,

If you have those labels in column A, and your data in column B, then you
could use

=SUMIF(A:A,"Min",B:B)/COUNTIF(A:A,"Min")

Similar formulas for averages of Max and Average should be readily apparent.

HTH,
Bernie
MS Excel MVP

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A1000="Min"),B1:B1000)/COUNTIF(A1:A1000,"Min")

See

http://www.mcgimpsey.com/excel/doubleneg.html

for an explanation of the use of "--" in SUMPRODUCT() formulae.

 

[Frank Kabel]

Hi
So you have 3 rows of data and then a blank row?. If yes try the
following array formula (entered with CTRL+SHIFT+ENTER):
=AVERAGE(IF(MOD(ROW(A1:A300)-1,4)=0,A1:A300))

to geth the AVERAGE for all MIN figures

 

[GerryK]

I think the last part of the formula should reference the
B column data range. I tried to use it the other way and
it gave me 0.

 

[Frank Kabel]

Hi
if your data is in column B, yes then use B1:B300