different if formulas based on another text cell

[the finance guy]

Hello,
I'm new to posting on the forum, though I've used it extensively to sort out other problems in the past. I do owe you all a thank you for that!

What I'm trying to do now is create a spread sheet to track income for our companies F&I department (financing).

What I need help with is calculating the "Reserve", which is different for each bank and based on values in other cells. The bank name will be listed in one cell, the amount financed in another and the variable that detemines the amount of reserve in yet another.

Here are a few examples.

For "Bank A", for each .25 in the variable column, reserve is equal to 1% of the amount financed. (simple)
For "Bank B", for each .25 up to 1.0 in the variable is equal to 1%, then each additional .5 is equal to 1% of the amount financed.
For "Bank C", 0 is equal to .25% amt financed, .5 is 1%, .75 is 2%, 1.0 is 3%, 1.75 is 4%, etc jumping around quite a bit.

I don't have a problem with drawing out the nested If statements to calculate the reserve, but the problem is that with so many variables with so many banks (about 10 total), I'd be way beyond 7 nested if statements.

Please let me know if there's any way to calculate this in excel, or if I need to abandon it and look into access or another program.

Thanks in advance for any suggestions!

 

[JackDanIce]

Welcome to the board.

I think you need to create your self some tables for each bank with the different variables and their changing gradients and then in the column next to this the percentage of amount financed and then use VLOOKUP functions to each table (per bank) to return your required value.

Do a search on VLOOKUP functions if you don't know what they are; generally they're a better function to use than nested IFs, which in Excel 2003 at least are limited to a depth of 7 maximum.

 

[the finance guy]

Welcome to the board.

I think you need to create your self some tables for each bank with the different variables and their changing gradients and then in the column next to this the percentage of amount financed and then use VLOOKUP functions to each table (per bank) to return your required value.

Do a search on VLOOKUP functions if you don't know what they are; generally they're a better function to use than nested IFs, which in Excel 2003 at least are limited to a depth of 7 maximum.

Thank you JackDanIce.

I came across this in my research and considered it. I understand how the VLOOKUP works in the sense of searching an external table. What I don't understand is how I can get the external table to calculate a nested If statement based on the variables in each row.

Maybe it's possible and I'm just missing how to do it?

 

[JackDanIce]

A VLOOKUP finds the best/exact match in the 1st column of the table against the variable you pass to it. It then returns the value on the same row as the match it finds of the column you specify in the table.

So if you have descending:

0.5 2%
1.0 3%
1.5 4%
2.0 5%

And your function is say:

=VLOOKUP(1.5, A1:B5, 2, 0)

Then the 0 means it looks for an exact match to your search item = 1.5 and returns the value in the 2nd column (in this example) which is 4%. If your search item is 1.0, it would return 3%

Instead of 1.5, you can use a cell reference, e.g. A1

 

[the finance guy]

Thanks again.

I see what you're saying there - each bank has it's own table.

I guess what I'm confused with now is how to pull from 1 of 10 tables depending on text cell that has the bank name in it. Nested If statements are still out, since i'd still be over 7.

Any ideas there?

 

[JackDanIce]

Could have a table that then links to the bank tables - effectively an index table that then links to the individual bank tables. This would be nesting VLOOKUPS though I think there are other solutions....

 

[the finance guy]

OK, so as I'm working through this, this is what I think so far:
The formula itself will need to take the amt financed * a VLOOKUP that returns a percentage.
The initial VLOOKUP will need to narrow it down on a table of banks, which will probably need to VLOOKUP to another table (one for each bank) to find the percentage.

What I don't know is how to push the Variable to the 2nd VLOOKUP to use.
Maybe what I need to do is return the 2nd VLOOKUP "table_array" to the original formula and use a the 2nd VLOOKUP there to calculate.

Hmmm... I think I'm getting more confused.

Anybody have any better workarounds?

 

[the finance guy]

OK, that's not working.
when I try"=VLOOKUP(J3,(VLOOKUP(G3,Banks!A2:B11,2,FALSE)),2,TRUE)"
where the second lookup should return the table array D2:E8, it returns it in quotations and causes an error.

Back to the drawing board....

 

[the finance guy]

Ok, so I got this resolved.

I used the Concatenate work around, then VLOOKUP Tables.

Basically a 2nd sheet with tables for each of the banks, then the Concatenate checks the bank name with a series of IF statements. When it finds the right bank, it uses VLOOKUP to compare the variable to the correct table and find the right output and adds this to the Concatenate text string. The if statements that don't match (the bank), it adds ""(null). Since the Concatenate function is outputting a text string, for further calculations I use the VALUE function to turn the text string back into a value and go from there.

The Concatenate allows me to more or less "unnest" the If statements, running through a series of individual If statements adding nothing to the non matches, or using my VLOOKUP formula when it does. The VLookup tables are doing the work of calculating out the complicated formulas, which differ from bank to bank.

Thanks for the help, JackDanIce! You definitely helped me work through this.

 

[JackDanIce]

I think you did more yourself than anything I did, expect make a few suggestions/pointers! Glad it's resolved now anyway