General discussion

Locked

Chart run-times in minutes by weeks

By Marshwiggle ·
I am trying to chart run-time averages in Excel taken on a weely basis . For example the 1st week the average run time is 12 minutes, 42 seconds (12:42 in the spreadsheet); the 2nd week is 11:00, the 3rd week 9:44, the 4th 10:50. My minimum and maximum are 0 and 20 minutes.

I can get a chart easily enough, but I can't seem to get the value axis (y) formatted to go from 0 to 20 in 1-min. (major tick) and 1/2-min. increments.

Does anybody know how to do this, or am I trying to do the impossible?

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by sgt_shultz In reply to Chart run-times in minute ...

i bet it is the cell formatting. represent your data as decimal portions of minutes and format cells as numbers.
u will get your y value in decimal minutes which is what you want i think. minutes and half minutes. right now, bet cells contain time format and you are seeing that on axis with week on other axis

Collapse -

by Marshwiggle In reply to

You are correct in that my cell data is formatted as mm:ss, and I want to keep it that way because it is more familiar to people. When I tried to format the x-axis as 1 maximum and .5 as minimum, I got a 21-tick scale from 00:00 to 00:00.

Collapse -

by amanabala In reply to Chart run-times in minute ...

You can change the settings for major and minor ticks by right-clicking the y axis and clicking on 'Format axis.' In the Patterns tab, change the Minor tick type from None to Outside or inside as you like. In the Scale tab, you can change the values for Major and Minor ticks - default is usually 5 and 1 change it to 1 and 0.5 and you are ready to go!
cheers,
Ravi

Collapse -

by Marshwiggle In reply to

Thank you for your suggestion for using major and minor ticks of 1 and 0.5, respectively, but I believe I indicated in my question that I have tried that: "... I can't seem to get the value axis (y) formatted to go from 0 to 20 in 1-min. (major tick) and 1/2-min. increments." I forget what the exact results were, and I don't have access to the spreadsheet at the moment, but they were not the tick labels of 1:00, 1:30, 2:00, etc. that I was looking for.

Collapse -

by amanabala In reply to Chart run-times in minute ...

I sort of misunderstood your question earlier. I did spend a few minutes and found a hack: Please try the following:
1. Enter the data as 00:mm:ss (instead of just mm:ss - to prevent Excel from treat your data as hh:mm)
2. On the format axis dialog, Number tab, select Custom and enter "hh:mm"
3. In the scale tab, uncheck every box - enter 0 for Minimum, 0.014 for maximum (20 minutes = 0.014 of one day), 0.0007 for Major tick and 0.00035 for Minor tick.
Your labels will now show 00:00 to 00:20. I think this is the closest I can get without writing a macro.
Hope this helps
-Ravi

Collapse -

by amanabala In reply to

The reason for using hh:mm instead of mm:ss is that the labels will look a little off by about 1 second every other tick i.e., 1:00, 2:01, 3:02 etc due to rounding. If you have the time to play with the values for Max, major tick and minor tick I think it is possible to find the right number (0.0013996 or something like that..)
-Ravi

Collapse -

by amanabala In reply to

Well, I wanted to see the end of it so, here are the numbers for custom type mm:ss
Max = 0.1398
Major tick = 0.000694444
Minor tick = 0.000347222

-Ravi

Collapse -

by Marshwiggle In reply to

My apologies for letting this go so long. I got completely sidetracked, and am no longer at the position where I was working on this issue; nor do I any longer have access to the spreadsheet I was working with. But Amanabala's most recent submissions look like they should work, and he/she stuck with it, so I rated the answer acceptable.

Collapse -

by Marshwiggle In reply to Chart run-times in minute ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums