• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Proper Formula for Multiple IF statements within a range

L

lfrazier

New Member
I am trying to create a spreadsheet for customers to quote finance payments. I have predefined the spread over prime for these variable rate loans based upon the amount financed. Thus, for finance amounts of $10,000 - 74,999 the spread is 3.99%, $75,000 - 149,999 the spread is 3.69%, $150,000 - 299,999 the spread is 3.29% and over $300,000 the spread is 2.99%. I would like for the customer to input the finance amount and then based upon that amount my cell that contains the spread over prime is filled in to calculate the total interest rate charged. I completed the formula for one range, but am having a problem creating it for all the ranges.


Thanks
 
Hi Ifrazier,


Your case needs a VLookup and not formula to be more effective. The VLookup with "True" option in the end.


First sort your rate of interest in the order of "Amount" in ascending order, as this is determining the rate of interest. Like this:

Amt Rate

10000 3.99%

75000 3.69%

150000 3.29%

300000 2.99%


Now, you can use the formula mentioned below to get the rate of interest.

=VLOOKUP(A8,$A$2:$B$6,2,TRUE)


A8 is referring to finance amount that customer inputs

$A$2:$B$6 refers to table created above

2 refers to column (rate of interest)

True = Sets lookup know that the amounts are sorted and to pick nearest value, and not exact value.


Regards,

Prasad DN
 
Prasad,


Here is my table I am using with Column F being the amount financed and Column G being the Spread over Prime. Cell C8 is where the customer inputs the finance amount. Column 7 is where the Spread over Prime %'s are.


Amount Financed Spread Over Prime

$ 10,000 - 74,999 3.99%

$ 75,000 - 149,999 3.69%

$150,000 - 299,999 3.29%

$300,000 + 2.99%


I tried the following formula =VLOOKUP($C$8,F9:G12,7,TRUE) and the result was #NA.


Could it be because the ranges are TEXT vs #'s in the Amount Financed Column?


Any suggestions?
 
Starting F9 cell put the values exact what I have mentioned, no hyphens required.

For $10,000 - 74,999 just put 10000.

For $75,000 - 149,999 just put 75000

like what is in my post


Also, change 7 to 2 in your formula


Rest assured it will get you result :)
 
PS: Since your table is starting from Column F, F is counted as Col 1 and G as Col 2, that is the reason, I mentioned to change 7 to 2 in your formula.
 
Thanks for your help as I seperated the amount financed ranges into different columns as #'s and then revised the formula accordingly to what you said and it worked.


THANKS again!!
 
You must log in or register to reply here.
Back
Top