SOLVED

Selecting a value by determining if a number lands between two values on a scale

Copper Contributor

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

8 Replies

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.

User defined functions are custom functions used in the same way as Excel's built in functions (sum, if, lookup, etc.). UDFs are written in VBA (Visual Basic for Applications, the coding component of Excel). In order to use a UDF the code must be in the VBA of a workbook.

To add a UDF to a workbook, with the workbook open hit alt-F11, insert a module (insert, module). You should then see module 1. Copy the code I posted earlier into the module. Close the VBA Editor (alt+Q). Now you can use the UDF in a cell on the workbook (ScaleReturn). In order to save the VBA code in the workbook (ie. To have the function continue to work) you must save as a macro enabled workbook (What you posted was not a macro enabled workbook so you'd need to use save as and change the file type to macro enabled workbook). Hopefully this makes sense.

As I said before the 2 arguments for the function are the cell you want to check against your ranges (C2 on the sheet I believe) and the range for the lower end of your ranges ($I$2:$I$22). Full function being =ScaleReturn(C2,$I$2:$22).

Side note, have you learned what the $ in a function means? If not Google absolute vs relative references in Excel as this is very handy thing to understand.

Another quick note, your 2 number ranges start and end at the same numbers, while I really don't think this matters in your application as it does not look like you'd ever end up with an exact number, it does leave ambiguity on which range you'd want an exact match to fit into (does it match the highest number of one range or the start number of the range one row down?). Currently the way this is written it will match the lower number in your 2 number range further down the column, ie. return the higher value in your example. If you want this to return the lower value let me know and we can switch it. Like I say, looking at your column C2 values or looks really unlikely we'd be working with any whole number let alone one that matched the top/bottom of your 2 number range.

You can definitely stop reading here if you just want to get it working. Keep reading if you'd like to read how it works.

'The first line of code defines the function name (ScaleReturn, you can change this to whatever you like as long as the name does not contain spaces, special characters (_ is ok though), start with a number or the word is reversed by Excel for another use), if you were to change the name all other instances of ScaleReturn would need to be changed to the same thing. The first line also tells Excel what the arguments of the function are (In our example InputValue was C2 and LowerBound was I2:I22. InputValue and LowerBound are both variables.

Function ScaleReturn(InputValue, LowerBound As Range)

Dim Cell As Range 'Dim is short for dimension, we are telling excel that we want to use Cell as a variable with the type range (ie. A1, B2:B20, etc. As opposed to a different type of variable such as an integer or string.)

For Each Cell In LowerBound 'Here we are starting a loop, Everything after this line to the line reading: next cell, will run once for each worksheet cell in our range named LowerBound, ie runs once for I2, once for I3, etc. up to and including I22.

If InputValue >= Cell.Value And InputValue <= Cell.Offset(0, 1).Value Then 'This says if the value stored in the variable InputValue (Value in C2 for the example) is greater than or equal to the value of the cell I2 (For the first iteration of the loop, I3 for the next and so on) and if the value in C2 is less than or equal to the value in the cell one to the right (.Offset(0,1) means one cell to the right) then run the code between this line and the line End If

ScaleReturn = Cell.Offset(0, 2).Value 'We are storing the value found in the cell 2 to the right of cell in the current iteration of the loop (ie. The number we want out function to return), if C2 is within I2 and J2 this would be the cell 2 to the right of i2, so k2, this line will never run until we've met the condition of the If line above

End If 'this denotes the end of our if code

Next Cell 'takes us back to the beginning of the for each cell loop but for the next cell in the range (ex. if we just finished running the code for I2, start again but for I3)

If ScaleReturn = 0 Then 'Another if, I didn't want the function to return 0 if no matches for your 2 number ranges were met so we check if it's 0 and replace with "" (which is a blank cell) on the next line. You did not request this but I thought it would make more sense to return nothing than 0 if no matching 2 number range was found, thought it was possible one of the values you wanted to return could be 0, wanted to avoid any ambiguity there)

ScaleReturn = ""

End If 'close this if statement

End Function 'A UDF always ends with this line, lets Excel know when to stop running code, whatever is sitting in the variable ScaleReturn when Excel gets to this line is what the function is going to return on your worksheet.

Thank you again for your guidance! Leveraging User Defined Functions in the future will certainly be a valuable asset while trying to refine this old spreadsheet into a more useful tool. I will have to experiment if there are any adverse conditions created by converting the main workbook into a macro enabled version. Currently the macros I created through the record macro function run with buttons added to the quick access - I was unaware of the different version of the spreadsheets. Being able to build them into the book itself seems like something that might be a good quality of life change.

 

Another solution that was recommended was use of the lookup function. In this case this function copied down:

 =LOOKUP(D2,$I$2:$I$22,$K$2:$K$22)

 

I sincerely appreciate your guidance! 

 

best response confirmed by JRock (Copper Contributor)
Solution

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

 

1 best response

Accepted Solutions
best response confirmed by JRock (Copper Contributor)
Solution

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

 

View solution in original post