Forum Discussion
Selecting a value by determining if a number lands between two values on a scale
Is it possible to create a formula that is able to find where a number fits in a scale and then selecting the cell associated with that scale/range?
Simplified Example:
Result is 13
Scale is 1-5 =100, 6-10=200, 11-15=300 and so on
I need the formula to check the scale, know it lands between 11-15 and then enter 300 in a cell.
Setup of data:
D2 (down to about D300) = results of other calculations
I2 (down to about I26)= low of a given range
J2 (down to about J26) = high of a given range
K2 (down to about K45) = the value associated with a range (usually a number but can be alphanumeric)
So, something to the effect of:
D2 = number
If D2 falls between I2 and J2, K2's value is entered into cell B2
If D2 falls between I3 and J3, K3's value is entered into cell B2
If D2 falls between I3 and J4, K4's value is entered into cell B2
...and so on until reaching around I26 and J26
Then the same for D3, D4,...,D300
I can get it to check if it lands between a range, but I can't figure out how to make it check the next range up if the logic was false and go until the appropriate range is found
Hopefully this makes sense.
Any help is greatly appreciated.
All the best,
Jeff
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
8 Replies
- JWR1138Iron Contributor
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- JRockCopper Contributor
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
- JWR1138Iron Contributor
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).