How to omit certain cells in formula

[Klemen25]

Hello

If I have monthly data in a table, but data form April on is not
available yet, but I would like to be inputted automatically from some
other table when it becomes available there. So I put in the cell a
formula =DATA!I49.

And as the source is empty so far- I get the result of the formula
'0'.

But at the end of this line (that now includes data till March and for
the next 9 months there are 0) I have formula for average- which by
now also takes in to account the 0 in the line- which I do not want.

What would be the best way to tell the excel that: calculate average
in the line for those cells that are not equal 0?

Thank you very much- it is not life or death situation of course, but
I would like to crack this...
Best regards

 

[Roger Govier]

Hi

One way
=SUM(A1:M1)/COUNTIF(A1:M1,">0")

 

[Klemen25]

Thank you very much...
the formula =SUM(A1:M1)/COUNTIF(A1:M1,">0") works.
But I forgot to say (stupid me!- but I also didn't see it till now)
that I also have one negative number..
I would like to change this to: calculate average in the line for
those cells that are higher or lower of 0?

 

[Roger Govier]

Hi

Try
=SUM(A1:M1)/COUNTIF(A1:M1,"<>0")

 

[Klemen25]

Works..
Thank you and all the best!
Klemen

 

[Gord Dibben]

Alternate route.

Get rid of the zeros.

=IF(Data!I49="","",Data!I49)


Gord Dibben MS Excel MVP

 

[Don Guillett]

An array formula that must be entered using ctrl+shift+enter
=AVERAGE(IF(a1:m1>0,a1:m1))

 

[Roger Govier]

It often helps to tackle the problem from the other direction.
Much simpler Gord.

 

[Gord Dibben]

Thanks

But........the negatives had to be taken care of also so OP needed more than
than one solution.

We got it covered<g>