Go Premium for a chance to win a PS4. Enter to Win

x
Solved

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

Posted on 2009-05-13
Medium Priority
665 Views
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
Question by:Andrea_Jennison
• 8
• 5
• 4

LVL 4

Expert Comment

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

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

ID: 24378526
wrong attachment...sorry...you need this one
0

Author Comment

ID: 24378535
0

LVL 4

Expert Comment

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

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

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

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

LVL 4

Expert Comment

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

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

LVL 4

Expert Comment

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

ID: 24400154
30.8 to 30.89?

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

0

LVL 4

Expert Comment

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

LVL 3

Expert Comment

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

Doc_McAlister earned 2000 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

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

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

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 â€“ â€œskill leveâ€¦