The points of interest are equidistant from one another. How do you wish to round in the event of a tie?
Your first figure is 4.22, which is both .23 from 3.99 and 4.45.
I've got most of a function written for this but I'm not sure which way you'd like to go.
=TwoPointsRound(a2,.99,.45)
Public Function TwoPointRound(rngInput As Range, dblPoint1 As Double, dblPoint2 As Double) As Double
Dim lngInteger As Long
Dim lngIndex As Long
Dim dblDecimal As Double
Dim tmp As Double
Dim dict As Object
lngIndex = 1
Set dict = CreateObject("Scripting.dictionary")
lngInteger = Int(rngInput)
dblDecimal = rngInput - lngInteger
On Error Resume Next
With dict
.Add Abs(dblDecimal - dblPoint1), dblDecimal - dblPoint1
.Add Abs(dblDecimal + 1 - dblPoint1), dblDecimal + 1 - dblPoint1
.Add Abs(dblPoint1 - dblDecimal), dblPoint1 - dblDecimal
.Add Abs(dblPoint1 - dblDecimal), dblPoint1 - dblDecimal
.Add Abs(dblDecimal - dblPoint2), dblDecimal - dblPoint2
.Add Abs(dblDecimal + 1 - dblPoint2), dblDecimal + 1 - dblPoint2
.Add Abs(dblPoint2 - dblDecimal), dblPoint2 - dblDecimal
.Add Abs(dblPoint2 - dblDecimal + 1), dblPoint2 - dblDecimal + 1
End With
tmp = 1
For i = 0 To dict.Count - 1
If dict.Keys()(i) < tmp Then
tmp = dict.Keys()(i)
lngIndex = i
End If
Next
TwoPointRound = rngInput - dict.Items()(lngIndex)
End Function
Bookmarks