Help needed with "IF" function

  • Thread starter Thread starter lsmith
  • Start date Start date
L

lsmith

I have an "open ended" calculation I need help with. If I have a
specific loan amount, say $100,000.00, and I need to figure out the
calculation for title work costs are for the following, what would the
calculation be?

$2.25 per $1,000 from $10mm upward?

(Bernie Dietrich, if you're out there, write back since you helped me
last time!). Many thanks.:)
 
If your loan amount is in cell A1, in cell B1 use =IF(A1>=10000000,
A1/1000*2.25, ""). This will leave the cell blank if the loan amount is less
than $1,000,000.
 
I need verfication of the formulas for the THREE SEPARATE queries below.
I've received a previous answer, but the formula given combined the two
examples. I cannot have ONE formula for these queries, they HAVE to be
separate. They also need to link to the original loan amount, so if
this changes, the other amounts will change in accordance to that
original amount.

If the orginal loan amount is $100,000.00, what would the formula be to
get the amount for:

(1) $5.75 per $1,000 from $1,000 to $100,000 = ____?____

for this amount?

(2) $5.00 per $1,000 from $100,000 to $1,000,000 = ____?____

for this amount?

(3) $3.000 per $1,000 from $1,000,000 to $10,000,000 = ____?____

:confused:
 
I have quickly put together the the fomula below which seems to provide for
all three scenarios in a single cell (you'll need to verify it, I did this
quickly), assuming the following conditions.

1. For 10,000 to 100,000 there is a straight fee of $5.75 per $1,000.
2. For 100,000 to 1,000,000 the fee includes the maximum fees for #1 plus a
fee of $5.00 per $1,000 for amounts in excess of $100,000.
3. For 1,000,000 to 10,000,000 the fee includes the maximum fees in #2 plus
a fee of $3.00 per $1,000 for amounts in excess of $1,000,000 but less than
or equal to $10,000,000.

If you need calculations in separate cells, you can cut and paste the parts
need from the equation.

Here is my formula:
=IF(AND(1000<=A1,A1<100000),A1/1000*5.75,IF(AND(100000<=A1,A1<1000000),(10*5.75)+(A1-10000)/1000*5,IF(AND(1000000<=A1,A1<=10000000),(10*5.75)+(999.99999*5)+(A1-1000000)/1000*3,"")))
 
Dear SVC: This is part of the problem I'm having. As a new user, I
don't know WHICH parts to take out of the equation for each separate
query. I need three separate formulas.
 
Assuming you have a header row with cell A1 labeled Amount and Cell B1
labeled fee, Cell C1 labeled Range of loan (c2= 1,000 to 10,000, c3=10,000 to
1,000,000 and c4=1,000,000 to 10,000,000), try the following:

Enter the loan amount in Cell A2.

In Cell B2 enter this formula:
=IF(A2>=10000,57.5,IF(AND(A2>=1000,A2<10000),A2/1000*5.75,""))

In Cell B3 enter this formula: =IF(A2>1000000,
500,IF(AND(A2>=100000,A2<1000000),(A2-100000)/1000*5,""))

In Cell B4 enter this formula:
=IF(AND(A2>=1000000,A2<=10000000),(A2-1000000)/1000*3,"")

Cell B2 will show the fee for the portion of the loan from 1,000 to 10,000;
Cell B3 will show the fee for the portion of the loan from 10,000 to
1,000,000; and Cell B4 will show the fee for the portion of the loan from
1,000,000 to 10,000,000. You can create a sum in cell B5 to give you the
total fee.
 
I just noted an error for cell B3--the correct formula is: =IF(A2>1000000,
4950,IF(AND(A2>=10000,A2<=1000000),(A2-10000)/1000*5,""))
 
Sorry about this. Another correction to cell B3 and that should be it:
=IF(A2>1000000, 4999.5,IF(AND(A2>=10000,A2<=1000000),(A2-10000)/1000*5,""))
 
Back
Top