+ Reply to Thread
Results 1 to 7 of 7

Chart value drops to zero when no value in a cell.

  1. #1
    Registered User
    Join Date
    01-27-2014
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    26

    Chart value drops to zero when no value in a cell.

    Colleagues:
    I need your help on an Excel 2013 charting issue. It gives with one hand but, takes with the other.
    I have an Excel 2013 chart which drops values to zero where there is no data displayed in a particular cell, for the current year. One of your solved threads mentions scatter charts to solve the issue. However, this didn't work with my issue.
    To graphically describe this; I've attached a desensitized spreadsheet, to illustrate the issue.
    Many thanks in advance for any help your are able to offer.
    David2020.
    Attached Files Attached Files

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Chart value drops to zero when no value in a cell.

    The issue is the actual data used in the chart if a zero.

    You can modify the formula in the intermediate data table you have to check user inputted value actually exists. If not output #N/A.
    The chart will then be fed with #N/A when no data is present and the line will not drop at the end.

    You can revert to using a line chart as the periods are categories.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    01-27-2014
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    26

    Re: Chart value drops to zero when no value in a cell.

    Andy:
    I wasn't sure what you meant by the #N/A. I tried it in my formulas on the 2nd Tab but, I was unable to resolve my issue.
    I couldn't find a way of sending a second updated attachment?
    Many thanks for any help you are able to provide.
    Regards
    david2020.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Chart value drops to zero when no value in a cell.

    the formula would be NA(), which displays the result #N/A when used

    Did you check the attachment I posted which includes the revised formula?

  5. #5
    Registered User
    Join Date
    01-27-2014
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    26

    Re: Chart value drops to zero when no value in a cell.

    Andy:
    I didn't check the amended attachment but, I have now. The graph doesn't drop to zero values as you say, with the new formulas in the table C3 to O6.
    However, my 5 check boxes no longer work, to display the year comparisons from the data in Cell range B11 to O16.
    Any ideas where I'm going wrong ? Your continuing help and support is invaluable.
    Regards
    David2020

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Chart value drops to zero when no value in a cell.

    The file you uploaded and I modified had references to your H drive.

    I have modified the controls and chart data to reference the workbook and it now works
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-27-2014
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    26

    Re: Chart value drops to zero when no value in a cell.[SOLVED]

    Andy:
    Yes, It works. Thank you for the amended work and the time and effort you've spent on it. It's much appreciated and I have learned a lot with your guidance!
    I'll mark this thread as solved.
    Again many thanks for your help.
    David2020.

+ 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. Formula drops in a cell w/ Data Validation
    By donyadaniels76 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-26-2016, 08:02 PM
  2. E-mail automaticly send if value of cell drops below 90
    By InvalidTxtString in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-19-2016, 11:59 AM
  3. Replies: 5
    Last Post: 07-16-2015, 10:18 AM
  4. how to redflag a value if it drops below a set number
    By mufan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-11-2008, 09:48 PM
  5. zero drops off time
    By rodentrod in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-26-2006, 10:09 PM
  6. csv drops decimal places
    By Steve in forum Excel General
    Replies: 1
    Last Post: 06-29-2005, 03:05 PM
  7. Replies: 2
    Last Post: 02-24-2005, 08:06 AM

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