need to hide "div/0!"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jontie - 10 Oct 2007 09:39 GMT

I have this formula an if there is no data them i get div/0 how to fix
it so if no data then blank
=AVERAGE(IF(N6:N100=0,"",N6:N100))

Reply to this Message

Pete_UK - 10 Oct 2007 10:59 GMT

You could have something like:

=IF(COUNT(N6:N100)=0,"",your_formula)

Hope this helps.

Pete

> I have this formula an if there is no data them i get div/0 how to fix
> it so if no data then blank
> =AVERAGE(IF(N6:N100=0,"",N6:N100))

Reply to this Message

Stefi - 10 Oct 2007 11:00 GMT

=IF(COUNT(A6:A12)=0,"",AVERAGE(A6:A12))

Regards,
Stefi

„Jontie” ezt írta:

> I have this formula an if there is no data them i get div/0 how to fix
> it so if no data then blank
> =AVERAGE(IF(N6:N100=0,"",N6:N100))

Reply to this Message

Jontie - 10 Oct 2007 18:25 GMT

> =IF(COUNT(A6:A12)=0,"",AVERAGE(A6:A12))
>
[quoted text clipped - 8 lines]
>
> - Show quoted text -

Stefi thanks for it but when i put this formula in the cell it gave me
number in decimal place where real it should have given me 30 but the
answer was way too off the scale

Reply to this Message

Ron Rosenfeld - 10 Oct 2007 18:48 GMT

>I have this formula an if there is no data them i get div/0 how to fix
>it so if no data then blank
>=AVERAGE(IF(N6:N100=0,"",N6:N100))

Try this instead:

=IF(SUM(N6:N100)=0,"",AVERAGE(N6:N100))

entered as a normal formula
--ron

Reply to this Message

Ron Rosenfeld - 10 Oct 2007 18:49 GMT

>I have this formula an if there is no data them i get div/0 how to fix
>it so if no data then blank
>=AVERAGE(IF(N6:N100=0,"",N6:N100))

Sorry, that should have been:

=IF(COUNT(N6:N100)=0,"",AVERAGE(N6:N100))

--ron

Reply to this Message

Jontie - 11 Oct 2007 23:55 GMT

> >I have this formula an if there is no data them i get div/0 how to fix
> >it so if no data then blank
[quoted text clipped - 5 lines]
>
> --ron

.3368 is the answer i m getting after using the above formula where as
it should be 32

Reply to this Message

Ron Rosenfeld - 12 Oct 2007 01:16 GMT

>> >I have this formula an if there is no data them i get div/0 how to fix
>> >it so if no data then blank
[quoted text clipped - 8 lines]
>.3368 is the answer i m getting after using the above formula where as
>it should be 32

Then there is a problem with your interpretation of your data.

Place the following formulas on your worksheet and tell me what the results
are:

=AVERAGE(N6:N100)
=COUNT(N6:N100)
=SUM(N6:N100)

--ron

Reply to this Message

Jontie - 12 Oct 2007 03:23 GMT

> >> >I have this formula an if there is no data them i get div/0 how to fix
> >> >it so if no data then blank
[quoted text clipped - 19 lines]
>
> --ron

thank you for your help and that was a mistake on my part. sorry you
were right.

thanks for everyone who answered my questions and my template is now
processing and soon will be avaliable to download on the "microsoft
office online" under excel templates . :) :) :) with a name of
"Short Term Stock Investments
"

Reply to this Message

Ron Rosenfeld - 12 Oct 2007 03:29 GMT

>thank you for your help and that was a mistake on my part. sorry you
>were right.
[quoted text clipped - 4 lines]
>"Short Term Stock Investments
>"

Glad you got it worked out. Thanks for the feedback.
--ron

Reply to this Message

Jontie - 24 Oct 2007 20:07 GMT

> >thank you for your help and that was a mistake on my part. sorry you
> >were right.
[quoted text clipped - 7 lines]
> Glad you got it worked out. Thanks for the feedback.
> --ron

thank you all for you answers my template is up on "Microsoft Online"
webpagenow here is the handy link in case you guys want to see your
contribution to it under search term of "Swing Trading Calculator" and
link is [http://office.microsoft.com/en-us/templates/
TC300009021033.aspx?pid=CT101444811033]

Reply to this Message