IF Statement

[Shams]

Folks,
I am trying to combine the AND and OR logic within an IF
statement. I am not sure if I can do it or if I am using
the right construct:

Basically, I am trying to calculate an additional bonus
for a Salesperson if his March Gross Profit > February
Gross Profit AND if his March GP > any of the 4 GP
brackets. That is, he would only earn the extra bonus if
his GP falls into a higher bracket. So, let's say there
are 4 brackets:

if GP is less than 30%, no std. commission is earned,
if GP is less than 45%, 6% comm. on GP is earned,
if less than 60%, 12%
if less than 80%, 18%.

So, if February GP was 50% and March GP is 58%, he will
only earn his standard commission and NOT his extra
commission. If he brings in over 60%, then he will make
the extra commission.

I am struggling to create the right argument. How do I
ensure that if his GP is higher than last month's and the
GP is in a higher bracket then indicate Extra Commission
to be earned? Please help!!

Regards,
Shams.

 

[Olly]

If MarGP > FebGP Then
If MarGP < 30% Then
Commission = 0
Else
If MarGP < 45% Then
Commission = 6%
Else
If MarGP < 60% Then
Commission = 12%
Else
If MarGP < 80% Then
Commission = 18%
Else
Commission = amount_for_over_80%?
End If
End If
End If
End If
Else
Commission = 0
End If

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Hi Shams,

Try this

If ThisGP > LastGP Then
Select Case ThisGP
Case < 30% : Commission = 0
Case < 45% : Commission = 6%
Case <60%: Commission = 12%
Case < 80%: Commission = 18%
End Select
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Robert McCurdy]

Try this Shams.

=CHOOSE(MATCH(A2,{0.30,0.45,0.60,0.80},1),1,1.06,1.12,1.18)

Use this to test for each month, then use Large or Max?

Regards Robert