• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Question on Conditional and Complex formulas

M

max4asd

Member
Dear All,


I would like to have a solution on the below complex calculations. Please help if anybody has a solution for this calculations.


Below is the conditions table

[pre]
Code: 
Targets							Incentive Split
Sales	Discounts Supply Safety		       Sales	Discounts Supply Safety
100%	6.5%	   95%	  3			100$	   36$	   18$	  36$
110%	5.85%	   95%	  3			130%	   130%	   18$	  36$
120%	5.20%	   95%	  3			200%	   200%	   18$	  36$
[/pre]
Based on the above conditions i want to calculate the following.


1. If X achieved 100% sales and his discounts are below or equal to 6.5% and supply is 95% and safety tickets is less than or equal he is eligible to get full incentive 100$+36$+18$+36$ = 190$ or if he achieves any one of the condition he will get what he achieve Example: If he achieve only sales he will get 100$ only or only achieved safety gets 36$ or achieves both he gets 136$ but will loose the other 2. In other words if he achieves any of the conditions above he gets incentive accordingly.


2. If X achieved 110% sales he will get 130% of sales incentive means 100$+30$=136$ and if his discounts are below the target of 6.5% by 10% he gets 130% of discount incentive 36$+10.8$=46.8$ and other 2 conditions of supply and safety will be the same.


3. If X achieved 120% sales he will get 200% of sales incentive means 100$+100$=200$ and if his discounts are below the target of 6.5% by 20% he gets 200% of discount incentive 36$+36$=72$ and other 2 conditions of supply and safety will be the same.


But if his discounts are doubled the target discounts he wont get incentive for any of the KPI means 0.


Could you please help in putting this logic in formula in excel i will be grateful to all of you and the forum.


Thanks
 
If I understoon correctly, all of the KPIs are independently checked (except for the 2x discounts), so I rearranged your criteria table like so, in cells A1:H4 (labels abbreviated)

[pre]
Code: 
Sales_T	Sales	Disc_T	Disc	Sup_T	Sup	Safe_T	Safe
100%	100%	6.50%	100%	95%	$18 	3	$36
110%	130%	5.85%	130%
120%	200%	5.20%	200%
[/pre]
Also, to clarify, does supply need to be <= 95%, or exactly =? I wasn't sure, but it's easy to change. Formula structure becomes:

=IF(Discounts>2*$C$2,0,(Safety<=3)*$H$2+(Supply<=95%)*$F$2+IF(Sales>=$A$2,LOOKUP(Sales,$A$2:$B$4)*100,0)+

IF(Discounts<=$C$2,INDEX($D$2:$D$4,MATCH(Discounts,$C$2:$C$4,-1))*36,0))


Also, note that the $100 for sales and the $36 for discounts are currently hardcoded into formula, since the table value has been replaced with percentages. Ideally, this should be written down somewhere and cell references used.
 
Thanks a lot luke it works perfectly but i think i didn't communicate one more thing all the KPIs are independently checked only in case of 100% achievement but in case if sales achievement is more than or equal to 110% than discount should also be less by 10% means 5.85% meaning both should be achieved to earn an incentive of 130% for both KPIs all other remains same.


In the same way it apply the same rule if its 120%
 
Hi Max4asd,

Just woke up, so am just now reading your message. =)

It's somewhat clear, but to make sure, perhaps you can tell me what happens in following:

1. Sales, 115%, discount 6.5%, supply 95%, safety 3

2. Sales 100%, discount 5.85%, suppliy 90%, safety 5

3. Sales 120%, discount 5.85%, supply 95%, safety 3
 
You must log in or register to reply here.
Back
Top