Change X axis to start count at 1

[excel12121]

In the attached file I have a time column that starts on day 11. when I graph this the x axis label I want to do so in days, with day 11 being reprsented by 1, then all the data for that first day and then day 2 is 12 etc. How can I do this? test data.xlsx

[Pete_UK]

I don't see a days column with values of 11, 12 etc. in your example. Did you attach the correct file?

Pete

[MrShorty]

I assume that you are wanting to use column A as the x data. It appears that this column contains data in the form of "decimal days" (formatted as dd:hh:mm:ss).

What kind of chart are you wanting to use: scatter or line?

The first step in plotting data in Excel is to get the desired data into Excel. So, if you want your x values to be "integer days since day 10", you need to add a column that will calculate the desired X values. This looks to me like it should be as easy as =A2-10 or =INT(A2-10) in an adjacent column. The choice of function will depend on whether you want the X values to retain the "time element" or not. IOW, do you want the X data to account for the difference between 10 minutes past midnight and 1 minute past midnight, or do you want the X data to ignore time and show everything for day "11"/"1" at the same point.

Once you have a column that computes the desired X values, it should be as easy as inserting the desired line/scatter chart. If your helper "X" column is to the right of the desired Y column, you will have to create the chart with whatever Excel gives you, then go in to the Select Source Data dialog and instruct Excel to use the appropriate column for the X data.

[excel12121]

I assume that you are wanting to use column A as the x data. It appears that this column contains data in the form of "decimal days" (formatted as dd:hh:mm:ss).

You are correct it is in decimal days, actually though the 11 refers to the day of the year. So this is only a snippet of all of the data and further down in my larger files the information is displayed 168:22:23:33.00 for example. So in that file I have 163-168 as the "days" and i want to just ahve on the x axis 1-5 labeled whenever there is a day changeover in the data. So 163 would correspond to day 1, and so on.

What kind of chart are you wanting to use: scatter or line?

Either works, I usually use a scattered with smoothed lines.

The first step in plotting data in Excel is to get the desired data into Excel. So, if you want your x values to be "integer days since day 10", you need to add a column that will calculate the desired X values. This looks to me like it should be as easy as =A2-10 or =INT(A2-10) in an adjacent column. The choice of function will depend on whether you want the X values to retain the "time element" or not. IOW, do you want the X data to account for the difference between 10 minutes past midnight and 1 minute past midnight, or do you want the X data to ignore time and show everything for day "11"/"1" at the same point.

Once you have a column that computes the desired X values, it should be as easy as inserting the desired line/scatter chart. If your helper "X" column is to the right of the desired Y column, you will have to create the chart with whatever Excel gives you, then go in to the Select Source Data dialog and instruct Excel to use the appropriate column for the X data.

I am not sure i understand this. the data is not evenly spaced, but the x axis always is on the graph. How can this be? If i have fewer data points on day 1, and then 10x more on day two and want them all plotted shouldnt the spacing be arbitrary?

[excel12121]

I don't see a days column with values of 11, 12 etc. in your example. Did you attach the correct file?

Pete

Sorry I meant to say time column. it is reporting the data point timestamp as dd:hh:mm:ss with 11 being the day of the year out of 365.

[excel12121]

test data.xlsxOkay so i figured out how to do it, now I want to know how to do it more efficiently. Basically if I place a 1 in the first cell, scroll down until I see it change from 11 - 12 and leave the rest blank I can get the graph i want. How can i do this automatically? See attached for what I am referring to:

[Pete_UK]

Put this formula in D2:

=INT(A2)-10

and this in D3:

=IF(DAY(A3)=DAY(A2),"",INT(A3)-10)

copied down to D20.

Hope this helps.

Pete

[excel12121]

That did it! Does typing day indicate that it only pick out the dd portion of the time stamp? does that work for YEAR or MIN or SEC?

[Pete_UK]

Yes, though it's not MIN or SEC - use MINUTE or SECOND. There is also a function MONTH to extract the month number from a date (results in 1 to 12), and HOUR to extract the hour part from a time (results in 0 to 23).

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete