Working out row average including zeros and negative numbers

[conorm]

Hi, I'm trying to figure out how to find the average of a row of data, but whatever I do it seems to exclude something, can anyone help. I obviously want to skip nay cells that are blank but include any zeros as they are actual results that will rightly effect the average.

Here is an example example line of data:

4, -6, 0, 10, blank cell, -3

So in this example it should be (4-6+0+10-3)/5=1 but, whatever I seem to do it either excludes '0' or doesn't count negative numbers, any ideas?

I've looked around the internet but can;t find any solutions that work...

First i tried =AVERAGE(A1:X1)
then: =SUM(A1:X1)/COUNTIF(A1:X1,"<>=0")
then: =SUM(A1:X1)/COUNTIF(A1:X1, "<>"&0)

[PeterLe]

Hi
You could use a command such as =COUNTIF(E6:J6,"") as an example...what that would do is return the number of times that a blank appears (not a zero)..then deduct that fig from the sum total number of cells the you are counting (lets say x)
Then sum the total or all the cells and divide by x
Would that work?
Regards
Peter

[nigelk]

If your totals are in columns try:

=SUM(COUNTA(A:A))/COUNTA(A:A)

If in rows:

=SUM(COUNTA(10:10))/COUNTA(10:10)

Change the references where necessary, but obviously, don't have the formula in the same column/row as your numbers.

Cheers,
Nigel.

[conorm]

Neither of these seem to be working, all I want to do is the same function as =average(A1:A100) for example but for a row instead of a column and this seems to confuse excel?

[TheTub]

The AVERAGE function will work across rows and columns and ignore blank cells.

From Microsoft:

If a range or cell reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

When I use your data I get the result you expect which is 1.

Check that none of your values are being treated as text.

[conorm]

Hi tub, thanks, I don;t know why it's not working it just says DIV/0!

How do you make sure it's reading it as a number not text?

I'm doing it in excel in google docs, do you think this might be the problem?