Rounding Formula Question

Microsoft Microsoft excel 2007 (pc)
April 23, 2010 at 05:20:33
Specs: Windows Vista
I am using Excel 2007 and I want to round decimals up or down but not using .005 as the determining factor. I want to round up using .006 instead. i.e. 2.7559 rounds down to 2.75 and 2.7561 rounds up to 2.76
Rounding in this way needs to apply to all figures that I enter not just those used in the example.

See More: Rounding Formula Question

April 23, 2010 at 05:52:33

When you say you are rounding are you doing this using the ROUND() function or are you formatting cells to show two decimal places.

If you are using formatting and two decimal places, Excel does not actually round the value. It rounds the display but does not change the underlying number stored in the cell.

Do you want the numbers you enter to remain as originally entered, but to display based on the 0.06 'rounding',
do you want to convert the number that is entered to a two decimal place value 'rounded' based on 0.06.


Report •

April 23, 2010 at 06:02:34
Thanks for responding.
Yes your first suggestion is what I am after - i.e. the numbers to remain as originally entered, but to display based on the 0.06 'rounding'

Report •

April 23, 2010 at 07:48:15

Math is not my strong point, so I may have this wrong, but when 'rounding' 2.7559 using 6 rather than 5, I get 2.76

as follows:
2.7559 rounds to 2.756 as 9 is greater than or equal to 6
2.756 rounds to 2.76 as 6 is greater than or equal to 6

If I change the rule to just 'is greater than 6'
then this happens for the above number:
2.7559 rounds to 2.756 as 9 is greater than 6
2.756 rounds to 2.75 as 6 is not greater than 6

but applying this rule to your other example (2.7561)
you get this:
2.7561 rounds to 2.756 as 1 is not greater than 6
2.756 rounds to 2.75 as 6 is not greater than 6

As a result using either variation returns the same value for both numbers.

Can you clarify what the mathematical steps are: is it greater than or equal to 6 which will round up, or is it just greater than 6 which rounds up.

As to what you want - the original value to remain in the cell, but to display with different rounding applied, the answer is no it can't be done. The formatting available in Excel does not provide alternatives (although I could always be proven wrong - let's wait for some more responses).

It is possible to use a user defined formula which will display a rounded value in say the adjacent column.

Here is a user defined formula:

Public Function CstmRound(RndVal As Double, RndType As String) As Variant
'A Custom rounding function
Dim strVal As String
Dim n As Integer

strVal = CStr(RndVal)

For n = Len(strVal) - 1 To InStr(1, strVal, ".") + Len(RndType) Step -1
    If CInt(Mid(strVal, n + 1, 1)) >= CInt(RndType) Then
        strVal = Left(strVal, n - 1) & CStr(CInt(Mid(strVal, n, 1) + 1))
        strVal = Left(strVal, n)
    End If
Next n
CstmRound = CDbl(strVal)
End Function

The function is very basic with no error checking and no comments - as I wasn't sure where to go with this.

To try this function, right-click on the name tab of your worksheet and select 'View code'
In the Visual Basic window that opens, you will see your worksheet highlighted in the Project explorer pane on the left.
Right-click on the highlighted sheet name and select 'Insert' then 'Module' (not Class module)
Double click the new module 'Module1' under 'Modules' and paste the code into the large VB window on the right.

Use Alt+f11 (the Alt key and function key #11 pressed together) to return to the main Excel window.
Put a number in Cell A1 and enter this in cell B1:


The string "06" specifies the number of decimal places to round to and the base value.
"006" would round to three decimal places but still using 6 as the basis for the rounding, similarly "6" will round to one decimal place.


Report •

Related Solutions

April 23, 2010 at 12:44:48
I have tried the suggested function but the results are not consistent with what I am looking for.
If the original value is not retained it will not be a big problem. The main requirement is for the desired figure to display.
Therefore any figure like 0.2559, 1.3559, 2.5559 etc (or below)should round DOWN to 0.25, 1.35, 2.55 etc.
Any figure like 0.2561, 1.3561, 2.5561 etc (or above) should round UP to 0.26, 1.36, 2,56 etc.
0.26, 1.36, 2.56 would stay the same.
I hope this makes sense.
Thanks for your patience

Report •

April 23, 2010 at 15:27:22

Try this user defined function:

Public Function CstmRound(RndVal As Double) As Variant
'A Custom rounding function
Dim strVal As String
Dim strValPre As String
Dim strValPost As String

'convert number to its string representation
strVal = CStr(RndVal)

'test that there are at least three decimal places
If Len(strVal) - InStr(1, strVal, ".") < 2 Then
    'no need to round - just return the original
    CstmRound = RndVal
    'there are at least three decimal places
    'split the string after the second decimal
    strValPre = Left(strVal, InStr(1, strVal, ".") + 2)
    strValPost = Right(strVal, Len(strVal) - InStr(1, strVal, ".") - 2)
    'test the remaining decimal part of the number
    If CDbl("0.00" & strValPost) >= 0.006 Then
        'greater than or equal to 0.006, so round second decimal place up
        strValPre = CStr(CDbl(strValPre) + 0.01)
        CstmRound = CDbl(strValPre)
        'less than 0.006, so leave first two decimal places 'as is'
        CstmRound = CDbl(strValPre)
    End If
End If
End Function
There are now some comments in with the code to show what is happening.

This function only uses your 6 basis, so the formula in the cell is now just:


The function appears to work based on your latest examples.
I don't know if my method will hold up over the full range of possible values - if it doesn't please let me know, showing examples of what does not work.

If this function works 'as expected' it might be possible to use it to replace entered data, rather than showing the result in another column.


Report •

April 23, 2010 at 16:05:10
Wow, that is clever stuff. Way beyond my ability.
It calculated correctly everything I threw at it.
I have an exam next week and that will help greatly
Thanks so much. Your time and effort is very much appreciated.

Report •

April 24, 2010 at 04:41:58

Thanks for your comments.

The function is really quite simple.

This is what it does:
1. Converts the number into a string of characters
2. Finds the position of the decimal point and counts the number of characters after the decimal point.
4. If there are two or less decimal places just return the original value
5. If more than 2 decimal places do the following steps:
6. Create two strings from the initial string - the first is the original number with only two decimal places and the second is all decimal places from the third on.
7. Tack the second string on to "0.00" and compare it to 0.006
8. If greater than or equal to 0.006 then take the first string (original number and its first two decimal places) and add 0.01, or if not greater than 0.006, just leave the first string alone
9. Return the first string (the number with the first two decimal places) with or without the added 0.01


Report •

Ask Question