New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Converting tenth of a second to fifth of a second

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Converting tenth of a second to fifth of a second

Postby mikec326 » Fri Mar 11, 2016 9:39 am

Is there a way in formula to convert a number from a tenth of a second to a fifth of a second? I am trying to convert numbers such as 12.8 into 12.4 or 50.6 into 50.3.

Any thoughts are appreciated.
  • 0

mikec326
Rookie
 
Posts: 1
Joined: Mar 11, 2016
Reputation: 0
Excel Version: 2010

Re: Converting tenth of a second to fifth of a second

Postby gebobs » Mon Mar 14, 2016 2:53 pm

Not exactly like that and you wouldn't want to since your 12.4 and 50.3 would be stored as those in tenths, as it should, since that is the definition of decimal.

However, you can have it display fractions i.e. 12 4/5 and 50 3/5. Thus...

Select the cells you would like to display as such.
Create the custom number format =# ?/5.

Enter 12.8 in the cell and it will display as 12 4/5.
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010

Re: Converting tenth of a second to fifth of a second

Postby pecoflyer » Wed Mar 16, 2016 7:11 am

Hi gebobs

I can't seem to get your solution to work

Leaving the space before the question mark returns an error. If I omit the space I get 64/5 for 12.8.

Can this be due to regional settings ?
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Converting tenth of a second to fifth of a second

Postby ConneXionLost » Wed Mar 16, 2016 11:59 am

Hi pecoflyer,

This seems to work for me:

Code: Select all
=TEXT(A2,"# ?/5")


Cheers,
  • 0

ConneXionLost
Regular
 
Posts: 68
Joined: May 9, 2013
Location: Canada
Reputation: 19
Excel Version: 2003, 2010

Re: Converting tenth of a second to fifth of a second

Postby pecoflyer » Wed Mar 16, 2016 12:05 pm

Thank you for the suggestion. Unfortunately I get a VALUE error

BUT adding an underscore saved the day
Code: Select all
=TEXT(A1;"#_ ?/5")
did work.

( Don't ask me why)
  • 0

A relevant topic title helps get faster and more answers
pecoflyer
Moderator
 
Posts: 1274
Joined: Jan 24, 2012
Location: Belgium
Reputation: 39
Excel Version: 2003/2007/2010

Re: Converting tenth of a second to fifth of a second

Postby gebobs » Mon Mar 21, 2016 1:15 pm

pecoflyer wrote:I can't seem to get your solution to work

Leaving the space before the question mark returns an error. If I omit the space I get 64/5 for 12.8.

Can this be due to regional settings ?

Try just using one of the canned fraction formats, say for eighths, then go to number format again, custom formats, and edit the format for fifths.
  • 0

gebobs
Regular
 
Posts: 74
Joined: Feb 23, 2015
Reputation: 9
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 37 guests