Excel formula help needed!

  • Thread starter Thread starter Pat Convey
  • Start date Start date
P

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.
 
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
 
Pat Convey said:
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.
Click to expand...

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.
Click to expand...

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
Click to expand...

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 -----
 
Back
Top