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
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
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Assuming that the -150 is applied whereever profit appears, maybe
Formula:
Please Login or Register to view this content.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
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
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
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
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
Last edited by etaf; 01-22-2021 at 01:46 PM.
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.
Oh, worked for me
see attached
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)
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
Sorry, typo
Formula:Please Login or Register to view this content.
row 2 and 5 are not good
you are missing the difference from 0 - 1500 between A and B. A is 35% B is 25%
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
=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
Last edited by etaf; 01-22-2021 at 03:19 PM.
Please try at G2
=SUMPRODUCT(TEXT(F2-150-{0;1500},"0;\0")*({0.25;0.15}+(E2="A")*{0.1;0}))
thanks tons.. this works.
thanks, this works too. thanks tons.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks