Average across cells with formulas

A

Arlene

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
 
M

Mike H

Maybe in Q1

=AVERAGE(IF(A1:p1<>0,A1:p1))

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

Mike
 
A

Arlene

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

Peo Sjoblom

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
 
Top