average
 

I have a spreadsheet and in one column I have results from time. I am trying
to average this column and if I choose just the cells that have results so
far, I get the correct average, but in my formula if I take the ending cell
down so I don't have to change the formula each time, I get an average based
on all the empty cells which makes the answer incorrect.
This is my formula: =AVERAGE(IF(AG6:AG11342<0,AG6:AG11342,""))
My worksheet is only down to AG838, but I need it expanded for more data
entry, but my average is showing 24 and if I change the AG11342 to AG838, I
get the correct average of 16. Please help

average
 

Pammy,

Average is 'smart' enough to ignore empty cells so providing the formula you
posted is array entered (CTRL+Shift+Enter) then it should give the correct
answer for the larger range.

If is doesn't then I suggest you check all of your numbers are really
numbers and not text that look like numbers

Mike

"Pammy" wrote:

I have a spreadsheet and in one column I have results from time. I am trying
to average this column and if I choose just the cells that have results so
far, I get the correct average, but in my formula if I take the ending cell
down so I don't have to change the formula each time, I get an average based
on all the empty cells which makes the answer incorrect.
This is my formula: =AVERAGE(IF(AG6:AG11342<0,AG6:AG11342,""))
My worksheet is only down to AG838, but I need it expanded for more data
entry, but my average is showing 24 and if I change the AG11342 to AG838, I
get the correct average of 16. Please help