Min/Max Axis Options Issue

[DirtyDenim]

Hello all,

I'm trying to plot a graphs of numbers (55) VS durations (mm:ss) and I want to condense the plots as much as possible. However, because the x-axis values are the in the format (mm:ss) I am having issues editing the axis options. Excel axis options use decimal format and I can't get them to adhere to the format (mm:ss) instead.

I've tried going to the "Number" tab and changing the values to times but this doesn't effect the axis options, they still go by decimals which doesn't help me condense the graphs.

Any help would be greatly appreciated.

[Jacc]

First of all, you seem to have hours instead of minutes.
I think you have to calculate the min and max numbers, you can see how I did that.
Not sure exactly what you want but this makes sense to me.
A bit sloppy response but time is limited.

[DirtyDenim]

"1/24/10 = 6 min"

So you're calculating the minute by using the number 1 as a unit for 1 day?

Interesting. I'll try this out. Thank you!

[Andy Pope]

Excel stores time as a division of 1.
So 1 hour = 1/24

You can use the CEILING and FLOOR functions to calculate MAX and MIN values.

In xl2003 you could enter date/time values directly into the min/max/major/minor axis property boxes and it would auto convert to precision value.

[Jacc]

Yup, that is correct! That's the way Excel keeps track of time.
It also keep track of dates by counting the number of days since 1/1 1900. Try hit Ctrl + ; and it will give you todays date, then try format that as a number.

[DirtyDenim]

I've compiled all the data onto one page but I'm having troubles converting everything to times in (mm:ss) the way you did. It seemed simple, but I can't get it right. All of the times generated are just about the same.

[Jacc]

Your formula produce the correct result, it's the format that makes it look wrong.
However you don't need an array formula, it's just complicating things. Array formulas are for other stuff. Just use this formula and fill down:

Please Login or Register  to view this content.

Then format the cell, number formats, custom format and type mm:ss.

Edit: Or maybe just m:ss

[Jacc]

I just noted one thing for typing in new data; even if the cell is formatted as mm:ss you can't type in 3:34, you have to type 0:3:34.

[DirtyDenim]

you sir are brilliant. Salvaged hours of my work. Thank you so much.

[Jacc]

De nada