Forum Discussion

Kate Vilain's avatar
Kate Vilain
Copper Contributor
Jul 16, 2018

How to standardize x-axis units from real data in a line graph or scatter plot?

I am trying to chart a variable (called RR) against a real number of years and create a chart like the one below. Unfortunately, I had to capture the inputs by setting the RR variable in a model and deriving the corresponding year value. The resultant data seem impossible to chart the way I want: with the years value standardized in increments of 1 (year) across the x-axis. I hoped to force this via formatting the axis, but whatever data type I use, I cannot shift it. I think I need some function to extrapolate RR from the data for standard years 19, 20, 21, 22, etc. I tried applying the function the exponential trendline provides, but it's nowhere close to my data. Suggestions? Here are my data: RR years to event 0 21.59 0.1 22.115 0.15 22.3 0.2 22.535 0.25 27.392 0.3 28.014 0.35 28.408 0.4 29.12  Thanks for any help!

 

Goal chart (with x-axis in regular intervals):

 Best I can do:

Charts & Visualizing Data
Need Help
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 17, 2018

    Hi Kate,

     

    To show your x-axis in years with 1 year increment better to transform your x-values into dates with helper column, like

    =DATE(INT(C19)+100,1,MOD(C19,1)*(DATE(INT(C19)+1,1,1)-DATE(INT(C19),1,1)))

    After that use Yrs instead of time as your x-axis and format it as

    with YY format

    Result is like this

    and attached

     

     

     

    • Kate Vilain's avatar
      Kate Vilain
      Copper Contributor
      Jul 17, 2018

      Thank you!! Using a date conversion had occurred to me, but I was too fried to work it out. REALLY appreciate this!!

    • Alison Telschow's avatar
      Alison Telschow
      Copper Contributor
      Aug 22, 2018
      I'm tracking projects in Excel.  I created a calendar in another tab, with a scatter chart, so my start and end dates create lines with a go live diamond at the right end, dropping down to x-axis with an error bar. 
       
      The x-axis randomly shows odd dates and intervals, that I need to fix, and show a normal date frequency.
       
      So instead of 6/1/2017, 9/9/2017, 12/18/2017...
       
      I'd like 6/18, 7/18, 8/18... Or something to that effect.
       
      I've read up a bit on changing the x axis to include a date-based axis, but this isn't working either.  
       
      Thanks for letting me know how best to fix the x-axis labels.
      Alison

Resources