CODEWITHSUNDEEP
excelformula
Fazzette
Fazzette

Reputation: 1

complex rounding decimals in excel spreadsheet

Have created formula for example =(E2+(G2*37))/290 which returns decimals based on data entered but can't work out how to round the answer to get the following:

  1. If 0 to 0.39 round to 0
  2. If 0.4 to 0.89 round to 0.5
  3. If 0.9 to 1.39 round to 1
  4. If 1.4 to 1.89 round to 1.5
  5. If 1.9 to 2.39 round to 2 etc

Hope my question makes sense. Thanks

Upvotes: 0

Views: 5518

Answers (3)

barry houdini
barry houdini

Reputation: 46331

Your custom rounding is simply rounding to the nearest 0.5.....but with an "offset" of 0.15. With value to be rounded in A1 you can do that with a simple formula, i.e.

=ROUND(A1*2-0.3,0)/2

or with your calculation in place of A1 that becomes

=ROUND((E2+G2*37)/290*2-0.3,0)/2

Upvotes: 1

Alex P
Alex P

Reputation: 12489

You could define a VBA function to do this for you:

Public Function CustomRound(number As Double)
    Dim result As Double, fraction As Double

    fraction = number - Int(number)

    If number <= 0.39 Then
        result = 0
    ElseIf fraction >= 0.4 And fraction <= 0.9 Then
        result = Int(number) + 0.5
    Else
        result = Round(number, 0)
    End If

    CustomRound = result
End Function

You would call this as follows:

=CustomRound((E2+(G2*37))/290)

Example:

=CustomRound(0.23) // 0
=CustomRound(1.58) //1.5
=CustomRound(2.12) //2

Upvotes: 0

digEmAll
digEmAll

Reputation: 57210

It's a bit convoluted but this should work:

=IF(A1 + 0.1 - ROUNDDOWN(A1+0.1;0) < 0.5; ROUNDDOWN(A1+0.1;0); ROUNDDOWN(A1+0.1;0) + 0.5)

where A1 is the cell you want to round.

e.g.
enter image description here

N.B.

  • This works only for positive numbers.
  • Ranges are actually:
    [0, 0.39999999...] [0.4 , 0.8999999...] ...
    or equally:
    [0, 0.4) [0.4 , 0.9) ...

Upvotes: 0

Related Questions