Calculating bonus with multiple conditions

Question

I need help with calculating bonus based on the below :-

If user reaches 120% in one category except E, user will get 10% bonus in another category which reached 60%-69%

The bonus column is my desired result.

User	Category	Achievement	Bonus
John	A	65%	10%
John	B	80%	0
John	C	120%	0
John	D	68%	10%
John	E	120%	0
Jack	A	68%	10%
Jack	B	54%	0
Jack	C	130%	0%
Jack	D	65%	10%
Jack	E	120%	0%

xxplore · Answer

=(C2&gt;=0.6)*(C2&lt;=0.69)*(COUNTIFS(A:A,A2,B:B,"&lt;&gt;E",C:C,"&gt;=1.2")&gt;=1)*10%

wmfw2360 · Answer

Thanks ! Could you explain the formula ? why do we use *

xxplore · Answer

wmfw2360&nbsp;&nbsp;The formulas in (...) returns True=1 or False=0&nbsp; (Condition_1)*(Condition_2)*...*(Condition_n)*(Result)It returns 1*1*...*Result=Result when all conditions are Ture, otherwise 0;Similar&nbsp;toIf ( AND(Condition_1, Condition_2, ..., Condition_n), Result_If_All_True )&nbsp;

Forum Discussion

Calculating bonus with multiple conditions

3 Replies

Resources