Help with formula in Excel please

[reeljustice]

Not sure this is in the correct forum, but here goes.
Here is my quandry.

I need calculations performed for different states. Each state has a different computation to arrive at the $$ charged.

For example:
NC fee computation
We have a total dollar amount, say $800,000.00

The fee is computed on a sliding scale.

$0-100,000 is $2 per $1,000
PLUS
$100,001 - $500,000 is $1.50 per $1,000
PLUS
$500,001 - infinity is $1.00 per $1,000

In our example of $800,000.00 the first $100,000.00 is
=((100,000/1000)*2) = $200.00
then $100,001 - $500,000 is =((400,000/1000)*1.50) = $600.00
then $500,001 - $800,000 is =((300,000/1000)*1.00) = $300.00

So, our total fee is $200+$600+$300 = $1,100.00

What I would like to do is create a work sheet which contains the calculation formulaes for each state, so, I would initially need the formula for NC above, then on "display" worksheet, an = reference of something of the sort which references the state and the amount and makes the state formula work with the amount varible to give me an accurate fee.

Can someone help with the above?

Thanks,

Joel

 

[Bong]

I'll try it.

First of all, it seems to me that you want to (and can) do this with formulae on the spreadsheet so, technically, this would be in the MSOffice forum and not here in VBA. But anyway...

I think you start with the largest amount, in your example that would be "over 500,000".

if <amount> > 500000 then <fee>= 2*100 + 1.5*400 + 1*<leftover>,
where <leftover>= (<amount>-500000)/1000

else if <amount> > 100,000 then <fee>= 2*100 + 1.5*<leftover>,
where <leftover>=(<amount>-100000)/1000

else <fee>=2*(<ammount>/1000)

the Excel formula syntax for if is:
IF(logical_test,value_if_true,value_if_false)

You would have to nest the else condition(s) inside the value_if_false part(s) of the formula.

_________________
Bob Rashkin

 

[reeljustice]

Many thanks Bob. I'll give that a shot. Also, John recommended this thread:

http://tek-tips.com/viewthread.cfm?qid=1282681&page=1

Sincerely,

Joel