Help with Commission calculations

[solporg]

Hi,

i would like to get the commission numbers based on the following:

first i want to take off 150.00 from the profit total.
than if the Type is A than for the first 1,500.00 give 35% anything higher is 50%
than if the Type is B than for the first 1,500.00 give 25% anything higher is 40%


Thanks

[etaf]

jut to clarify

I would use a nested IF along the lines of
=IF(AND(E2="A",F2 < 1500),F2*35, IF(AND(E2="A",F2 > = 1500), ((1500*0.35)+((F2-1500)*0.5)), .....
BUT

What value is for 1500 you say less than for 35% so 1500 would apply 50% for type A

And so is this the % applicable
((1499*0.35)+((F2-1499)*0.5)),

Also the 150 - does that mean
that the 1500 is in fact 1650 ???
or how does the 150 apply

[solporg]

Thanks for replying.
to clarify, the profit in F2 is 3,400.00 so i would first take off 150.00 so total is now 3,250.00
the type is A so he would need to get on the first 1,500.00 35% and for the remaining 1,750.00 50%
hope this helps
thanks again

[solporg]

This works partially.
you did not subtract first the 150.00.
and what if its a "B"
can you please give me the full formula?
Thanks

[solporg]

Also the 150 - does that mean
that the 1500 is in fact 1650 ???
or how does the 150 apply

on the first 150.00 he does not make any commission

[ChemistB]

Assuming that the -150 is applied whereever profit appears, maybe

=MIN(1500, F2-150)*0.35 +MAX(0, F2-150-1500)* IF(F2="A", 0.5,0.4)

[etaf]

this is a nested IF - maybe a better way to do this
BUT does this actually work ?

=IF(AND(E2="A",(F2-150<=1500)),(F2-150)*0.35,IF(AND(E2="A",(F2-150>1500)), (F2-150-1500)*0.5,IF(AND(E2="B",(F2-150<=1500)),(F2-150)*0.25,IF(AND(E2="B",(F2-150>1500)), (F2-150-1500)*0.4,"criteria not met"))))

on the first 150 does not make commission nor is it part of the 1500 threshold going by your reply
threshold is 1650 - then if 1650 commision is calculated on 1500

[solporg]

it gives me back only the commission for the over 1650.00, for some reason for the first 1650-150=1500 * 35% or 25% nothing is calculated.

[etaf]

Oh, worked for me
see attached

[solporg]

for line 1 and 4 on your attached sheet, you give back only commission for above the 1500.00, you do not calculate the 25% for the first 1650.00-150.00=1500.00 (commission for this part should be 525.00 and for the above numbers an additional 875.00 total 1,400.00)

[etaf]

Row 4 is for
Type A
profit 200
Commision 17.5
200-150 = 150
150*35 % = 17.5
seems to work

Row 3
Type B
profit 860
Commision 177.5
860 - 150 = 710
710 * 25% = 177.5
also seems to work

Image attached of a screen shot

[solporg]

row 2 and 5 are not good

[ChemistB]

Sorry, typo

=MIN(1500, F2-150)*0.35 +MAX(0, F2-150-1500)* IF(E2="A", 0.5,0.4)

[solporg]

you are missing the difference from 0 - 1500 between A and B. A is 35% B is 25%

[solporg]

in other words if the Type is "B" you are still giving 35% for the first 1500.00, it should be only 25% for the first 1500.00 if the type is B
thanks

[etaf]

=IF(AND(E2="A",(F2-150<=1500)),(F2-150)*0.35,
IF(AND(E2="A",(F2-150>1500)), (1500*0.35)+((F2-150-1500)*0.5),
IF(AND(E2="B",(F2-150<=1500)),(F2-150)*0.25,
IF(AND(E2="B",(F2-150>1500)), (1500*0.25)+((F2-150-1500)*0.4),"criteria not met"))))

forgot to add the 1500 commision
hows that

[solporg]

thanks tons.. this works.

[Bo_Ry]

Please try at G2
=SUMPRODUCT(TEXT(F2-150-{0;1500},"0;\0")*({0.25;0.15}+(E2="A")*{0.1;0}))

[solporg]

thanks, this works too. thanks tons.