+ Reply to Thread
Results 1 to 5 of 5

Sparklines messed up by formulas returning a blank cell

  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    23

    Sparklines messed up by formulas returning a blank cell

    I have the following formula in my worksheet and I want to use a sparkline to show the trend. The formula returns a blank cell in case of an error value.

    =IFERROR(C29/(C3-C33);"")

    My sparklines go down to 0 and I don't understand why. OK, since the cell contain a formula it is not really blank but its value is not 0 either.

    I would like the sparkline to show a gap in those cases. Is there a way to make it work?

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Sparklines messed up by formulas returning a blank cell

    Perhaps replace with NA()

    =IFERROR(C29/(C3-C33),NA())
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    05-16-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Sparklines messed up by formulas returning a blank cell

    Quote Originally Posted by Ace_XL View Post
    Perhaps replace with NA()

    =IFERROR(C29/(C3-C33),NA())
    Wow, that worked. I tried to replace the blank by a character or leave the D/0! error as is but it was still considered zero. Thank you very much!

    Now I just need to find out how to hide the error display to make the file look clean.

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Sparklines messed up by formulas returning a blank cell

    Now I just need to find out how to hide the error display to make the file look clean.
    You could use conditional fomatting

    - format only cells that contain -- errors --- format white font

  5. #5
    Registered User
    Join Date
    05-16-2013
    Location
    Belgium
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Sparklines messed up by formulas returning a blank cell

    Yes, that what I was going to do, thank you. The thing is that I already have a different conditional formatting that changes the color of the fill. So I will have to do 2 different conditions:

    - if NA and the other condition is true - change fill and change font to the same color as the fill
    - if NA but the other condition not true - change font to white.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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