If Statements with multiple criteria options

[osscie3]

Good morning all,

I'm trying to build a lending price model for my boss. Basically one that can determine the interest rate based on some inputs.

Here are the criteria -

1. Credit score - which I have defined as 720+, 719-690, etc
2. The year the vehicle was made - 2015-2017, 2012-2014, etc
3. The term of the loan - 48, 64-75, etc.

Depending on what each criteria has been selected will determine an interest rate.

Is this something I can do with formulas or am I going to have to write a macro instead? I'm not shy to macros as I've written a few over 1,000 lines of code. It may even be easier to go the macro route. My goal is to make it as simplistic I can for my boss. Thanks for your opinions!

 

[Joe4]

It really depends upon how all the criteria interact with each other and how many levels of each that you have.
Can you explain, in plain English, that given those three criteria, how you would determine the rate now?

 

[osscie3]

Absolutely. Luckily, it's a pretty straightforward table. The only problem is, it has 3 criteria in it rather than just 2 columns. So for example, the table is laid out as follows - (I'm putting in dummy interest rates as it's proprietary)

[TABLE="width: 500"]
<tbody>[TR]
[TD]Auto Year[/TD]
[TD]Term[/TD]
[TD]720+[/TD]
[TD]719-690[/TD]
[TD]689-640 [/TD]
[TD]600-639[/TD]
[/TR]
[TR]
[TD]2015-17[/TD]
[TD]up to 48[/TD]
[TD]1.00%[/TD]
[TD]2.00%[/TD]
[TD]3.00%[/TD]
[TD]4.00%[/TD]
[/TR]
[TR]
[TD]2015-17[/TD]
[TD]49-63[/TD]
[TD]1.10%[/TD]
[TD]2.10%[/TD]
[TD]3.10%[/TD]
[TD]4.10%[/TD]
[/TR]
[TR]
[TD]2015-17[/TD]
[TD]64-75[/TD]
[TD]1.20%[/TD]
[TD]2.20%[/TD]
[TD]3.20%[/TD]
[TD]4.20%[/TD]
[/TR]
[TR]
[TD]2012-14[/TD]
[TD]Up to 48[/TD]
[TD]1.30%[/TD]
[TD]2.30%[/TD]
[TD]3.30%[/TD]
[TD]4.30%[/TD]
[/TR]
[TR]
[TD]2012-14[/TD]
[TD]49-63[/TD]
[TD]1.40%[/TD]
[TD]2.40%[/TD]
[TD]3.40%[/TD]
[TD]4.40%[/TD]
[/TR]
</tbody>[/TABLE]

Etc, etc. So it's taking the auto year, then term, then the credit score to produce an interest rate.

 

[Joe4]

Hmmm...

This could be a little tricky. Here is how I would approach it:

Since all your criteria are "ranges of values" instead of single values, for each of your three criteria, write a VLOOKUP to return the range.
So would create three lookup tables, where the first column is the lower limit of each range and the second column is the range value, and sort it by the first column.

For example, your year lookup table may look something like this:

Lower Limit     Range
2006            2006-08
2009            2009-11
2012            2012-14
2015            2015-17

