Multiple IF's for a report

[CovMatt]

Hi All

I'm trying to create a comms calculation but struggling to bring to IF's together?

Scenario

If a salesman generates upto £ 9,999 then they get 25% of this, however if they go above £ 9,999 then they get (£9999*25%) + 40% of everything above that figure?

Anyone able to help?

Thanks in Advance

 

[JackDanIce]

Try:

=(0.25*A1)+(0.4*MAX(0,A1-9999))

Where A1 is the amount the salesman has generated, change to suit.

 

[Joe4]

Try this (for an amount in cell A1):

=(A1*0.25)+(MAX(0,A1-9999)*0.15)

 

[Joe4]

Note. I see that Jack and I had a little different interpretation of your question, as it is not quite clear.
For amounts above 9999, should they get a total of 40% or 65% (that is 40% on top of the initial 25% for that amount)?

Perhaps an actual example would help clear up exactly what you are after.
If the amount was 20000, should they get 6500.15 or 9000.40?

 

[CovMatt]

Thanks Jack, Joe

If a salesman generated £ 9999 GP then we would get 25% of this as comms

If a salesman generated anything above £ 9999 GP then he we would receive the initial 25% upto £ 9999 then 40% of everything above £ 9999 as well

SM would receive the following

£ 5000 = £ 1,250
£ 9999 = £ 2,499.75
£ 15000 = £ 4,499.75
£ 20000 = £ 6,499.75




Hope this helps?

 

[JackDanIce]

Joe's is correct!

 

[CovMatt]

Thanks both