• Status: Solved
• Priority: Medium
• Security: Public
• Views: 264

# round down number to a list of numbers

I have a spreadsheet that contains some values in column A.  I need to round those numbers down.  The catch is that I need to round them down to a set of numbers in column B.  I listed the expected results in column D.  I need help coming up with a formula to do the calculation.  Thank you!
rounddownlist.xlsx
0
colonialiu20
1 Solution

Commented:
Assuming column B is in ascending order per your sample, try:

=LOOKUP(2,1/(\$B\$2:\$B\$36<=A2),\$B\$2:\$B\$36)

copied down

this looks for the last time a number in column B is less than or equal to the value in column.  This would be equivalent to the rounded down value of column A according to column B options.
0

Older than dirtCommented:
The above probably works and if so it's better but you can also add a module to your workbook and add this UDF

``````Function RoundToList(r As Variant) As Variant
Dim lngRow As Long
Dim lngLastRow As Long

lngLastRow = Range("B65536").End(xlUp).Row

For lngRow = lngLastRow To 2 Step -1
If Cells(lngRow, 2).Value < r Then
RoundToList = Cells(lngRow, 2).Value
Exit For
End If
Next

End Function
``````

Then in D2 put =RoundToList(A2) and copy down.
0

Commented:
I don't think your LOOKUP needs to be as complicated as NBVC suggests -  this version should do what you want in D2 copied down:

=LOOKUP(A2,B\$2:B\$36)

regards, barry
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.