multiple if conditions

[REENA GUPTA]

Hi

I have to calculate incentive based on the criteria mentioned at col G to col I. I have computed the incentive in Column E with the help of the helper column C (Target % increase) .

But I feel this is not done professionally . Is there any better way to compute the incentive . Excel sample file attached.

Thanks & Regards

 

[vletm]

REENA GUPTA
hmm? ...
I didn't get the whole idea of this 'formula', especially those right side of and-formulas ...
=IF(C3="maintain";6000;IF(AND(C3=1;D3>=B3+C3);5000;IF(AND(C3=2;D3>=B3+C3);4000;IF(AND(C3=3;D3>=B3+C3);3000;IF(AND(C3=4;D3>=86);2000;0)))))
But, You could check this ...

 

[REENA GUPTA]

REENA GUPTA
hmm? ...
I didn't get the whole idea of this 'formula', especially those right side of and-formulas ...
=IF(C3="maintain";6000;IF(AND(C3=1;D3>=B3+C3);5000;IF(AND(C3=2;D3>=B3+C3);4000;IF(AND(C3=3;D3>=B3+C3);3000;IF(AND(C3=4;D3>=86);2000;0)))))
But, You could check this ...

Hi
Thanks for your efforts !
I have to calculate incentive which is based on the March & April serviceability. I have computed the incentive in Column E with the help of the helper column C (Target % increase) which is showing the correct results.But I feel this is not done professionally . I want to know if any better professional approach can be used to achieve the same result.

I had elaborated the conditions in the attached excel file. Hope I am able to explain the problem. Any clarifications if any are welcome.

 

[vletm]

REENA GUPTA
1) You skipped my C-column formulas ...

2) Your E-column formulas has still same feature ...
Your B3+C3 means eg 52,78+4%=52,82
but You'll mean 53,82 ( = B3*(1+C3)) or what?

Newer version ...

 

[REENA GUPTA]

REENA GUPTA
1) You skipped my C-column formulas ...

2) Your E-column formulas has still same feature ...
Your B3+C3 means eg 52,78+4%=52,82
but You'll mean 53,82 ( = B3*(1+C3)) or what?

Newer version ...

Hi

Thanks . I appreciate your patience & efforts. I am sorry I skipped your C column formula. That is definitely a better approach than my formula.
But the result in column E is not correct because since the April availability is <86 i.e. 56.78 (52.78+4), the person is not eligible for bonus. We have to cheque 2 conditions. In this case condition 1 will be applicable . I had elaborated all the conditions below. Hope I am able to explain correctly. In the above example only one condition is true so no bonus. You may check the condition in E column in my attached file "File for Forum" , I had used AND condition.

Conditions 1 If March servicibility(S) is < 86 then April Servicibility (S1) should be March servicibility(S) plus 4% subject to minimum 86%. That means if S<86% then S1 should be S+4% and S1 is >=86% bonus will be 2000 else no bonus.
Conditions 2 If March servicibility(S) is < 90 then April Servicibility (S1) should be March servicibility(S) plus 3% .That means if S<90% S1 should be S+3% to get bonus of Rs 3000 else no bonus.
Conditions 3 If March servicibility(S) is < 94 then April Servicibility (S1) should be March servicibility(S) plus 2% .That means if S<94% S1 should be S+2% to get bonus of Rs 4000 else no bonus.
Conditions 4 If March servicibility(S) is < 98 then April Servicibility (S1) should be March servicibility(S) plus 1% .That means if S<98% S1 should be S+1% to get bonus of Rs 5000 else no bonus.
Conditions 5 If March servicibility(S) is > 98 then April Servicibility (S1) should be equal to or greater than March servicibility(S) .That means if S>98% S1 should be >=S to get bonus of Rs 6000 else no bonus.

 

[vletm]

REENA GUPTA
before continue ...
Your i.e. 56.78 (52.78+4)
52.78 is Okay.
but if You mean that Your 4 would work like 4% then 56.78 won't match for me!
For me ... that is 53,82.

 

[REENA GUPTA]

REENA GUPTA
before continue ...
Your i.e. 56.78 (52.78+4)
52.78 is Okay.
but if You mean that Your 4 would work like 4% then 56.78 won't match for me!
For me ... that is 53,82.

Hi
the figures in col B,C,D are just % to be achieved. We don't have to compute the %. Simply add the number to achieve the target % required to be eligible for bonus subject to Conditions 1 to conditions 5. So 52.78+4 = 56.78 is the target but subject to minimum of 86% in case of condition 1.
So is there is there a better way to get the desired result in Incentive column

 

[vletm]

REENA GUPTA
You have marked that those are like and work like %
... then for me those are %!

I have to use those as % ... I don't know about You?
> Take care <