Error: #DIV/0!

[Khalil Handal]

Hi all,
In cell J14 I have the formula =AVERAGE(H14:I14).
When nothing is in the cells H14, I14 I have the message #DIV/0, and since
the colomn width is small I see the ### signs.

How do I need to modify the formula in cell J14 so that nothing is seen
inside it (empty) when cells H14 and I14 has nothing?

Logicaly: IF H14="" and I14=" then J14="" else J14=AVERAGE(H14:I14)

How can I write this in Excel? (if it is correct).

Help is realy appriciated.'

Khalil

 

[daffy333]

I believe that the solution is

=if(iserr(AVERAGE(H14:I14)),"",AVERAGE(H14:I14)))

See if that works.

Greg

 

[Bob Phillips]

=IF(SUM(H1:H14),AVERAGE(H1:H14),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dave Peterson]

One more:

=IF(count(H1:H14)=0,"No Numbers",Average(H1:H14))

 

[Sandy Mann]

Hi Bob,

Nit picking but wouldn't that return an empty string in the rare event that
SUM(H1:H14) was zero?

Surely it would be better with COUNT()

=IF(COUNT(H1:H14)>0,AVERAGE(H1:H14),"")

although the OP was talking about H14 & I14 so

=IF(COUNT(H14:I14)>0,AVERAGE(H14:I14))

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Khalil Handal]

It worked just fine, Greg, Thank you.
The other postings include the cells from row ONE which I don't need. Only
the cells in the same row cell I and cell H.

Thanks to all of you.

 

[Khalil Handal]

Hi Again,

What changes do I need to do if the range H14:I14 is in another sheet named
sheet1

Khalil

 

[Harlan Grove]

Nit picking but wouldn't that return an empty string in the rare event that
SUM(H1:H14) was zero?

Who says that's a rare event?

Surely it would be better with COUNT()

=IF(COUNT(H1:H14)>0,AVERAGE(H1:H14),"")

....

No need for the >0 test. COUNT(..) alone is sufficient.

 

[Bob Phillips]

Not nit picking at all, it is a better test.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

=IF(COUNT(Sheet1!H14:I14),AVERAGE(Sheet1!H14:I14))


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Khalil Handal]

Hi,
You sujjestion gives the word FALSE in the cell; What adjustments is needed
to so as to have the cell empty? ie. nothing is shown inside it.

 

[Khalil Handal]

This works well for me. It solved my problem.
Thank you all

Khalil

 

[Bob Phillips]

Sorry, I missed the other action

=IF(COUNT(Sheet1!H14:I14),AVERAGE(Sheet1!H14:I14),"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)