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
 
Entai said:
First thing I tried.

Result was 00:00.0
Click to expand...
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
 
Last edited:
Entai said:
Now getting #DIV/0!
Click to expand...

How weird, what version of Excel are you using? Can you share the file?

Check your range too because #DIV/0 occurs when an empty cell or a cell with 0 is included in the formula or for averages, one of those cells contains text
 
Last edited:
Are they definitely numbers, or has someone entered them as text?

Quick way to check, change the format to normal numbers and you should see a decimal.

Basically Excel doesn't use time as such, it converts time into 'normal numbers', calculates and then shows it again as a time.

One day is 1, starting on Jan 1st 1900.
 
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 ??
 
Like I say, try changing the format to normal, if they don't change then they are text.

For 12 noon you should see 0.5 (half a day), 18:00 is 0.75, etc.

Edit: I've fixed hundreds of spreadsheets and times are the biggest problem people tend to have, they are a PITA until you've had some experience with them and when they go wrong.
 
Also if you intend to calculate, the format is normally [h]:mm:ss.

This allows the background calculations to work properly on some cases.
 
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...

Yeah, this could be the issue.

Pasting as values might work if they're taken from someone else's spreadsheet.

That or you can sometimes copy '1' from a cell, select the range of times, paste special>>multiply and that will sometimes force Excel to convert them to numbers.
 
The Excel file posted by @Entai is completely ignoring any alteration to the formatting (at least that column is) which suggests it's locked open some formatting from wherever the values came from. I copied them out as pasted values, got the formatting to mm:ss.00 and then pasted back to ColE and the average updated.
 
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.
 
Entai said:
The original values were exported as a text based file, from a swimming race timing and results reporting package then dropped into excel.
Click to expand...

I'd put a tenner on you just needing to put a step in the process to make sure you don't bring text that looks like numbers in then, looks like @Django x2 has confirmed the issue.

Oh, and if there are lots of times, and the total before you average them is more than 24 hours, use the format I suggested above.
 
Entai said:
The original values were exported as a text based file, from a swimming race timing and results reporting package then dropped into excel.
Click to expand...

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
 
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