ok I need help on this formula:

[onsitellc]

ok I need help on this formula:

here is what I am trying to do

G9 = Loan Amount - ( this is open to add data) Example $4,500.00
G10= Interest Rate -
G11= Payment Factor -

what I need to do is have a formula that will take the loan amount and calculate the Interest and payment factor from a set of factors.

Formula 1:

if Loan amount is Greater than 0 but less than 2499.99 then G10 would auto fill to 24.9% or if Loan amount is Greater than 2500 but less than 4,999.99 then then G10 would auto fill at 21.9% or if Loan Amount is Greater Than 4500 but less than 20,000 the G10 would auto fill to 19.99

also need to formulate the same type for the payment factor

Formula 2:

if Loan amount is Greater than 300 but less than 6499.99 then G11 would auto fill to .00929 or if Loan amount is Greater than 6500 but less than 10,999.99 then then G11 would auto fill at .007606 or if Loan Amount is Greater Than 11,000 then G11 would auto fill to .006321

please help I thought i almost had it and some how changed something now getting a false return..... Ughhhhh

please email me at scottnapier@ocbmail.com

Thanks

 

[GNB]

Easiest way to do this would be to set up two tables with your hurdles and rates and do a vlookup on your input.

Tables would look like this:

Excel Workbook
	B	C
1	Rate Hurdle	Rate
2	0	24.90%
3	2500	21.90%
4	5000	19.99%
5
6	1500	0.249
7	2600	0.219
Sheet2

Excel 2007

Cell Formulas
Range	Formula
C6	=VLOOKUP(B6,$B$1:$C$4,2,TRUE)
C7	=VLOOKUP(B7,$B$1:$C$4,2,TRUE)

[/B]
These would give the following results:

Excel Workbook
	B	C
6	1500	24.90%
7	2600	21.90%
Sheet2

Excel 2007

This would then be replicated for what you need in your second formula.

HTH

 

[onsitellc]

here is what I am trying to do

G9 = Loan Amount - ( this is open to add data) Example $4,500.00
G10= Interest Rate -
G11= Payment Factor -

what I need to do is have a formula that will take the loan amount and calculate the Interest and payment factor from a set of factors.


Formula 1

if Loan amount is Greater than 0 but less than 2499.99 then G10 would auto fill to 24.9% or if Loan amount is Greater than 2500 but less than 4,999.99 then G10 would auto fill at 21.9% or if Loan Amount is Greater Than 4500 but less than 20,000 the G10 would auto fill to 19.99

IF G10 <= 2499.99, return 24.9
IF G10 > 0 but <= 2499.99, return 24.9
IF G10 > 2500 but <= 4499.99, return 21.9
IF G10 < 4500, return 19.99
=IF((G10)<=0,24.9,IF(AND((G10)>0,(G10)<=2499.99),24.9,IF(AND((G10)>2500,(G10)<=14),21.9,IF(AND ((G10 <4500),19.99))))
also need to formulate the same type for the payment factor


Formula 2

if Loan amount is Greater than 300 but less than 6499.99 then G11 would auto fill to .00929 or if Loan amount is Greater than 6500 but less than 10,999.99 then G11 would auto fill at .007606 or if Loan Amount is Greater Than 11,000 then G11 would auto fill to .006321
IF G10 > 300 but <= 6,499.99, return .00929
IF G10 > 6500 but <= 10999.99, return .007606
IF G10 < 11,000, return .006321
=IF((G10)<=300,000929,IF(AND((G10)>6,499.99,(G10) …………….Ughhhhhhhhh

if you give me an email i can forward you the spreadsheet so you can view what i am doing......

Thanks for your help
Scott

 

[Sanjeev1976]

Use :

G10 =IF(OR(G9="",G9=0),0,IF(AND(G9>0,G9<2500),24.9%,IF(AND(G9>=2500,G9<4500),21.9%,IF(AND(G9>=4500,G9<20000),19.99%,"Out of range"))))

G11=IF(OR(G9="",G9=0),0,IF(AND(G9>300,G9<6500),0.00929,IF(AND(G9>=6500,G9<11000),0.007606,IF(G9>=11000,0.006321))))

 

[onsitellc]

Just wanted to say thanks! I did a little tweaking and it working great.....

Headache is going away now! (LOL)

Again thanks so much for looking what I was trying to do and providing help!

Would love to learn more.....

Thanks again
Scott Napier
Onsite Collection Bureau LLc
Massillon, Ohio 44648
(855) 872-9622

 

[onsitellc]

OK SORRY I HAVE ONE MORE ISSUE:

HOW CAN MY VALIDATION LIST TO CALCULATE THE % WITH OUT THE STATE AND ONLY SHOW THE PERCENTAGE SO IT CAN BE ADDED INTO A CALCULATION.

IT MAY BE SIMPLE BUT MY BRAIN IS FRIED!!!!

<table style="width: 456px; height: 410px;" border="0" cellpadding="0" cellspacing="0"><col style="width: 56pt;" width="74"> <tbody><tr style="height: 24pt;" height="32"> <td class="xl24" style="height: 24pt; width: 56pt;" width="74" height="32">24.9%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">21.9%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">19.99%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">9.9%</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">8.5% (AK)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">8.5% (AR)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">18.% (CT)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">17.8% (FL)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">12.8% (MD)</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">8.5% (MN)</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl22" style="height: 13.5pt;" height="18">18.% (NE)</td> </tr> <tr style="height: 14.25pt;" height="19"> <td class="xl22" style="height: 14.25pt;" height="19">17.8% (ND)</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl22" style="height: 13.5pt;" height="18">8.5% (WV)

FOR INSTANCE THEY WILL ENTER THE LOAN AMOUNT AND SELECT THE DROP DOWN TO SELECT INTEREST RATE BUT i HAVE A FEW SELECTED STATES THAT FALL IN TO A SECURED RATE.
</td> </tr> </tbody></table>

 

[onsitellc]

Or is there a way to add a radio button when it is checked that it will over ride the 9.9 and allow it to point to a cell to add the preferred rate???

That would be the best option, or either one.......