Excel Formula

  Quickbeam 08:50 25 Mar 2006
Locked

How do I make Excel work out a variable commission from one input figure?

For example... a sale of £220...
£0 - £60 =100% comm'
£61 - £100 = 80%
£101 - £140 = 70%
£141 - £180 = 60%
£180 - £220 = 50%

The answer being... £164
Please feel free to correct any mistake I've made doing it on paper... maths was never my strong point!

  Simsy 08:55 25 Mar 2006

So the more is sold the less the commission?

What if sales are more than £220?

Regards,

Simsy

  Quickbeam 09:07 25 Mar 2006

The 220 figure & 50% are the highest likely figures.
It's not realy a product... I just put the £ sign on as an example, but it confusses the matter, it's realy car milage use.
The bands of 40 are only to simply work out a figure with pen & paper... I just don't know how to make the excel formula do this without seeing an example with the above simplified figures that can then be modified.

  GroupFC 09:17 25 Mar 2006

I think Quickbeam means that the commission is staged. In other words for a sale of £220, the commission is 60 + 32 + 28 + 24 + 20 (least ways that the only way I can get to £164!). These number being for example £180-£140 at 60% = £24 and so on.

As to a formula, I'll leave that to the experts!

Quickbeam, this commission structure does seem exceedingly generous, for a sale of £220, £164 is paid away in commission!

  GroupFC 09:19 25 Mar 2006

Doh!!! - must refresh before posting, must refresh before posting, must.......!!

  Quickbeam 09:26 25 Mar 2006

GroupFC... yes, that's how I mean it to work. The £ sign has caused a confussion... so disregard that as far as the example goes.

I'm glad I'm not the only one that doesn't know how to make this work on a one entry formula. Generally if we think a computer can do it... it can... if we know how!

  VoG II 09:48 25 Mar 2006

I get a different answer but I think that your last category should start at 181

=MIN(A1,60)+0.8*((A1-61)-(A1-100))+0.7*((A1-101)-(A1-140))+0.6*((A1-141)-(A1-180))+0.5*(A1-181)

=161.4

  VoG II 09:52 25 Mar 2006

But that will give the wrong answer with values other than 220!

  Quickbeam 09:58 25 Mar 2006

Thanks VoG™... This is indeed a corrected answer... I said I was rubish at maths!
This one was beyond my Excel in easy steps book, which is about all I can understand by myself!!!... Thanks again.

  Simsy 10:31 25 Mar 2006

Does VoG™'s solution resolve this?

I'm still confused as to what you're trying to work out, even with the mention of car mileage!

But if it's now resolved I'll go away!

Regards,

Simsy

  VoG II 11:17 25 Mar 2006

=MIN(A1,60)+0.8*MAX(MIN((A1-61),39),0)+0.7*MAX(MIN((A1-101),39),0)+0.6*MAX(MIN((A1-141),39),0)+0.5*MAX(A1-181,0)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

What is ransomware and how do I protect my PC from Petya?

Microsoft Surface Studio – the artist's hands-on review

Original iPhone review

Comment mettre à jour Kodi ?