Then, if we named this range "YearRange" (see this if you do not know how to name ranges: http://www.contextures.com/xlNames01.html), we would use this VLOOKUP formula to return which range a year falls into (for a value in cell A1):

=VLOOKUP(A1,YearRange,2,TRUE)

So, do likewise for the other two criteria.

Then, create a BIG lookup table of all your different combinations, and use an INDEX/MATCH function to match on all three range criteria, as shown here:
https://exceljet.net/formula/index-and-match-with-multiple-criteria

 

[thisoldman]

My biggest concern when taking on a project like this, is who is going to maintain the spreadsheet.

<tbody>
[TD="class: xl64"][/TD]
[TD="class: xl64"]A[/TD]
[TD="class: xl64"]B[/TD]
[TD="class: xl64, width: 64"]C[/TD]
[TD="class: xl64, width: 64"]D[/TD]
[TD="class: xl64, width: 64"]E[/TD]
[TD="class: xl64, width: 64"]F[/TD]

[TD="class: xl64"]1[/TD]
[TD="class: xl64"]Criteria[/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]

[TD="class: xl64"]2[/TD]
[TD="class: xl64"]Auto Year[/TD]
[TD="class: xl64"]2015-17[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64"]3[/TD]
[TD="class: xl64"]Term[/TD]
[TD="class: xl64"]49-63[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64"]4[/TD]
[TD="class: xl64"]Credit Score[/TD]
[TD="class: xl64"]689-640[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64"]5[/TD]
[TD="class: xl64"] Rate
[/TD]
[TD="class: xl65, align: right"] 3.10%
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64"]6[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]

[TD="class: xl64"]7[/TD]
[TD="class: xl64"]Auto Year[/TD]
[TD="class: xl64"]Term[/TD]
[TD="class: xl64"]720+[/TD]
[TD="class: xl64"]719-690[/TD]
[TD="class: xl64"]689-640[/TD]
[TD="class: xl64"]600-639[/TD]

[TD="class: xl64"]8[/TD]
[TD="class: xl64"]2015-17[/TD]
[TD="class: xl64"]up to 48[/TD]
[TD="class: xl65, align: right"]1.00%[/TD]
[TD="class: xl65, align: right"]2.00%[/TD]
[TD="class: xl65, align: right"]3.00%[/TD]
[TD="class: xl65, align: right"]4.00%[/TD]

[TD="class: xl64"]9[/TD]
[TD="class: xl64"]2015-17[/TD]
[TD="class: xl64"]49-63[/TD]
[TD="class: xl65, align: right"]1.10%[/TD]
[TD="class: xl65, align: right"]2.10%[/TD]
[TD="class: xl65, align: right"]3.10%[/TD]
[TD="class: xl65, align: right"]4.10%[/TD]

[TD="class: xl64"]10[/TD]
[TD="class: xl64"]2015-17[/TD]
[TD="class: xl64"]64-75[/TD]
[TD="class: xl65, align: right"]1.20%[/TD]
[TD="class: xl65, align: right"]2.20%[/TD]
[TD="class: xl65, align: right"]3.20%[/TD]
[TD="class: xl65, align: right"]4.20%[/TD]

[TD="class: xl64"]11[/TD]
[TD="class: xl64"]2012-14[/TD]
[TD="class: xl64"]Up to 48[/TD]
[TD="class: xl65, align: right"]1.30%[/TD]
[TD="class: xl65, align: right"]2.30%[/TD]
[TD="class: xl65, align: right"]3.30%[/TD]
[TD="class: xl65, align: right"]4.30%[/TD]

[TD="class: xl64"]12[/TD]
[TD="class: xl64"]2012-14[/TD]
[TD="class: xl64"]49-63[/TD]
[TD="class: xl65, align: right"]1.40%[/TD]
[TD="class: xl65, align: right"]2.40%[/TD]
[TD="class: xl65, align: right"]3.40%[/TD]
[TD="class: xl65, align: right"]4.40%[/TD]

</tbody>

Formula in B5 is an array formula, Use Ctrl+Shift+Enter after typing in the formula, not just Enter.
=INDEX(C8:F12, MATCH(B2 & B3, A8:A12 & B8:B12, 0), MATCH(B4, C7:F7, 0))

 

[osscie3]

Hmmm...

This could be a little tricky. Here is how I would approach it:

Since all your criteria are "ranges of values" instead of single values, for each of your three criteria, write a VLOOKUP to return the range.
So would create three lookup tables, where the first column is the lower limit of each range and the second column is the range value, and sort it by the first column.

For example, your year lookup table may look something like this:

Lower Limit     Range
2006            2006-08
2009            2009-11
2012            2012-14
2015            2015-17

Then, if we named this range "YearRange" (see this if you do not know how to name ranges: http://www.contextures.com/xlNames01.html), we would use this VLOOKUP formula to return which range a year falls into (for a value in cell A1):

=VLOOKUP(A1,YearRange,2,TRUE)

So, do likewise for the other two criteria.

Then, create a BIG lookup table of all your different combinations, and use an INDEX/MATCH function to match on all three range criteria, as shown here:
https://exceljet.net/formula/index-and-match-with-multiple-criteria

Ok so I've set up my vlookups, rearranged my table to make it a little more computer friendly as follows -

[TABLE="width: 500"]
<tbody>[TR]
[TD]720+
[/TD]
[TD]2015 - 2017
[/TD]
[TD]Up to 48
[/TD]
[TD]1.00%
[/TD]
[/TR]
[TR]
[TD]720+
[/TD]
[TD]2015 - 2017
[/TD]
[TD]49 - 63
[/TD]
[TD]2.00%
[/TD]
[/TR]
[TR]
[TD]720+
[/TD]
[TD]2012 - 2014
[/TD]
[TD]Up to 48
[/TD]
[TD]3.00%
[/TD]
[/TR]
[TR]
[TD]720+
[/TD]
[TD]2012-2014
[/TD]
[TD]49 - 63
[/TD]
[TD]4.00%
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Etc.

I'm getting a VALUE error in my index match formula and now I'm stuck. Trying to debug but I'm not as familiar with the debug process on the front end. I tried formatting everything in my table to be the same. The values in my vlookups vs my table seem to be an exact match (I've tried different combinations to ensure it's not one column of my table throwing it off). My formula is -

[=INDEX(Sheet3!L24:L76,MATCH(1,(Sheet3!U12=Sheet3!I24:I76)*(Sheet3!U13=Sheet3!J24:J76)*(Sheet3!U14=Sheet3!K24:K76),0))/CODE]

I clicked on "Show Calculation Steps" and it underlines Sheet3!I24:I76. Right before that, it displays "720+" which tells me it's getting U12. So it's something with the columns in my table. Maybe I should format my table as a table rather than just raw data?

Disregard, I did what thisoldman suggested at the end hit Control+Shift+Enter and it worked beautifully.

Thanks for everyone's help!

 

[osscie3]

[TABLE="width: 500"]
<tbody>[TR]
[TD]720+[/TD]
[TD]2015 - 2017[/TD]
[TD]Up to 48[/TD]
[TD]3.00%[/TD]
[/TR]
[TR]
[TD]720+[/TD]
[TD]2015 - 2017[/TD]
[TD]49 - 63[/TD]
[TD]4.00%[/TD]
[/TR]
</tbody>[/TABLE]
Spoke too soon. So it will return a rate. It's just not the correct rate. It returns the interest rate directly below correct one. Example if the user has 720 credit, year of car is 2015, and term is 40. It should look at the table above and display the 3% rate, however it displays 4% instead. No idea why. Thoughts?

 

[osscie3]

Figured it out. Had to tweak the formula slightly. Thanks again everyone!