Increase value in cell based on range

[sharp_1]

I have a rate sheet attached for real estate title fee and I want to be able to have excel calculate and increase rate based on an offer price.

Example: If I have an Offer for $300,000 my rate would be $1,030.

Problem I am having is the rates change based on price.

Base rate is $375 up to $15,000 purchase price then raises by $3 per 1000 up to $100,000. After $100,001 the rate raises by $2 per 1000...

$15,000 to $100,000 = $3 per $1,000
100,001 to 500,000 = 2 per 1,000
500,001 to 1,000,000 = 1 per 1,000

Thank you for any help you can provide.

title rates.pdf

[etaf]

so is it simply the rate that applies or do you have it stepped

ie

if 200,000
then
200,000 = 200 * 2
or is it 375+ then for the next 85,000 = 85*$3 and then the last 100,000 = 100* $2

so 375+ 255 + 200

also what do you do with
199,999
is that round down to
199,000


assuming the value is not below 15,000 and we round down

try
=IF(A1>500000, INT((A1-500000)/1000)*1+1430,IF(A1>100000, INT((A1-100000)/1000)*2+630,INT((A1-15000)/1000)*3+375))

[ChemistB]

Here's what I came up with for the compounded rate, just using MIN and MAX

Please Login or Register  to view this content.

[sharp_1]

Thanks etaf and ChemistB for your help.

The only issue I still have with etaf's solution is the base rate of $375...not that I sell anything less then $15k, but I need the lowest number to be $375. If I enter say $13,000 as my price I get $369 and not the $375 min fee.

Great work!!

[etaf]

so whats the rule to get 369 instead of 375 ?
thats $6 less for 2000 less
so does the 375 reduce by $3 per 1000 below

i think this works for those conditions
=IF(A1>500000, INT((A1-500000)/1000)*1+1430,IF(A1>100000, INT((A1-100000)/1000)*2+630,IF(A1>15000,INT((A1-15000)/1000)*3+375,375)))

[sharp_1]

etaf, that's perfect. Anything below the $15,000 now defaults to $375....awesome!! Thank you!!

[etaf]

i would check out a few values and give it a bit of test before putting into production

if it works OK
if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.