Formula calculate the comission according some condition
example
salesman achieve 4.5 box and 1 carton in a month.
16box become 1 carton
(4.5/16)+1 = 1.28 carton
help me make a fromula can calculate as below:
total carton 1.28, it on the range 1 box RM6 , 1 carton RM9.
(box) 4.5 x 6 = 27
(carton) 1 x 90 = 90
total comission RM117
Please refer to attachment, thks
Reputation sure to be given for appreciate your helpful
Last edited by choy96; 02-12-2023 at 08:48 PM.
please explain the box:
4.5 x 6 = 27
Is that really correct??
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I think it should be 8 instead of 6 [as per total carton
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
thanks Glenn Kennedy.
1 carton = 16 box
salesman achieve
4.5 box , so convert to carton is 4.5/16 = 0.28
salesman also sell 1 carton , so 0.28+1= 1.28
total achive 1.28 carton
1.28 carton can get comission on the range 0-2 carton , on box comission can get RM6, on carton comission can get RM90
box comission = 4.5 x 6 = 27
Carton comision = 1 x 90 = 90
total comission 27 + 90 = 117
on box comssion each RM6, on carton should be 16 x 6 =96, but our sales is sell 1 carton = 16 box , we calculate as 15 box free 1 box, that why direct sell 1 carton, we only pay 15 box comission only.
hope can understand above detail, thanks very much.
Last edited by choy96; 02-10-2023 at 12:51 AM.
Try:
Formula:
Please Login or Register to view this content.
Quang PT
Thanks bebo021999
I have try your formula, but it return me a wrong answer.
when i add 1.5 on carton comssion, answer 588, correct answer should be 336
I have attached a file on it , may be can explain more clearly.
thanks very much.comission salesman .xlsx
With a small layout change (red cells):
=E12*LOOKUP(E12,I7:I10,J7:J10)+F12*LOOKUP(F12,I7:I10,K7:K10)
Thanks Glenn Kennedy, very much.
Have try your formula, but it return wrong answer when increase to comission range 10 to 15
total carton 12.78, it on the range 10-15, comission is 1 box RM10 , 1 carton RM150.
12.5 x 150 = 1875
4.5 x 10 = 45
total comission is 1875 + 45 = 1920
but your answer is 1911
Please refer attachment, thanks
Ahhhh!! It is the TOTAL that defines the row. I misunderstood!!
=E12*LOOKUP(E13,I7:I10,J7:J10)+F12*LOOKUP(E13,I7:I10,K7:K10)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks