Solved

Force excel to display last dicmal value of 9 in swim times

Posted on 2009-05-13
17
648 Views
Last Modified: 2012-05-11
is there a way to tell Excel to "round" a numer to the next 9/100th?   So I want it to round 33.55 to 33.59, 30.8 to 30.89, etc.  
0
Comment
Question by:Andrea_Jennison
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
17 Comments
 
LVL 4

Expert Comment

by:Doc_McAlister
ID: 24378029
Set a custom formatting on the cell as the following:

0.09

Then set the value of the cell to this equation:

=floor(33.8, 0.1)

The equation truncates the hundredths place, the formatting sets the hundredths place to a 9.  So the final solution will look like this:

Put your list of numbers somewhere else, like on an unused worksheet.  Enter the formula '=floor(<first cell in your list>, 0.1)' into the first spot where you want your list of numbers to be.  Copy the formula down X cells so it picks up the entire list floored where you want it to be displayed.  Then highlight those cells, right click, format cells, and set them to custom formatting with a format string of 0.09.

They will now display with their hundredths place set to a 9 always.  The value in the cell will be the original value floored to the tenths digit.  The original value will be wherever your hidden list is.
0
 

Author Comment

by:Andrea_Jennison
ID: 24378439
That works perfectly...the only catch is that the times are currently entered as h:m:s format, so 22.9 seconds displays in the formula bar as 12:00:23 am.  As in understand it, I need to convert these to number of seconds...any quick solution for that?  It's attached...Then I could use the floor function you described.

Thank you
Copy-of-2009-FSA-Qualtimes.xls
0
 

Author Comment

by:Andrea_Jennison
ID: 24378526
wrong attachment...sorry...you need this one
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:Andrea_Jennison
ID: 24378535
0
 
LVL 4

Expert Comment

by:Doc_McAlister
ID: 24378824
Hrrmm ... The time format is less friendly to this sort of thing.  Also, we are talking about hundredths of a second here, yes?  Not the seconds themselves?

And are you primarily concerned with what shows in the formula bar or what shows in the sheet?
0
 

Author Comment

by:Andrea_Jennison
ID: 24389025
I'm only interested in what displays on the sheet...the formula bar doesn't matter.  And yes, we're talking about 100ths of seconds.
0
 
LVL 4

Expert Comment

by:Doc_McAlister
ID: 24390505
OK, This format string did it for me for the top half of your sheet, using the first where there were no minutes and the second where there are minutes.

\:ss.0"9"
m:ss.0"9"


However, note that formatting does not change the underling value so if you use those cells in an equation they won't act like the hundredths place is a 9.  It just looks that way.This works there because none of those cells go out to the hundredths and no rounding occurs when you truncate it to tenths.

When you need to stop it from rounding the tenths digit up things get tricky because of the way seconds are stored inside excel.  Basically, the time part of a date/time in excel is stored as a fraction calculated by (seconds / 86,400) the number of seconds in a day.  So one second is 1/86400 =  .00001157407 internally and 1.5 seconds is .00001763111.  You can see what the real number in any cell is by formatting it to scientific notation (format number will just show zero because the numbers are so small).  

Soooo how about this (I snagged cell M41 as my test cell - it used to contain the formula = K41+(0.1*K41)):

Cell Equation =FLOOR( 86400 * (K41+(0.1*K41)), 0.1) / 86400
Cell format =  m:ss.0"9"

Now I can't get that to work with values typed into a cell like those at the top of a sheet, but I can get it to work with cell references.  So you could make a new sheet where every cell refers to your current sheet with this equation and the appropriate format string::

Cell Equation =FLOOR( 86400 * (reference cell), 0.1) / 86400

This multiplies it by 86400, then floors it to the nearest tenth of a second, then divides it back down by 86400 to excels internal representation of a second.

Good?
0
 
LVL 3

Expert Comment

by:eshurak
ID: 24398932
I was looking at using m:ss.0"9" format as well, but the problem is that it is not actually rounding so every value winds up with a "9" as the last digit.

Adding a function to the cell is not very effeshinet.   To much work for the user.

I think maybe some code is the way to go.  You can loop thru all the cell and modify the values that need to be rounded.  I've attached that loop below.  We can work on the loop if you're interested in going this route.


Sub formatcells()
Dim rng As Range
 
For Each rng In Range("A8:O130")
 
    If IsNumeric(rng) And rng <> "" Then
        'formula that round last digit to 9
        '???
    End If
 
Next
 
End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:Doc_McAlister
ID: 24400025
As I understand the question, she doesn't want it to round.  She wants a 9 there no matter what.  And she doesn't want the tenths digit to stay the same regardless of what was in the hundredths place:

----------- quote ----------
So I want it to round 33.55 to 33.59, 30.8 to 30.89, etc.  
-----------------------------

Creating a new worksheet is inelegant, but it is far easier for the novice user ...

1 - Click on new worksheet.
2 - put the upper left cell in your grid to this formula:

  =FLOOR( 86400 * (reference cell), 0.1) / 86400

where Reference Cell is the upper left cell in your other page.

3- Drag that formula to your entire grid ... like all excel file copies it'll adjust the cell reference as you copy.

4 - copy paste the text cells over into their proper place and delete the #ERROR text out of the blank cells.

5 - highlight the entire grid and apply the cell formatting to it.


Should take less than a minute.  You wind up with two sheets in your workbook, one the base sheet and the other the formatted one ... but you don't have to learn script.  Regardless, the  =FLOOR( 86400 * (reference cell), 0.1) / 86400 is the formula you would plug into that script if you were comfortable using it and combine with cell formatting for desired effect.
0
 
LVL 3

Expert Comment

by:eshurak
ID: 24400105
Your probably right doc, but she is saying round and all three examples are >= 5 so they should be qualify to be rounded.  Either way I just like working on these types of problems, so here's my solution in case you need it.  
Sub FormatCells()
Dim rng As Range
Dim NewValue As String
 
For Each rng In Range("A8:O130")
 
    If IsNumeric(rng) And rng <> "" Then
        If Right(rng.Text, 1) > 4 Then
            NewValue = Left(rng.Text, Len(rng.Text) - 1) & 9
            rng.Value = NewValue
        End If
    End If
 
Next
 
End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:Doc_McAlister
ID: 24400135
Edit:

this sentence:

 And she doesn't want the tenths digit to stay the same regardless of what was in the hundredths place:

Should be this sentence:

 And she wants the tenths digit to stay the same regardless of what was in the hundredths place:
0
 
LVL 4

Expert Comment

by:Doc_McAlister
ID: 24400154
30.8 to 30.89?

IE, 30.8<b>0</b> to 30.89.

0
 
LVL 4

Expert Comment

by:Doc_McAlister
ID: 24400163
Bah, forgot this board doesn't honor my html =D.
0
 
LVL 3

Expert Comment

by:eshurak
ID: 24400310
Try the code Doc.  If the last digit is 5 or above it is replaced with a 9.
0
 
LVL 4

Accepted Solution

by:
Doc_McAlister earned 500 total points
ID: 24408932
Precisely. And per the original question she wants 3.8 to become 3.89.

3.8 is the same as 3.80.  The last digit in 3.80 is not 5 or above, yet she still wants it replaced with a nine.

It isn't really rounding.  There isn't a word for what is being asked but the concept is closer to ceiling than rounding.  Just ceiling to the next 9 hundredth instead of the next tenth.

Barring the 3.8 to 3.89 being a typo of course but there is only so much second guessing one can do.
0
 

Author Comment

by:Andrea_Jennison
ID: 24413379
Good morning...thank you for all of your comments...Doc's response last week was exactly correct.  I simply need the 9 to display in the hundredth's position.  I do not need excel to change the value.  And if I take the number out of the time format and simply enter it as a number of seconds and format it, all's well!  
0
 
LVL 3

Expert Comment

by:eshurak
ID: 24413394
No your right 3.8 should become 3.89.  I'll have to modify that code if she's wants to use a code solution.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question