Excel Advice please

Entai

Entai

Entai

Caporegime
Joined
28 Feb 2004
Posts
74,822
I have a column of times

They are minutes:seconds.hundreths

so for example
1:16.98
1:17.55
1:17.64
1:17.95
1:18.13
1:18.87
1:20.49


I am trying to work out a formula to find the average of those times, and display the output in the same format as the originals.

Would think it simple, add them together and divide by how many you have.

But I cannot for the life of me get it to work.

I think it is a formatting issue but really not sure.

Sorry its such a basic question, and I am certain there is the simplest of answers.

Thank you in advance for any help
 
Django x2 said:
Works here:
od56FoF.png


Excel Format must be set to Time\Custom mm:ss.00

Screenshot above had the result cell set to mm:ss.0
Click to expand...



Now getting #DIV/0!
 
wesimmo said:
As above, if the range is definitely ok, #DIV/0 with calculations on times normally means they are text and Excel sees them, effectively, as 0.
Click to expand...

cells have been formatted custom/mm:ss.00

so surely excel should see them as time not text ??
 
Django x2 said:
Thanks, I see some others in there now but I've got it working. Out of interest, the original values, where they pasted in from somewhere else?
Click to expand...


The original values were exported as a text based file, from a swimming race timing and results reporting package then dropped into excel.
 
Django x2 said:
You've answered my question then. White space. That tool is adding white space to the front of every time... You can simply click where the cursor is and hit DEL and magically watch the formatting change :)
czhu6W1.png
Click to expand...


Knew it would be a simple answer.

Now to work out a way of deleting that space in front of each time with hundreds of times across multiple worksheets .
 
You must log in or register to reply here.
Back
Top Bottom