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)
Working out row average including zeros and negative numbers
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
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
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.
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.