Solved

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

Posted on 2009-05-13
17
640 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
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction It seems that at least a couple of times per month, I answer a question that requires automating Outlook from another Microsoft Office application, usually (although not always) to send one or more email messages.  For example: …
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

828 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