+ Reply to Thread
Results 1 to 13 of 13

Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

  1. #1
    Registered User
    Join Date
    05-21-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    I working with a dynamic graph that uses the following formula to define the X-Axis ("Fast" is the name of a specific sheet within the book). The dataset the formula is looking at will frequently have either no data or "0" as the value. I would like to edit this formula such that the graph does not plot anything if one of these condition is present. I have seen solutions recommending the use of NA(), but I cannot figure out how to change my formula. Can anyone help?

    [=OFFSET(Fast!$A$1,1,0,COUNTA(Fast!$A:$A))]

    Thanks in advance for your time! Tisha

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,171

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Hi Tisha & Welcome to the Forum,

    When you say you don't want them to plot, are you saying you just don't want the data value to show up on the plot at all? Or, say you have 12 data points but you only have 6 which are of value, do you want just 6 data points on the chart and that's it?

    Do you have a sample you can post and what you require?
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    05-21-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Hi, I appreciate the quick response! Ultimately, this file would be used be multiple people with varying datasets. Often datasets may be incomplete or of varying sizes; I need this graph to accomodate any situation and appear as if there no missing data. So, yes, if there are 5 lines of data, I only want to see 5 items on the graph instead of a bunch of zero data points.
    I have attached an example of my graph. Please let me know if you need further clarification. Tisha
    Attached Files Attached Files

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,171

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Maybe this will give you an idea.

    Not sure what you wanted to do with both columns, but if you give me more of an idea of where you want to go we can get there.

    Notice >> formula in D1 which is used with formula in A2 and down.

    Column B and C have a vlookup.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-21-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Smile Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    I like it! My method was kluged together, so I am not suprised there are much better ways. It took be a bit to figure out what was going on (with your file), but I think I have a pretty good idea. The only thing I don't understand now is why drop down is not switching between ROP and Distance. If you can help with that, I this problem will be resolved.

    I really appreciate your time with this! This little project has really taught me several new things.

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,171

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    That is what I was asking?

    With your example, the Data tab has Distance Drilled and Total RPM (AVG).

    The headers for the Fast tab has ROP and Distance Drilled.

    When you change the drop down in C25, what do you expect to happen?

    I imagine you want the chart to update? Is ROP and RPM the same?

  7. #7
    Registered User
    Join Date
    05-21-2012
    Location
    Houston
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    So, I have previously thought of myself as reasonably savvy with Excel (at least in my circle of engineers), but I am definitely in my place. You are correct, in attempt to strip my actual file of confidential data, I mislabeled a ROP/RPM (It should have been labeled ROP). My goal is to allow users to switch between ROP and Distance (on the Y-axis), with Type on the X-axis. In addition, I need the X-axis to only show data points that actually contain data (which you helped me accomplish).

    I have reattached my working file. This file contains the necessary corrections.
    Again, I sincerely appreciate your help and patients with this!
    Attached Files Attached Files

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,171

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Hi Tisha,

    Chart building/preparing can be somewhat of a science project as there are many ways in which to go. I had one question about the data, but as not to delay time, Ive included the update and you can decide if it is relative.

    Option 1 (Fast Tab):
    D1 holds the count of cells on the Data tab column A which are not empty and E1 is what column you want displayed on the chart. Select C25 and the chart will update accordingly.

    Option 2 (Fast (2) Tab):
    Now the user selects either Distance Drilled or ROP from B1 and all will be changed, both on the chart and in column B. C1 is the same count as option 1, non-blank cells from Data tab column A.

    Option 3 (Fast (3) Tab):
    I noticed your data on the Data tab, row 12, ROP is zero. Question, should the chart reflect the zero or not at all. In this option I went with no, only produce an axis for > 0. Notice C1 will change depending on a selection in B1. ROP >> 9 Distance Drilled >> 10.

    My preference is to go with option 2 or 3. That is, only show the user data for what is selected, but again, only my preference.

    Not sure which way you want to go, but here are some options. Once you select your direction you can simply delete the tabs not required and then in the name manager, removed any extra named ranges.

    Right now the data tab goes to row 22, but if your data will go further than this, then dynamic ranges would be the next step which I have not done.

    If you require some more help please let me know.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-18-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    20

    Question Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Hi Tisha, Jeff,

    In the excel files you have shared, you have used "=Chart2.xlsx!Myrng" for chart values. Could you please tell me where have you defined the values of "Myrng". Or where can I find it? Because, I've a similar problem.

    Thanks in advance,
    Nivi

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,171

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Hi Nivi & Welcome to the Forum,

    Look in the name manager (Ctrl + F3).

    If you have further questions, please start your own thread.

  11. #11
    Registered User
    Join Date
    09-18-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    20

    Thumbs up Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Thanks Jeff,

    That helped me.... I just followed this post as, it will be easier for me to explain my problem.... Will surely post new threads on my new doubts.... Thanks again....

    Nivi

  12. #12
    Registered User
    Join Date
    04-10-2013
    Location
    Nottingham, England
    MS-Off Ver
    Excel 2003
    Posts
    39

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Hi,
    This is posted some time after the last message posted, but I have been following this thread and it has been useful. One question I had, the drop down box to select the chart title & ranges on the chart - 'distance drilled' or 'ROP', how is this set up and what named ranges does this refer to, and how is the chart reconfigured each time when the user chooses the selection? I'm hoping to prepare a similar chart for UK 2011 census data, either a total numbers chart or a % chart.
    Many thanks,
    Jonathan

  13. #13
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Dynamic Graph, Do Not Want X-Axis to Plot Blank or Zero Values

    Jonathan,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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