CODEWITHSUNDEEP
excelfunctionmathexcel-formularounding
gimmegimme
gimmegimme

Reputation: 330

Excel function to round up a number based on 0.15 instead of 0.5

Here's the table of data:

+---+------+---+
|   |  A   | B |
+---+------+---+
| 1 | 5.16 | 6 |
| 2 | 5.15 | 6 |
| 3 | 5.14 | 5 |
+---+------+---+

Column B shows the desired results of rounding a number up or down to nearest whole number based on the threshold of .15, instead of the standard 0.5.

The following function is best I've come up with:

=IF(B1-INT(B1)>=0.15, CEILING(B1, 1), FLOOR(B2, 1))

Is there any other solution involving less nested functions or less length overall?

Upvotes: 0

Views: 855

Answers (3)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

If you are okay with using VBA, you can create a UDF (User Defined Function). Keep in mind that this will require you to save as a Macro-Enabled Workbook.

Function customRound(ByVal dNum As Double) As Double

    With Application.WorksheetFunction
        If (dNum - Int(dNum)) >= 0.15 Then
            customRound = .Ceiling(dNum, 1)
        Else
            customRound = .Floor(dNum, 1)
        End If
    End With

End Function

Protip: You can rename the UDF to whatever you would like as long as the name is not already reserved within Excel. In this example, we are simply naming it customRound()

You would then be able to replace your above formula with:

=customRound(B1)

If you are unfamiliar with VBA, read on:

How do I access the VBE?
You can gain access to VBE by pressing Alt + F11 while you are inside your workbook.

Okay, So I have the VBE open. Now how do I apply this UDF?
In the left pane you will see your workbook object modules. This is called the Project Explorer. You will need to create a new module, which you can do by right-clicking inside the Project Explorer > Insert > Module:

enter image description here

Now you can paste the UDF to this new module and you are all set!

Upvotes: 3

Enigmativity
Enigmativity

Reputation: 117027

Slightly longer:

=IF(RC[-1]-INT(RC[-1])>=0.15,CEILING.MATH(RC[-1]),FLOOR.MATH(RC[-1]))

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166156

This is shorter:

=ROUND(B1+0.35,0)

Upvotes: 3

Related Questions