+ Reply to Thread
Results 1 to 7 of 7

IF statement displaying the logic test as 0 when it shouldn't be.

  1. #1
    Registered User
    Join Date
    05-16-2017
    Location
    Auckland, New Zealand
    MS-Off Ver
    2016
    Posts
    3

    IF statement displaying the logic test as 0 when it shouldn't be.

    Hi there, I am having a bit of an issue with if statements surrounding equations.
    I have columns in a table which calculate percentages. I also have graphs that plot the data automatically.
    The thing is is that the graphs have been plotting the equations as 0 values. It continues to do this with conditionally formatting the cells to display nothing. So I've tried to make the cell's display 0 values as #N/A.

    The original formula is just a basic dividing equation: =B6/N12
    Which ends up displaying 0% if there is no data to work with, and will display the correct percentage when there is data available, i.e. 83.3%
    So I altered it with an IF statement to try and make 0's display #N/A which looks like this: =IF(B6/N12,0,NA())
    Now this works when the equation has no data to work with, but when there is data to calculate, the percentages displays as 0 no matter what.
    From what I can see all it should be doing is checking whether or not B6/N12 = 0, if so display #N/A. But when B6/N12 does not = 0, display the correct data. But in my case it's not working that way.

    The main issue is that my line graphs have the cells pre-set, and all the cells are equations, so when the data is displaying 0's, it will plot them. The only way to get around it that I have found is to display 0's as #N/A, which has worked with other sets of data, just not this one.

    Any support would be greatly appreciated.

    Cheers,
    - Sam
    Last edited by mrsamzor; 05-16-2017 at 03:22 PM.

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: IF statement displaying the logic test as 0 when it shouldn't be.

    Try this:

    Please Login or Register  to view this content.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    05-16-2017
    Location
    Minnesota
    MS-Off Ver
    Professional Plus 2013
    Posts
    9

    Re: IF statement displaying the logic test as 0 when it shouldn't be.

    In your File>options>Advanced scroll down to Display options for this worksheet: and you will see a check box with Show a zero in cells that have zero value. If you uncheck this, all your 0 returns will now be blank.

  4. #4
    Registered User
    Join Date
    05-16-2017
    Location
    Auckland, New Zealand
    MS-Off Ver
    2016
    Posts
    3

    Re: IF statement displaying the logic test as 0 when it shouldn't be.

    Ah so simple a fix, you are a life saver.
    Cheers!

  5. #5
    Registered User
    Join Date
    05-16-2017
    Location
    Auckland, New Zealand
    MS-Off Ver
    2016
    Posts
    3

    Re: IF statement displaying the logic test as 0 when it shouldn't be.

    Quote Originally Posted by simmy51 View Post
    In your File>options>Advanced scroll down to Display options for this worksheet: and you will see a check box with Show a zero in cells that have zero value. If you uncheck this, all your 0 returns will now be blank.
    The only problem I've found with this is that the graphs still detect the 0 value whether or not its hidden.

  6. #6
    Registered User
    Join Date
    05-16-2017
    Location
    Minnesota
    MS-Off Ver
    Professional Plus 2013
    Posts
    9

    Re: IF statement displaying the logic test as 0 when it shouldn't be.

    Quote Originally Posted by mrsamzor View Post
    The only problem I've found with this is that the graphs still detect the 0 value whether or not its hidden.
    In your graph, select ignore blanks or 0's

  7. #7
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: IF statement displaying the logic test as 0 when it shouldn't be.

    Quote Originally Posted by mrsamzor View Post
    The only problem I've found with this is that the graphs still detect the 0 value whether or not its hidden.
    That's because the zeros are still stored as a value. You'll need to store a #N/A if you want your chart to "skip" said data points. Or format them as empty cells and change your chart settings to skip hidden cells.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. IF Statement where Logic Test is a cell that contains a Vlookup
    By dcraggs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-19-2014, 07:04 AM
  2. [SOLVED] logic test AND OR
    By censura in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2013, 05:48 AM
  3. help with a logic test
    By BAC_ana in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-25-2010, 09:45 AM
  4. Logic Test??
    By WTB in forum Excel General
    Replies: 2
    Last Post: 12-16-2009, 12:27 PM
  5. [SOLVED] Logic Test ?
    By Zeno in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-11-2006, 01:45 PM
  6. logic test
    By toolman in forum Excel General
    Replies: 2
    Last Post: 10-08-2005, 09:05 AM
  7. [SOLVED] logic test
    By toolman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2005, 12:05 AM

Tags for this Thread

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