Work out commission in Excel 4

[Brian555]

Can anyone help please.

I am trying to create a single formula that will work out the amount of commission that can be earnt dependant on the amount of sales.

The commission structure is as follows:

for the first £100 earned a commission of £20 is given
an extra 4% can be claimed for the next £400 (£100 to £500
an extra 2.5% can be claimed for the next £1500 (£500 to £2000)
an extra 1% can be claimed for the next £8000 (£2000 to £10000)
with an extra 0.25% for anything above $10000)

Any suggestions will be gratefully recieved.

 

[Auger282]

I would suggest using "if" statements

 

[bluedragon2]

Assuming the amount is in cell A1:

=IF(A1>10000,A1*.0775+20,IF(A1>2000,A1*.075+20,IF(A1>500,A1*.065+20,IF(A1>100,A1*.04+20,20))))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

It is often clearer and easier to maintain (as commissions DO change over time) to put the data in a TABLE and use a LOOKUP function to compute the commission.


Skip,

_[red][/red]

 

[GlennUK]

Like Skip says, you ought to put the controlling figures in a table, and use a formula to use those figures to calculate your commission.

Put these figures in cells A2:A7
0
100
500
2000
10000
99999

and put these figures in cells B2:B7
0%
5%
4%
2.5%
1%
0.25%

and use this array formula ( entered using Ctrl-Shift-Enter ) to calculate your commission:

=SUM((-IF(A2:A6>SalesAmount,SalesAmount,A2:A6)+IF(SalesAmount>A3:A7,A3:A7,SalesAmount))*B3:B7)

Replace SalesAmount with what the Sales Amount is, or a reference to a cell containing the Sales Amount.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[combo]

Or (complete threshold commission values in formula):
=CHOOSE(MATCH(A1,{0,100,500,2000,10000}),0.2*A1,20+0.04*(A1-100),36+0.025*(A1-500), [the rest of formulas])

combo

 

[xlbo]

Glenn - would a VLOOKUP using TRUE for the 4th argument do just as well for that? then you wouldn't need an array formula...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Geoff, a VLOOKUP would give you the matching amount for a particular "bucket", but my formula does a "stratify" on the full amount, multiplying the first £100 by 5%, the next £400 ( £500 - £100 ) by 4%, the next £1500 ( £2000 - £500 ) by 2.5%, the next £8000 ( £10000 - £2000 ) by 1%, and the remainder ( up to £99999 in my example - should be increased to largest possible value ) by 0.25%.

That's why you need an array formula.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

aaaaaaaaaah - me be dense

Didn't look closely enough and wonderered why you would use such a convoluted way to get a match......

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Ha ha Geoff, I guess you read that at the end of a very long day.

Yes, it's a very convoluted formula, but SO useful ... you can have a many buckets as you like and the formula hardly changes.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Loomah]

Ha Ha, I've nicked the formula and you'll never catch me!!

Seriously I can see many uses for this (though none of immediate concern) so I've stowed it away for future reference. As I work with pay the calculation of NI would be a possibility, though I use a UDF to calc full pay costs.

Current proposals for awards could so easily use this functon.

Purple Pointy Pip as (scant) compensation for my thievery!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

You can have one o' them from me as well - thread well and truly archived !!!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

I see, only one Purple Pointy Pip for the thievery ( hint hint to you other people nicking it out there ), tsk tsk.

I can see many uses for this too, so it was well worth creating it in my laboratory.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[GlennUK]

Ah, thanks Geoff ( now I have 2 Purple Pointy Pips )!!!

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[kjv1611]

That indeed could be a handy dandy forumla to keep in the ole archive. Thanks.

 

[GlennUK]

My pleasure. Am keeping it at the top of my list of useful creations.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Brian555]

Sincere thanks to everyone who has responded to me plea for help.

Brian

 

[KenWright]

Just as another resource for the same kind of topic, Chip Pearson also gives example formulas for Progressive Pricing.

http://cpearson.com/excel/pricing.htm

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[GlennUK]

Thanks for link Ken. I think I much prefer my formula ... and might even write my first FAQ "How to calculate banded commission" ( if I get some free time over the next few days ).

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[reeljustice]

Glenn:

I was able to use your example above, but, I have an added issue --- my quantity has to be divided by 1,000 then multiplied by the progressive pricing. Would you please see if you can help me on this thread?

http://www.tek-tips.com/viewthread.cfm?qid=1288683&page=1

Thanks,

Joel