4 Replies Latest reply on Nov 13, 2016 11:26 PM by Morgan DUARTE

# Help in formula - If match primary condition and secondary one then xx

Hi there,

I'm working on an incentive plan and need your help to build a formula that works...

I have 3 activities with a target and an actual value"

Act1 = Target: 100, Actual: 90

Act2 = Target:50, Actual: 60

Act3 = Target: 40, Actual 30

If they achieve Act1 + Act2 targets, they got 2%, if they achieve Act1 + Act3, they got 3% and if they achieve all, they got 5%

So Act1 target is the primary condition. If they failed this, they don't get anything.

How can I build a formula to display how much incentive they will get?

Any ideas?

Thank you !

• ###### 1. Re: Help in formula - If match primary condition and secondary one then xx

Hi Check this :

Create 2 calculations like below :

z :

CASE [Act]

WHEN 'Act1' THEN (IF [Actual]>=[Target] THEN 1 END)

WHEN 'Act2' THEN (IF [Actual]>=[Target] THEN 2 END)

WHEN 'Act3' THEN (IF [Actual]>=[Target] THEN 3 END)

END

Result :

IF MIN([z])=1 THEN (IF MEDIAN([z])=2 THEN 5 ELSEIF  MAX([z])=2 THEN 2 ELSEIF MAX([z])=3 THEN 3 END)

ELSE 0

END

attaching sample workbook :

1 of 1 people found this helpful
• ###### 2. Re: Help in formula - If match primary condition and secondary one then xx

Very interesting solution ! Thank you Hima,

What if I have 4 activities?

Act4 = Target 50, Actual 60

If the targets for Act1 and Act4 are met, we got 5% incentive.

So if all met, it will be 10%

In that case the medium will not work, right.

Any other way in that case?

Thank you !

• ###### 3. Re: Help in formula - If match primary condition and secondary one then xx

Yes Median will not work in that case : Try these calculation (just changing old calculations) :

z :

CASE [Act]

WHEN 'Act1' THEN (IF [Actual]>=[Target] THEN 1 END)

WHEN 'Act2' THEN (IF [Actual]>=[Target] THEN 2 END)

WHEN 'Act3' THEN (IF [Actual]>=[Target] THEN 3 END)

WHEN 'Act4' THEN (IF [Actual]>=[Target] THEN 4 END)

END

Result :

IF ({ FIXED : MIN([z])})=1 THEN (IF [z]=2 THEN 2 ELSEIF [z]=3 THEN 3 ELSEIF [z]=4 THEN 4 ELSE 0 END)

ELSE 0

END

• ###### 4. Re: Help in formula - If match primary condition and secondary one then xx

Thanks a lot for your help.

Attach is the exact data I have.

As the data is coming from 2 different sources, I have to aggregate the value and then the result does not work anymore.

Any idea on how to do it?

Thanks