Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  4. #4
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    May 2005
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thank you, I have put that to use

  7. #7
    3 Star Lounger
    Join Date
    Nov 2004
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>
    <font color=448800><font face="Comic Sans MS"><big>Lyra J </font color=448800></font face=comic></big>
    <img src=/S/flags/UK.gif border=0 alt=UK width=30 height=18> Ducking the arrows in Robin Hood country <IMG SRC=http://www.wopr.com/w3tuserpics/Lyra_J_sig.gif ALT="No, Admins, no! I'm sorry, okay!" title="No, Admins, no! I'm sorry, okay!">

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Sometimes you have to think:

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

Posting Permissions

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