+ Reply to Thread
Results 1 to 4 of 4

Formula shows junk value when there are no values in the cells

  1. #1
    Registered User
    Join Date
    03-19-2010
    Location
    Bangalore. India
    MS-Off Ver
    Excel 2007
    Posts
    10

    Formula shows junk value when there are no values in the cells

    Hi,

    i have put a formula in the cell... =C3*100/B3( which would cal %) but when there is no values in the cell it shows... #DIV/0!. Which shouldn't appear.

    How can we make it show blank when there are no values for the cells?

    Thanks in Advance,
    Shreesh.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula shows junk value when there are no values in the cells

    If you only want the calculation to be done when both B3 and C3 have values try changing to

    =IF(COUNT(B3,C3)=2,C3*100/B3,")

  3. #3
    Registered User
    Join Date
    03-19-2010
    Location
    Bangalore. India
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Formula shows junk value when there are no values in the cells

    Hi,

    Now I have put the following formula....=IF(COUNT(B4,C4)=0,"",C4*100/B4). But here also if B4 & C4 has values it returns right value, but if B4 doesnt have value it gives #DIV/0!.

    Pls advice how to overcome this?

    Regards,
    Shreesh.

  4. #4
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Formula shows junk value when there are no values in the cells

    Have another look at DaddyLongLegs formula.

    If you do =IF(COUNT(B4,C4)=0,"", ... then your formula will be calculated if only one of the values is present. If that value happens to be C4 and B4 is empty, you'll still end up with Div/0.

    You need to make sure you only do the calculation when both cells are filled. So ...

    Have another look at DaddyLongLegs formula.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1