Average

[Alex Hammerstein]

Hi I have to get an average for figures in column. At present I am using
the Average function. Is there a way that I can ignore cells with no data
in them?

Thanks

 

[CyberTaz]

The AVERAGE() fx *does* ignore empty cells & cells containing text.

 

[Alex Hammerstein]

That's what I would have assumed, but:

E F
31 2 00:16:23
32 2 00:49:08
33 -
34 -

E31 = =AVERAGE(F32:F34)
I have cleared the contents in F33 and F34, but as you can see F31 seems to
be 1/3rd of Column F whereas given that F33 and F34 are empty, I would have
expected F31 to be 00:49:08

Have I got something wrong?

Thanks

 

[Alex Hammerstein]

Hi

Sorry its sorted.

Thanks

Alex

That's what I would have assumed, but:

E F
31 2 00:16:23
32 2 00:49:08
33 -
34 -

E31 = =AVERAGE(F32:F34)
I have cleared the contents in F33 and F34, but as you can see F31 seems to
be 1/3rd of Column F whereas given that F33 and F34 are empty, I would have
expected F31 to be 00:49:08

Have I got something wrong?

Thanks

 

[Bob Greenblatt]

That's what I would have assumed, but:

E F
31 2 00:16:23
32 2 00:49:08
33 -
34 -

E31 = =AVERAGE(F32:F34)
I have cleared the contents in F33 and F34, but as you can see F31 seems to
be 1/3rd of Column F whereas given that F33 and F34 are empty, I would have
expected F31 to be 00:49:08

Have I got something wrong?

Thanks

How did you "clear" F33 and F34? This works fine for me. Did you use the
delete key? Are you sure Excel is fully up to date?

 

[CyberTaz]

Yeah, like Bob G says, it seems to work fine here. Do you have all cells
involved formatted as Time? I can repro your result if the "empty" cells
actually contain zeros -- 0 is a value that gets included in the averaging.

HTH |:>)
Bob Jones
[MVP] Office:Mac