+ Reply to Thread
Results 1 to 5 of 5

Scatter Plot X Axis & Trendline Formula Wrong

  1. #1
    Registered User
    Join Date
    10-13-2010
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    22

    Scatter Plot X Axis & Trendline Formula Wrong

    I have this spreadsheet (attached) that does this:

    User inputs "Entry Point X & Y" and "Exit Point X & Y". The user is inquiring what every point along that path is, every "Calculate X,Y every" feet. The table automatically lists every X & Y along that path, every Z number of feet (Z being user specified earlier).

    The table automatically checks the first and last value, as they should be the same as the user input value.

    Everything seems to work fine. My formulas check and all is well.

    However, to give our engineers a warm & fuzzy, I wanted to add a "check" diagram. The first chart here plots the user input X&Y and gives the trend formula. The second chart plots all the points as calculated in the table. Here's where things go wrong.

    The chart refuses to use the same Horizontal Axis labels as the first chart and puts them at REALLY small increments. Then the trendline equation doesn't match. I've even tried the trick where you change the Number format of the equation to include a lot of 0's but still the formulas don't change (other than expanding out the decimal place). Please help! This is my last step to making this tool work!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Scatter Plot X Axis & Trendline Formula Wrong

    You have committed the usual "what should I use to mimic empty cells" blunder. In column G, your "if there is no data" result is empty string. If there is a text string (even "") anywhere in the x values range of the chart, Excel will treat the entire range as if it is text, and will use its default "count number" as the x values.

    I would suggest that you read this article (http://peltiertech.com/mind-the-gap-...g-empty-cells/ ), especially the section titled "simulating empty cells". Then, replace the empty string "" with NA() functions so that Excel's chart engine will correctly ignore those points.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    10-13-2010
    Location
    Houston
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Scatter Plot X Axis & Trendline Formula Wrong

    That works great, except that because #N/A are now in those cells, when I try to do a MATCH to try and return the next greatest value, it keeps spitting back #N/A...

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Scatter Plot X Axis & Trendline Formula Wrong

    Sometimes, in situations like that, I use two tables: one for the MATCH() function, and one for the chart. Assuming you keep the current table as the lookup table, then the chart table could be over in columns R and S or T, and the formula here would be something like R3=IF(F3="",NA(),F3) copied down and across. Then, the chart uses columns R and S for its data, and the MATCH() function can continue to use columns F:H.

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,609

    Re: Scatter Plot X Axis & Trendline Formula Wrong

    Another proposition - typical dynamic chart.

    Define 3 names (Ctrl+F3) in your workbook
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and use later 2 for your chart data source
    Attached Files Attached Files
    Best Regards,

    Kaper

+ 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. XY Scatter Plot with 1st of month on X axis
    By teylyn in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 10-04-2016, 03:17 AM
  2. Scatter plot with two X axis data
    By hityou123 in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 02-02-2015, 08:26 PM
  3. Help with Dates on X axis (scatter plot)
    By apc2012 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-13-2014, 11:41 PM
  4. Horizontal axis limiting formula in xy scatter plot
    By S K ROY in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-06-2011, 09:15 AM
  5. Excel 2007 : Scatter Plot-Changing the X-axis HELP!
    By Pacinterp in forum Excel General
    Replies: 1
    Last Post: 04-07-2011, 04:11 AM
  6. [SOLVED] Double y-axis scatter plot.....?
    By Chikngizrd in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-06-2005, 12:50 PM
  7. Wrong Interpretation of Excel Value - Scatter Plot Problem
    By Klaus Kragelund in forum Excel General
    Replies: 4
    Last Post: 02-18-2005, 10: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