[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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
Asked:
colonialiu20
1 Solution
 
NBVCCommented:
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
 
Martin LissRetired ProgrammerCommented:
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

Open in new window


Then in D2 put =RoundToList(A2) and copy down.
0
 
barry houdiniCommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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