Forum Discussion
Selecting a value by determining if a number lands between two values on a scale
You may use formula like
=INDEX($K$2:$K$40,MATCH(1,INDEX(($D2>=$I$2:$I$40)*($D2<=$J$2:$J$40),0),0))
Please see attached
Hi, There is a probably a way to do this with an array formula using built in functions but it's pretty easy to do with a user defined function if that is a viable option for you. 2 Arguments, InputValue Being D2 and LowerBound being $I$2:$I$26 for your example. So =ScaleReturn(D2,$I$26) in B2 for your example. Returns "" if the number does not fall between any of your ranges.Let me know if I misunderstood what you are after. Thanks.
Function ScaleReturn(InputValue, LowerBound As Range)
Dim Cell As Range
For Each Cell In LowerBound
If InputValue >= Cell.Value And InputValue <= Cell.Offset(0, 1).Value Then
ScaleReturn = Cell.Offset(0, 2).Value
End If
Next Cell
If ScaleReturn = 0 Then
ScaleReturn = ""
End If
End Function
Hi there! Thanks for giving this a whirl. This may be the trick, unfortunately, I'm on an elementary level of excel, so I'm not able to follow your directions with any results. But, given my feeble understanding of the process below, I'm not sure how the upper range of the scale would be taken into consideration.
I've hopefully attached a scrubbed version of the sheet I'm working on.
In the attached example, any value in column D between 26,505 and 29,295 would be assigned a 100. 29,295 to 32,025 would be a 200.
The second sheet on the attached workbook shows the goal of the formula.
The goal is to weigh the values in column D against the range (I to J) and assign the proposed value in column C (mistyped as B in my initial post)
Thank you again
If this the intended result? (Attached). Either I'm not completely understanding what you are doing or you've got a few typos on the sheet you did manually) I've got to get off the computer for the day but I can explain how this function works in depth at a later point, Do you understand the concept of User Defined Functions (No slight intended, you said your knowledge is elementary), the code I sent needs to be inserted into a VBA module (I can give you a run down later, or just google how to use a User Defined Function in Excel).
Yes, it was likely typos as I tried to do it quickly. This is also the reason I wanted to automate it and remove operator error.
This is fantastic. At first glance this looks like what I needed. I will do some research on User Defined Functions and attempt to replicate your result.
Thank you!
I'll post an explanation later tonight.