Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
Evening!
Hoping someone can help me out with this please.
I have a chart in excel, 2010 for mac.
The x-axis is a log scale, and the markers by default are 10, 100, 1000, 10000 etc
I want to set them as time, for example: 1 sec, 5 sec, 10 sec, 30 sec, 1 min, 5 mins, 15 min, 30 min, 60 min....
Anybody know how to do that?
Thanks in advance.
Bump
I'd guess easiest if it's a small dataset would be to manually change to seconds rather than using minutes as well but I bet excel could probably interconvert if "we" knew how(can you format the cells as "time" units ?
Have you sent up a stoner batlight ?
Make sure your time values are formatted correctly - they'll essentially be fractions of a day, with a full day being 1, and 1 second being equal to 1/86,400.
Then plot as a scatter chart, double click on the time axis and tick the "Logarithmic scale" box. Then, while still in the axis format dialogue, select 'number', and put in a custom format which is just the letter s. This will display the values on the time axis in seconds. I don't think you can do it so that the seconds are in seconds, minutes in minutes and hours in hours, all values have to be the same units.
XL doesn't do log-axes with custom increments. You can finally select the base, but not specify custom ticks, which is what you want to do. Formatting is harder still
A workaround I've used is to plot as a series with your times as categorical classes rather than continuous X-Y, this works if your X axes (time) are reasonably spaced. Your eye will naturally interpolate between observations anyway.
Or move to plotting in R.
Is it to estimate power curve and ftp? SO you need regression to one hour?
Depending on how your time data are formatted/recorded, I'd probably go for what scaredypants says.
Inter-conversion of time is a chore (and I think it differs between Win and Mac), but I'd start by turning on the Analysis Tool Pack and have a play with CONVERT or some combination of e.g. =MINUTE(cellref)*60+SECOND(cellref)to get a time value in seconds.
Failing that, excel does 'decimal time' where increments of a day = 1, so e.g. 12 noon = 0.5. Again, I think the starting point differs between Win and Mac, but F1 is your friend for the details.
Then plox x (in seconds) on a log axis.
It works (but not with nice round numbers!) if you do this:
Enter your x values in the s/sheet as 00:00:01 etc (thats 1second)
then when you plot it edit the axis, set as log etc - then click "number" and tick "linked to source" and you'll get something that makes sense and combines H:M:S
From a scientific point of view I think using one unit would be neater but obv. on a log scale these are less intuitive - you could add a second axis to provide both.
Thank you everyone. Will have a play with these ideas today.