• 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.

Help ! Multiple Condition Logic

Laracroft

New Member
If Target achieved is less than or equal to 90% but more than 50% then result should be Difference of Target set and Target achieved but if Target achieved is equal to or less than 50% the result should be 50%.
And If Target achieved is more than 90% , then result should be NA.
Target SetTarget AchievedResult
1st Condition
100​
Less than or Equal to 90 but More than 50Difference of Target Set and Target Achieved
2nd Condition
100​
More Than 90NA
3rd Condition
100​
Less than or Equal to 50Then result should return to 50 Only and not difference

Please let me know how to write this logic in excel Cell.
 

GraH - Guido

Well-Known Member
Hi Laracroft, please take a minute to read the forum rules. And understand we need an representative example workbook to provide better help.

IF ( (Actual/Target ) <=50%, "50%", IF ( (Actual/Target ) >90% , NA() , TEXT ( Target - Actual ; "0.0%") & " Target Achieved" ) )

Where Actual and Target are cell references to your corresponding values.

EDIT: other solutions are possible too, like a (v)lookup using a reference table.
 
Last edited:

Laracroft

New Member
Thank you Grah. I have another query on how should i enter formula in Horizontal way, keeping the value of each cell getting changed based on "Result" . Attaching the file where i have provided all the conditions and references.
 

Attachments

GraH - Guido

Well-Known Member
Hi Laracroft,
The way I understand your query, you can't keep a formula result, while changing a cell that influences the calculation. At that point Excel will "recalculate".
There is potentially a way with "iterative" calculations on, but I would not recommend it. Other alternative is macros. But then you have posted this in the wrong forum.
Changing you data lay-out, where the dates are not columns but rows, might be a better way to enter the data. Since you would then enter your dynamic values for each period, the calculation works for reach row.
 

Laracroft

New Member
Thanks Grah.. How about for 2nd sheet which is 'earn back' and has various conditions attached for calculating earn back. How to write the formula there . It should have reference of 1st Sheet "penalty". Can you please guide me with writing the formula there.?
 

GraH - Guido

Well-Known Member
I don't have much time now. Can you make a manual example of the solution + how you did the manual calculation. That helps in understanding the logic, sometimes better then words.
If nobody else helped, I will look again this evening or tomorrow.
 

Laracroft

New Member
i applied the 'count if' formula.. i.e. if the target in over achieved for continuous 3 month. i,e value IF(Penalty!G3,'Not Achieved', COUNTIF(Earnback!B5>Penalty!K3,COUNTIF(Penalty!F3>Penalty!K3,COUNTIF(Penalty!L3>Penalty!F3). but i think this is not correct way of my applying the formula.
 

GraH - Guido

Well-Known Member
Sorry, I don't have much availability right now to analyse.
[EDIT] still missing a manual example in your workbook... As requested in #6. That would help me, help you...
 
Last edited:
Top