Solved

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

Posted on 2009-05-13
17
636 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
Comment Utility
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
Comment Utility
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
Comment Utility
wrong attachment...sorry...you need this one
0
 

Author Comment

by:Andrea_Jennison
Comment Utility
0
 
LVL 4

Expert Comment

by:Doc_McAlister
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 4

Expert Comment

by:Doc_McAlister
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
30.8 to 30.89?

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

0
 
LVL 4

Expert Comment

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

Expert Comment

by:eshurak
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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: …

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now