The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.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.

Thank you

Copy-of-2009-FSA-Qualtimes.xls

UUGH!

2009-FSA-Qualtimes.xls

2009-FSA-Qualtimes.xls

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

\: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?

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

----------- 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.

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

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:

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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.