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

 

[AnotherHiggins]

Joel,

This is, indeed, the correct forum for general Excel questions, including formula questions.

This was actually covered recently and GlennUK gave a brilliant answer.

You can read all about it here (thread68-1282681) - see his post dated 26 Sep 06 10:30 (the fifth post).

Put these figures in cells A2:A7
0
100
500
2000
10000
99999

and put these figures in cells B2:B7
0%
5%
4%
2.5%
1%
0.25%

and use this array formula ( entered using Ctrl-Shift-Enter ) to calculate your commission:
CODE
=SUM((-IF(A2:A6>SalesAmount,SalesAmount,A2:A6)+IF(SalesAmount>A3:A7,A3:A7,SalesAmount))*B3:B7)
Replace SalesAmount with what the Sales Amount is, or a reference to a cell containing the Sales Amount.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Zathras]

I would try to do something like this:

Store the rate schedule in a form that is easy to maintain, (i.e. in a separate table) and use a calculation formula that accesses the rate schedule with VLOOKUP functions.

For example, if the NC rates are stored in A2:C5 like this:
[tt]
A2: 0
A3: 100000
A4: 500000
B2: 0
B3: 200
B4: 350
C2: 2
C3: 1.5
C4: 1
[/tt]
Then assign a range name to A2:C4 like "NC_RATES"
Then this formula will provide the rate for any specified amount in cell A10 (for example):

=VLOOKUP(A10,NC_RATES,2,1)+(A10-VLOOKUP(A10,NC_RATES,1,1))*VLOOKUP(A10,NC_RATES,3,1)/1000

You can do the same thing for any rate schedule you have.

 

[Zathras]

Sorry, that should have been 800 in cell B4 (not 350 as posted).

 

[reeljustice]

Many thanks John. I'll give that a shot.

 

[reeljustice]

John:
I attempted to apply the reasoning (not very well) to my situation, but, couldn't get it to work.

Zathras, I think your reasoning is somewhat along the same lines but I don't know how to "assign a range name".

I also looked at this article

http://cpearson.com/excel/pricing.htm

attempting to follow the Progressive Pricing example but wasn't successful in doing that either.

I appreciate your help as I'm pretty dense when it comes to these logical functions.

Additional "handholding" and direction would be appreciated.

Sincerely,

Joel

 

[Zathras]

Range names are very useful. Well worth your time to look up in the help file and learn about them.

In this case, select the cells A2:C5 and then choose from the menu: Insert/Name/Define... Type the name in the space provided and click OK. (There is a short-cut way whereby all you need to do is select the cells and type the name in the "Name Box" to the left of the "Formula Bar" but if you ever need to change a range definition, you need to know how to use the menu.)

All of the methods presented provide for only one set of fee brackets. You indicated that you will need to do this for multiple states. If you can know in advance which state you are working with, you can use the appropriate range reference. If you need to use the state code as an input variable to the formula, much more work is needed. It can be done, but for now better to walk before trying to run.

BTW, you should be able to see the similarity between the fee schedule as I outlined and the layout of the IRS tax rate schedules.

 

[GlennUK]

Hi reeljustice,

how are you getting on? If using my method is giving you answers of a factor of a 1000 too high, simply divide the sum by 1000.

If you need more help just post back, and we'll see what we can do.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[reeljustice]

OK guys, John first (failed) then Zatharas (success but don't know why).

John first:
I've tried doing the array deal with the "Ctrl+Shift+Enter" as follows:

Cells F:5 thru F:8
100000
500000
10000000
100000000

Cells G:5 thru G:8
$2.00
$1.50
$1.00
leaving the remainging cells blank

Used the following formula with "Ctrl+Shift+Enter":
=(SUM(IF(IF(B$5>=F6:F10,F6:F10-F5:F9,$B$5-F5:F9+1)>0,IF(B$5>=F6:F10,
F6:F10-F5:F9,$B$5-F5:F9+1),0)*G5:G9))/1000

I put the $800,000 price in B:5

I got the result $1,250.00

The proper result for my math in the post above is $1,100.00
Any ideas as to what is wrong?


Zathras:
I plugged in your suggestions, looked up Named Ranges and it worked fine.

Could you please explain in simple language what was done and how you came up with the figures in the B column so that I might apply them to other situations?


Thanks again for all your help.

Sincerely,

Joel

 

[GlennUK]

Hi Joel,
your problem:

I've tried doing the array deal with the "Ctrl+Shift+Enter" as follows:

Cells F:5 thru F:8
100000
500000
10000000
100000000

Cells G:5 thru G:8
$2.00
$1.50
$1.00
leaving the remainging cells blank

Used the following formula with "Ctrl+Shift+Enter":
=(SUM(IF(IF(B$5>=F6:F10,F6:F10-F5:F9,$B$5-F5:F9+1)>0,IF(B$5>=F6:F10,F6:F10-F5:F9,$B$5-F5:F9+1),0)*G5:G9))/1000

I put the $800,000 price in B:5

I got the result $1,250.00

The proper result for my math in the post above is $1,100.00
Any ideas as to what is wrong?

is that you should have set it up like this:

Cells F5 thru F9
0
100000
500000
10000000
100000000

Cells G5 thru G9
$0.00
$2.00
$1.50
$1.00
$1.00

with a formula of:
=SUM((-IF(F5:F8>B5,B5,F5:F8)+IF(B5>F6:F9,F6:F9,B5))*G6:G9)/1000

entered using Ctrl-Shift-Enter.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Zathras]

It's just another way of looking at your sample data
The data (A2:C4) look like this (after formatting):
[tt]
0 0 2.00
100,000 200 1.50
500,000 800 1.00
[/tt]
Column A defines the brackets
Column B defines the "fixed" portion for the bracket.
Column C defines the "variable" portion for the bracket.

For any amount up to 100,000 there is no "fixed" amount, simply the fee of 2.00 per thousand.

For any amount between 100,000 and 500,000, the first 100,000 is rated at 2.00 per thousand which is 200. That is the "fixed" amount for the first 100,000. Over 100,000 the fee of 1.50 is used.

For any amount over 500,000, the first 100,000 is rated at 2.00 per thousand which is 200, and the next 400,000 is rated at 1.50 which is 600. So adding 200 together with 600 gives 800 which is the "fixed" amount for the first 500,000. Over 500,000 the fee of 1.00 is used.

The VLOOKUP formula works in two different ways depending on the fourth argument. When the fourth argument is FALSE, an exact match is required. However when the fourth argument is TRUE (or 1 which has the same effect here), the lookups are a "less than or equal to" style of lookup, which provides exactly the functionality you required.

Now, as I said previously, that is fine if there is only one lookup table. In your case since you indicated that you would have multiple states, you would apparently need multiple tables. That would require a slightly more complicated formula, but could be done. If you provide additional test data we could give it a go.