Average across cells with formulas

[Guest]

I am trying to average(a11), 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

 

[Guest]

Maybe in Q1

=AVERAGE(IF(A11<>0,A11))

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

Mike

 

[Guest]

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

 

[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