Average across cells with formulas
 

I am trying to average(a1:p1), in column Q. there are only numbers currently
in a1 to L1, but there are formulas in the rest that compute the numbers at
the end of each month. I want to average these colums and not have to change
the average calculation at the end of every month. I know there is a way to
tell it to average on the columns with figures but I can't seem to remember
how. Thanks

Average across cells with formulas
 

Maybe in Q1

=AVERAGE(IF(A1:P1<0,A1:P1))

It's an array so commit with Ctrl+shift+enter

Mike

"Arlene" wrote:

I am trying to average(a1:p1), in column Q. there are only numbers currently
in a1 to L1, but there are formulas in the rest that compute the numbers at
the end of each month. I want to average these colums and not have to change
the average calculation at the end of every month. I know there is a way to
tell it to average on the columns with figures but I can't seem to remember
how. Thanks

Average across cells with formulas
 

Thanks it was the array part that I was missing, how can you tell whether its
an array?

"Mike H" wrote:

Maybe in Q1

=AVERAGE(IF(A1:P1<0,A1:P1))

It's an array so commit with Ctrl+shift+enter

Mike

"Arlene" wrote:

I am trying to average(a1:p1), in column Q. there are only numbers currently
in a1 to L1, but there are formulas in the rest that compute the numbers at
the end of each month. I want to average these colums and not have to change
the average calculation at the end of every month. I know there is a way to
tell it to average on the columns with figures but I can't seem to remember
how. Thanks

Average across cells with formulas
 

If you change the formulas to default to "" when there is no information you
can use the default AVERAGE since it ignores text as opposed to a zero

otherwise you can use


=AVERAGE(IF(A2:M2<0,A2:M2))

entered with ctrl + shift & enter

adapt cell ranges to fit your requirements




--
Regards,

Peo Sjoblom



"Arlene" wrote in message
...
I am trying to average(a1:p1), in column Q. there are only numbers
currently
in a1 to L1, but there are formulas in the rest that compute the numbers
at
the end of each month. I want to average these colums and not have to
change
the average calculation at the end of every month. I know there is a way
to
tell it to average on the columns with figures but I can't seem to
remember
how. Thanks