Fee Grid Calculation
 

I am looking for an easy way to calculate the fee based on the market value
of hundred of individual accounts.

The fee has various breakpoints - for example:

0 - 10,000 1% ( first 10,000)
10,001 - 100,000 .50% (Next 90,000)
100,001 - 500,000 .25% ( Next 400,000)
500,001 - 1,000,000 .10% ( Next 500,000)


Thus I would like a worksheet that would have one column with the Market
Value and another column with the fee amount. ( May be other columns to
assist with the calculation)

I am hoping to avoid nesting many if, then statements.

Any thoughts?

Thanks for your time and assistance

Fee Grid Calculation
 

Hi,
Let's assume that you enter the number in cell A24 in B24 enter

=SUMPRODUCT(--(A24{0;10000;100000;500000}),A24-{0;10000;100000;500000},{0.01;-0.005;-0.00025;-0.00015})

Change cell to fit your needs

if this helps please click yes thanks

"John S" wrote:

I am looking for an easy way to calculate the fee based on the market value
of hundred of individual accounts.

The fee has various breakpoints - for example:

0 - 10,000 1% ( first 10,000)
10,001 - 100,000 .50% (Next 90,000)
100,001 - 500,000 .25% ( Next 400,000)
500,001 - 1,000,000 .10% ( Next 500,000)


Thus I would like a worksheet that would have one column with the Market
Value and another column with the fee amount. ( May be other columns to
assist with the calculation)

I am hoping to avoid nesting many if, then statements.

Any thoughts?

Thanks for your time and assistance

Fee Grid Calculation
 

One method using no if statements - this assumes the number in question is in
cell E7.

=MIN(E7,10000)*0.01+MIN(90000,MAX(0,E7-10000))*0.005+MIN(400000,MAX(0,E7-100000))*0.0025+MIN(500000,MAX(0,E7-500000))*0.001
--
Wag more, bark less


"John S" wrote:

I am looking for an easy way to calculate the fee based on the market value
of hundred of individual accounts.

The fee has various breakpoints - for example:

0 - 10,000 1% ( first 10,000)
10,001 - 100,000 .50% (Next 90,000)
100,001 - 500,000 .25% ( Next 400,000)
500,001 - 1,000,000 .10% ( Next 500,000)


Thus I would like a worksheet that would have one column with the Market
Value and another column with the fee amount. ( May be other columns to
assist with the calculation)

I am hoping to avoid nesting many if, then statements.

Any thoughts?

Thanks for your time and assistance

Fee Grid Calculation
 

This is perfect - Thanks so much - do you know where I can find a write up
that explains what is going in the formula so I may understand better????

"Eduardo" wrote:

Hi,
Let's assume that you enter the number in cell A24 in B24 enter

=SUMPRODUCT(--(A24{0;10000;100000;500000}),A24-{0;10000;100000;500000},{0.01;-0.005;-0.00025;-0.00015})

Change cell to fit your needs

if this helps please click yes thanks

"John S" wrote:

I am looking for an easy way to calculate the fee based on the market value
of hundred of individual accounts.

The fee has various breakpoints - for example:

0 - 10,000 1% ( first 10,000)
10,001 - 100,000 .50% (Next 90,000)
100,001 - 500,000 .25% ( Next 400,000)
500,001 - 1,000,000 .10% ( Next 500,000)


Thus I would like a worksheet that would have one column with the Market
Value and another column with the fee amount. ( May be other columns to
assist with the calculation)

I am hoping to avoid nesting many if, then statements.

Any thoughts?

Thanks for your time and assistance

Fee Grid Calculation
 

Hi,
take a look here, different uses of sumproduct


http://www.xldynamic.com/source/xld.SUMPRODUCT.html

"John S" wrote:

This is perfect - Thanks so much - do you know where I can find a write up
that explains what is going in the formula so I may understand better????

"Eduardo" wrote:

Hi,
Let's assume that you enter the number in cell A24 in B24 enter

=SUMPRODUCT(--(A24{0;10000;100000;500000}),A24-{0;10000;100000;500000},{0.01;-0.005;-0.00025;-0.00015})

Change cell to fit your needs

if this helps please click yes thanks

"John S" wrote:

I am looking for an easy way to calculate the fee based on the market value
of hundred of individual accounts.

The fee has various breakpoints - for example:

0 - 10,000 1% ( first 10,000)
10,001 - 100,000 .50% (Next 90,000)
100,001 - 500,000 .25% ( Next 400,000)
500,001 - 1,000,000 .10% ( Next 500,000)


Thus I would like a worksheet that would have one column with the Market
Value and another column with the fee amount. ( May be other columns to
assist with the calculation)

I am hoping to avoid nesting many if, then statements.

Any thoughts?

Thanks for your time and assistance

Fee Grid Calculation
 

=LOOKUP(B1,{0,10001,100001,500001,1000001},{0.01,0 .005,0.0025,0.001})*B1

Will return #N/A for any number above 1,000,000


Gord Dibben MS Excel MVP

On Tue, 1 Dec 2009 08:39:01 -0800, John S
wrote:

I am looking for an easy way to calculate the fee based on the market value
of hundred of individual accounts.

The fee has various breakpoints - for example:

0 - 10,000 1% ( first 10,000)
10,001 - 100,000 .50% (Next 90,000)
100,001 - 500,000 .25% ( Next 400,000)
500,001 - 1,000,000 .10% ( Next 500,000)


Thus I would like a worksheet that would have one column with the Market
Value and another column with the fee amount. ( May be other columns to
assist with the calculation)

I am hoping to avoid nesting many if, then statements.

Any thoughts?

Thanks for your time and assistance