Average

[CuriousGeorge2]

Is there a function that average's the number's in a column and ignore zero's? I am averageing a column that has formula's in it.
The columnI am averaging add two other columns. Is it possible to make those colums return no valu instead of a zero. I'm sure the answer is starring me in the face. It has now become my life's mission to figure this silly little thing out.
Thank you to anyone who can help.

 

[carrr]

AVERAGEA ignores zeroes

 

[carrr]

....whoops, misspoke...empty cells get ignored, zeroes get counted

 

[carrr]

If you're summing the two columns and they have no zeroes, only blank spaces, you can go to Tools, Options, View, and uncheck Zero values under Window Options. The sum of blank cells will show as a blank, not a zero, and DAVERAGE should work....(this is, of course, assuming no zeroes to start with)

 

[Zathras]

Try the SUBTOTALS function with function_number 1.

 

[CuriousGeorge2]

I must still be doing something wrong because when I do that the summ of the two cells still has the value of zero the cell shows [-]. It is driving me crazy I can't be the only person.

 

[Zathras]

Please post EXACTLY what is in the two cells and what you are entering as the formula.

 

[CuriousGeorge2]

the cells that I am averaging have =SUM(E33,F33). then i have to average those cells

 

[Zathras]

If it is not too much trouble to ask, what perchance do you have in the location identified as "E33".

Similarly (I really hate to bother you) what is it that resides so comfortably in the cell known as "F33"?

Finally, (sorry to be such a pest), but if you could indicate with a little more detail what is the contents of the cell where "the summ (sic) of the two cells still has the value of zero the cell shows [-]" I would be most grateful.

 

[CuriousGeorge2]

I apologize that my entire post was not on the site. In cell E33 I have nothing. In Cell F33 I also have nothing. The cell that has a formula to add them returns zero. The Workbook that has these cells tracks Days of the month for employees. On the days the employees are off Cell E33 and Cell F33 do not have values entered into them. Therefore the cell that adds them returns zero. The e cell that then averages those totals counts those zeros and drags down the average.
Any help would be greatly appreciated.

Thank you,
Rebecca Ann

 

[xlbo]

Surely this is a case for:

=sum(Average_Range)/count(Average_Range)

from your description, it sounds like you have a column (lets say G for now) that has =SUM(E33,F33) in it
Presumably also, you have more of these cells in col G (say from G2:G50 for the sake of argument)

In that case, you should be able to get the average by using
=sum(G2:G50)/count(G2:G50)

HTH Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[bandit600]

I have tried what xlbo suggests, but the count part still counts the zeros.
If you replace count(G2:G50) with countif(G2:G50,">0&quot then I believe this will do the trick.

bandit600

 

[CuriousGeorge2]

Thank you xlbo & bandit600,

Thank you for all of your help. The combination worked. And thank you for showing me not everyone is as sarcastic as Zathras.

 

[Zathras]

Please accept my apologies. It was late and I was tired, but that is no excuse for bad manners.

 

[xlbo]

I dunno - I had a little chuckle at it Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Zathras]

Geoff: As I'm sure you have experienced, it is very frustrating to ask for additional information and get a response that provides absolutely nothing useful. Thanks for the support.