On Sat, 24 Apr 2010 05:26:01 -0700, Pat Convey
wrote:

Hello everyone,

I would like to create a formula that would calculate a different levy
charge dependent on the amount.

The example is below and any help would be really appreciated.

In circumstances where amounts owed do not exceed £100 or $100 12.5% charge
will apply, 4% on the next £400 or $400, 2.5% on the next £1500 or $1500, 1%
on the next £8000 or $8000 and 0.25% on any additional sum.

In short different %'s will apply at different levels depending on monies
mowed.

I'm sure that there will be use of If/And but I'm not sure on the structure
of the formula.

I hope I haven't confused you all.

Many thanks in advance,

Pat Convey.


It's a little confusing. It would have been valuable had you given some
examples of amount vs levy.

But, I think you could set up a lookup table as follows:

0 $- 12.50%
100 $ 12.50 4.00%
500 $ 28.50 2.50%
2000 $ 66.00 1.00%
10000 $146.00 0.25%

Using a table instead of a long IF formula would make future alterations much
simpler.

Note that the second column is the amount that would be levied on the amount in
the first column. So if your table is in I1:K5, then
J1: 0
J2: =J1+(I2-I1)*K1

and fill down to J5.

Then, with your table NAME'd Tbl, and your amount in A1, the levy would be
given by:

=VLOOKUP(A1,Tbl,2)+(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3)

--ron