Complicated (for me) excel issue

[bill3kradio]

Here's my problem:

I have a grid to determine interest rates. The are add ons to the rate that are dependent on two factors - the credit score and the Loan to Value percent or LTV, both of which are shown in terms of ranges (720-739, etc). See the screen shot below.

I have already written formulas for converting the score and LTV to the ranges as they appear on the sheet (ie, if you type in a score of 722, the formula converts it to the range of 720-739.

What I need to do is this - when the score and LTV are input and determine which set of add ons come into play, I need the sheet to take those add-ons, identified by an x next to it at the top of the page, and add them to the base interest rate, giving me the final rate. Again, see below to make this more clear.

I've thought of using VLookup, but I don' think that would be applicable here.

What's the best method, and can you give me an example of what a formula might look like?

Thanks,

Bill

<table x:str="" style="border-collapse: collapse; width: 534pt;" border="0" cellpadding="0" cellspacing="0" width="714"><col style="width: 78pt;" width="104"> <col style="width: 56pt;" width="75"> <col style="width: 64pt;" width="85"> <col style="width: 56pt;" span="6" width="75"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 78pt;" height="17" width="104">
</td> <td colspan="3" style="width: 176pt;" width="235">Loan Level Pricing Adjustments</td> <td class="xl29" style="width: 56pt;" width="75">
</td> <td class="xl29" style="width: 56pt;" width="75">
</td> <td class="xl29" style="width: 56pt;" width="75">
</td> <td class="xl29" style="width: 56pt;" width="75">
</td> <td class="xl29" style="width: 56pt;" width="75">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Base Rate</td> <td class="xl29" x:num="">6.25</td> <td class="xl29" x:num="" x:fmla="=C4">6.25</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Credit Score</td> <td class="xl35" x:num="">741</td> <td class="xl29" x:fmla="=IF(C5<621,"<620",IF(C5<640,"620-639",IF(C5<660,"640-659",IF(C5<680,"660-679",IF(C5<700,"680-699",IF(C5<720,"700-719",IF(C5<740,"720-739",IF(C5>739.99,">740","0"))))))))">>740</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>LTV</td> <td class="xl29" x:num="">60</td> <td class="xl29" x:fmla="=IF(C6<60.01,"<60.00",IF(C6<70.01,"60.01-70.00",IF(C6<75.01,"70.01-75.00",IF(C6<80.01,"75.01-80.00",IF(C6<85.01,"80.01-85.00",IF(C6<90.01,"85.01-90.00",IF(C6<95.01,"90.01-95.00",IF(C6>95,"Check LTV","0"))))))))"><=60.00</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl36" style="height: 12.75pt;" height="17">Add-ons</td> <td>
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>All</td> <td class="xl29">x</td> <td class="xl29" x:num="">-0.25</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Cash Out</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Invest</td> <td class="xl29">x</td> <td class="xl29" x:num="">1.5</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>2 unit</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>3-4 unit</td> <td class="xl29">x</td> <td class="xl29" x:num="">1</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Final Rate</td> <td class="xl29">
</td> <td class="xl29" x:num="" x:fmla="=C4+D9+D10+D12+D11+D13">8.5</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" height="17">
</td> <td class="xl30" style="border-left: medium none;">
</td> <td colspan="7" class="xl24" style="border-left: medium none;">LTV RATIOS</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt;" height="17">CREDIT SCORE</td> <td class="xl28" style="border-left: medium none;">Add-ons</td> <td class="xl33" style="border-top: medium none; border-left: medium none;"><=60.00</td> <td class="xl33" style="border-top: medium none;">60.01-70.00</td> <td class="xl33" style="border-top: medium none;">70.01-75.00</td> <td class="xl33" style="border-top: medium none;">75.01-80.00</td> <td class="xl33" style="border-top: medium none;">80.01-85.00</td> <td class="xl33" style="border-top: medium none;">85.01-90.00</td> <td class="xl24" style="border-top: medium none;">90.01-95.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">>740</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">all</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">-0.25</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">>740</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Cash Out</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.25</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="0.375">0.375</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="0.375">0.375</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">>740</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Investment</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">2.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">2.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">>740</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">2-unit</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">>740</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">3-4 unit</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl31" style="border-top: medium none;">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl32" style="height: 12.75pt;" height="17">
</td> <td class="xl32" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">720-739</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">all</td> <td class="xl34" style="border-left: medium none;" x:num="">-0.25</td> <td class="xl34" style="border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-left: medium none;" x:num="">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">720-739</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Cash Out</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="0.125">0.125</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="0.125">0.125</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="0.375">0.375</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">720-739</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">Investment</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">2</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">2.25</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">2.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">720-739</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">2-unit</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">0.5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="border-top: medium none; height: 12.75pt;" height="17">720-739</td> <td class="xl26" style="border-top: medium none; border-left: medium none;">3-4 unit</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl34" style="border-top: medium none; border-left: medium none;" x:num="">1</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> <td class="xl34" style="border-top: medium none; border-left: medium none;">n/a</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl31" style="border-top: medium none;">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> <td class="xl29">
</td> </tr> </tbody></table>

-

 

[JustinWWolcott]

sumproduct I'd think would be the quickest way; but you're gonna have to clean up the table a little

 

[dafan]

Excel Workbook
	A	B	C	D
1		Loan Level Pricing Adjustments
2
3
4		Base Rate	6.25	6.25
5		Credit Score	741	>740
6		LTV	60
7
8	Add-ons
9		All	x	-0.25
10		Cash Out
11		Invest	x	1.5
12		2 unit
13		3-4 unit	x	1
14
15		Final Rate		8.5
Sheet1

 

[DonkeyOte]

Bill, you may find your life is made a lot easier if you create a unique field in your data table based on concatenation of credit range (A) and Add-ons (B).

Let's say we add column of data in J (concatenation of A:B) and name your range "datatable".

Then we can use this new column to retrieve your rates etc without need for SUMPRODUCT / Array formulae.

See below for ex. of Data Table setup
(Note it is important that each Add-on is listed in identical format to the summary table else you won't be able to find the relevant values)

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
4	CREDIT SCORE	Add-ons		60.01-70.00	70.01-75.00	75.01-80.00	80.01-85.00	85.01-90.00	90.01-95.00	CONCAT
5	>740	All	-0.25	0	0	0	0	0	0	a>740 :bAll
6	>740	Cash Out	0	0	0	0.25	0.375	0.375	n/a	a>740 :bCash Out
7	>740	Investment	1.5	1.5	1.5	2	2.5	2.5	n/a	a>740 :bInvestment
8	>740	2-unit	0.5	0.5	0.5	0.5	0.5	0.5	0.5	a>740 :b2-unit
9	>740	3-4 unit	1	1	1	1	n/a	n/a	n/a	a>740 :b3-4 unit
10
11	720-739	All	-0.25	0	0	0	0	0	0	a720-739 :bAll
12	720-739	Cash Out	0	0.125	0.125	0.375	0.5	0.5	n/a	a720-739 :bCash Out
13	720-739	Investment	1.5	1.5	1.5	2	2.25	2.5	n/a	a720-739 :bInvestment
14	720-739	2-unit	0.5	0.5	0.5	0.5	0.5	0.5	0.5	a720-739 :b2-unit
15	720-739	3-4 unit	1	1	1	1	n/a	n/a	n/a	a720-739 :b3-4 unit
Sheet1

Then here is your summary table (again note to ensure the Add-ons are identical values in text to those in your datatable)

Excel Workbook
	A	B	C	D
20		Loan Level Pricing Adjustments
21
22		Base Rate	6.25	6.25
23		Credit Score	741	>740
24		LTV	60
25
26	Add-ons
27
28		All	-0.25
29		Cash Out	0
30		Investment	1.5
31		2-unit	0.5
32		3-4 unit	1
33		Final Rate		9.00
Sheet1

Note: I got result of 9 rather than 8.5 as per your example so I may be missing something but this should point you in the right direction.

HTH

 

[DonkeyOte]

Ha, dafan has just posted which makes sense to me... I think the prior posts (mine included) had misinterpreted the requirements... doh.

 

[dafan]

I had to read it a couple of times too but I think the only issue is summing amounts where an 'x' is present.

 

[DonkeyOte]

Stefan, it may (actually) end up being a hybrid of both

 

[dafan]

Nice approach. You get 9 because you are not using 'x' next to the cells in the summary.
In this case you are basically copying C5:C9 from the datatable. You get 9 instead of 8.5 cuz in the example 2-unit is not summed because it has no 'x' next to it.

 

[DonkeyOte]

Yes, once I saw your post re: SUMIF(...) it made sense... I thought OP wanted to return values instead of x (and values in D were to show what the values should be)... oops... hey, it is like 6:50am over here...

 

[bill3kradio]

Thanks to all for the help and suggestions. The thing I'm struggling with is that there are so many combinations of credit score and LTV ratios, combined with the add-ons for each.

I need to have the sheet recognize that subset of add-ons unique to the credit score/LTV combo. For example, if the score is 669 and the LTV is 80, the add ons would be different than the example I used originally, so the sheet has to recognize that.

Thanks again for the help. I'll give the suggestions a try.

If you think of anything else, I'd appreciate letting me know.

Bill