Sliding scale formula needed

[kewl_jules]

Hi there,

I have a question with a sliding scale formula. Does anyone know how to create a formula with a sliding scale with the following information

Cost = $50,000 and below ; Fees = $1,500
$100,000; $5,500
$250,000; $13,750
$500,000; $26,000
$1,000,000; $45,000
$2,000,000; $75,000
$3,000,000; $105,000

The formula I needed would be based on a curve graph of the above value. For example a cost of $900,000 will incur a fee between $26,000 and $45,000.

Can anyone help me here?

[K m]

=choose(if(a1<=50000,1,if(a1<=100000,2,if(a1<=250000,3,if(a1<=500000,4,if(a1<=1000000,5,if(a1<=2000000,6,if(a1<=3000000,7))))))),1500,a1/100000*5500,a1/250000*13750,a1/500000*26000,a1/1000000*45000,a1/2000000*75000,a1/3000000*105000)

[kewl_jules]

Dear K m,

Thank you very much. However, there seems to be some issue where if I choose a figure of $550,000, I should get a value between $26,000 and $45,000. But using your formula, this is not the case. I got a value of $24,750. This seems to be the issue with figures above $250,000 onwards. (ie, if you choose 251,000 or 501,000 or 1,001,000....etc).

Hope I have manage to explain myself well here.

[K m]

Try this:

=CHOOSE(IF(A1<=50000,1,IF(A1<=100000,2,IF(A1<=250000,3,IF(A1<=500000,4,IF(A1<=1000000,5,IF(A1<=2000000,6,IF(A1>2000000,7))))))),1500,($A$1-50000)/(100000-50000)*(5500-1500)+1500,($A$1-100000)/(250000-100000)*(13750-5500)+5500,($A$1-250000)/(500000-250000)*(26000-13750)+13750,($A$1-500000)/(1000000-500000)*(45000-26000)+26000,($A$1-1000000)/(2000000-1000000)*(75000-45000)+45000,($A$1-2000000)/(3000000-2000000)*(105000-75000)+75000)

[kewl_jules]

K m

Thank you very much for this. This works like a charm. Your help is much appreciated.

[W_C]

Hello! I am new to this forum and have a similar question can someone help me?

[K m]

Start your own thread

[FDibbins]

Hello! I am new to this forum and have a similar question can someone help me?

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[Armando Montes]

Another option:
=LOOKUP(A1,{1,50001,100001,250001,500001,1000001,2000001,3000001},{0.03,0.055,0.055,0.052,0.045,0.0375,0.035})*A1