A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Excel formula help needed!



 
 
Thread Tools Display Modes
  #1  
Old April 24th, 2010, 01:26 PM posted to microsoft.public.excel.worksheet.functions
Pat Convey
external usenet poster
 
Posts: 8
Default Excel formula help needed!

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.


  #2  
Old April 24th, 2010, 02:18 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default Excel formula help needed!

Pat,

with the amount owed in A1 try this

=SUMPRODUCT(--(A1{0;100;500;2000;10000}), (A1-{0;100;500;2000;10000}),
{0.125;-0.085;-0.015;-0.015;-0.0075})


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"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.


  #3  
Old April 24th, 2010, 03:08 PM posted to microsoft.public.excel.worksheet.functions
Stan Brown
external usenet poster
 
Posts: 536
Default Excel formula help needed!

Sat, 24 Apr 2010 05:26:01 -0700 from Pat Convey
:
Hello everyone,

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


Please don't multipost.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...
  #4  
Old April 24th, 2010, 07:21 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
Default Excel formula help needed!

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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.