Excel formula help needed!

[Pat Convey]

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.

 

[helene and gabor]

Hello Pat,

I hope this will do.

=IF(A1<=100,A1*0.125,IF(AND(A1>100,A1<500),12.5+(A1-100)*0.04,IF(AND(A1>500,A1<=2000),28.5+0.025*(A1-500),IF(AND(A1>2000,A1<=10000),66+(A1-2000)*0.01,146+0.0025*(A1-10000)))))

Best Regards,

Gabor Sebo

 

[Bernard Liengme]

Here is a version of helen & gabor's reply without the IF clutter
=(A1<=100)*(A1*0.125)+AND(A1>100,A1<500)*(12.5+(A1-100)*0.04)+AND(A1>500,A1<=2000)*(28.5+0.025*(A1-500))+AND(A1>2000,A1<=10000)*(66+0.01*(A1-2000))+(A1>10000)*(146+0.0025*(A1-10000))

See this file at my account on Microsoft Skydrive
http://cid-162119ff97286948.skydrive.live.com/self.aspx/.Public/24April2010.xlsx

best wishes

 

[Joe User]

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.

It is generally regarded easier to maintain a lookup table. See
www.mcgimpsey.com/excel/variablerate.html for one approach. I prefer to use
VLOOKUP myself. Post a follow-up here if you would like to see the VLOOKUP
solution.

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

Here is an alternative:

=MIN(A1*12.5%, (A1-100)*4%+12.5,
(A1-500)*2.5%+28.5, (A1-2000)*1%+66,
(A1-10000)*0.25%+146)

The amounts 12.5, 28.5, 66 and 146 are the total tax on the highest amount
of the previous "tax bracket". You can determine those amounts in bootstrap
fashion. For example, enter the formula =MIN(A1*12.5%), and compute the tax
for A1=100. Then enter the formula =MIN(A1*12.5%,(A1-100)*4%+12.5), and
compute the amount for A1=500. Et cetera.

where amounts owed do not exceed £100 or $100
[...] on the next £400 or $400 [...] on the next
£1500 or $1500 [...] on the next £8000 or $8000

The formulas work for any denomination, of course. But since you mentioned
dollars and pounds, it would behoove you to explicitly round any formula to
the penny, unless you want to keep track of fractional penny amounts.

For example:

=ROUND(MIN(A1*12.5%, (A1-100)*4%+12.5,
(A1-500)*2.5%+28.5, (A1-2000)*1%+66,
(A1-10000)*0.25%+146), 2)


----- original message -----