Thread: Formula but if no vaslue print a 0 (excel)

1. Formula but if no vaslue print a 0 (excel)

I have this formula =(Y4/0.001+(C4*9.81))/C4 which i drag down and it figures out a percentage. some of the cells that the formula needs have no values. So then the answer the formula comes up with gives ####, is there a way to print a 0 instead if the formula finds no value in the cells needed?

I think you kinda do something like this =(Y4/0.001+(C4*9.81))/C4 == "" then "0"

2. Re: Formula but if no vaslue print a 0 (excel)

Hi Jazzy and welcome to the Lounge

Can we have a little more information? Either attach the worksheet to your next post, or tell us whether it's the 'C4' part that is dragged, or the 'Y4'. What exactly is it you're trying to do?

3. Re: Formula but if no vaslue print a 0 (excel)

I have a attached an example, it should make things easier to understand

4. Re: Formula but if no vaslue print a 0 (excel)

You could always use something along the lines of

=IF(ISERR((Y4/0.001+(C4*9.81)/C4)),0,(Y4/0.001+(C4*9.81))/C4)

and just amend the formula to suit your needs. If the formula returns a 'real' number , then that's displayed otherwise a zero is returned

HTH

5. Re: Formula but if no vaslue print a 0 (excel)

If you want to return a null string (blank cell) which is really the correct answer since division by zero is undefined not zero, then use something like this:

<pre>=IF(ISERROR(((Y4/0.001+(C4*9.81))/C4)),"",((Y4/0.001+(C4*9.81))/C4))
</pre>

If you really want to return zero, then use this:

<pre>=IF(ISERROR(((Y4/0.001+(C4*9.81))/C4)),"0",((Y4/0.001+(C4*9.81))/C4))
</pre>

6. Re: Formula but if no vaslue print a 0 (excel)

Thank you, I have put that to use

7. Re: Formula but if no vaslue print a 0 (excel)

Yeah, thanks Legare. I should have included the NULL formula too, but was just following Jazzy's request for a zero.

At least now he gets a choice <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

8. Re: Formula but if no vaslue print a 0 (excel)

Sometimes you have to think:

"Do what I want, not what I say."

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•