Building a logic for multiple conditions

[zinah]

I need to build logic for below statement:

If 2018 Favorability % is greater than 2017 Favorability %, and if 2018 Favorability % is greater than or equal to 70% and if the difference of Favorability percentages 0f 2018-2017 is greater than or equal to 10%, then “positive”, other than that “negative”. And if the difference of 2018 & 2017 percentages is less than 10% then “ignore”.

Below is a sample table of my scenario and I tried to justify why the reason of each outcome. Can you help with building the right logic/formula?





2017 Fav 2018 Fav Diff. Outcome Justification
80% 65% -15% Bad
45% 55% 10% Ignore
70% 85% 15% Good more than 70% and Diff is more than 10%
49% 53% 4% Ignore
65% 59% -6% Ignore
50% 60% 10% Ignore
55% 75% 20% Good more than 70% and Diff is more than 10%
70% 75% 5% Good more than 70%
50% 69% 19% Good more than 10% improvement and almost 70%
80% 79% -1% Good more than 70% improvement
79% 80% 1% Good more than 70% improvement
20% 49% 29% Bad yes, it's good improvement but it's less than 70%
47% 59% 12% Ignore
83% 80% -3% Good more than 70% improvement
74% 70% -4% Good 70% is good
74% 69% -5% Ignore
80% 70% -10% Ignore
85% 70% -15% Ignore
65% 70% 5% Good increased
64% 69% 5% Ignore

 

[zinah]

Hi,

Can anyone help please?
I'm not sure how can I paste the table, as I tried to paste and couldn't and I don't know how to insert the table as picture.

Thanks

 

[StephenCrump]

Your "logic" doesn't match the results you have provided, so you will need to provide more detail.

Here is one possible formula that replicates your "Good" results. I have used 69% as the boundary, because this is
"close" to 70%.

E2: =IF(AND(B2>=69%,C2>-5%),"Good","")

I don't know how you differentiate between "Ignore" and "Bad"?

Book1
	A	B	C	D	E
1	2017 Fav	2018 Fav	Diff	Your outcome	My outcome
2	80%	65%	-15%	Bad
3	45%	55%	10%	Ignore
4	70%	85%	15%	Good	Good
5	49%	53%	4%	Ignore
6	65%	59%	-6%	Ignore
7	50%	60%	10%	Ignore
8	55%	75%	20%	Good	Good
9	70%	75%	5%	Good	Good
10	50%	69%	19%	Good	Good
11	80%	79%	-1%	Good	Good
12	79%	80%	1%	Good	Good
13	20%	49%	29%	Bad
14	47%	59%	12%	Ignore
15	83%	80%	-3%	Good	Good
16	74%	70%	-4%	Good	Good
17	74%	69%	-5%	Ignore
18	80%	70%	-10%	Ignore
19	85%	70%	-15%	Ignore
20	65%	70%	5%	Good	Good
21	64%	69%	5%	Ignore	Good
Sheet1

 

[zinah]

Thank you Stephen for your update. Here's what my logic is:

If 2018 Fav >2017 Fav, and If 2018 Fav is >= 70% and if Diff is >= 10%, then it's Good, otherwise Bad, and if Diff<10% & >0% and 2018 Fav < 70% then Ignore.
For Ignore situation: if 2017 Fav = 49% and 2017 Fav = 55% then Ignore
If 2017 Fav = 55% and 2018 Fav = 65% then Ignore (as the % is less than 70%)

Hope I clarified my logic. Can you help?

 

[StephenCrump]

If 2018 Fav >2017 Fav, and If 2018 Fav is >= 70% and if Diff is >= 10%, then it's Good, otherwise Bad, and if Diff<10% & >0% and 2018 Fav < 70% then Ignore.

This logic gives:

E2: =IF(AND(B2>=70%,C2>=10%),"Good",IF(AND(C2<10%,C2>0,B2<70%),"Ignore","Bad"))

Book1
	A	B	C	D	E
1	2017 Fav	2018 Fav	Diff	Your outcome	My outcome
2	80%	65%	-15%	Bad	Bad
3	45%	55%	10%	Ignore	Bad
4	70%	85%	15%	Good	Good
5	49%	53%	4%	Ignore	Ignore
6	65%	59%	-6%	Ignore	Bad
7	50%	60%	10%	Ignore	Bad
8	55%	75%	20%	Good	Good
9	70%	75%	5%	Good	Bad
10	50%	69%	19%	Good	Bad
11	80%	79%	-1%	Good	Bad
12	79%	80%	1%	Good	Bad
13	20%	49%	29%	Bad	Bad
14	47%	59%	12%	Ignore	Bad
15	83%	80%	-3%	Good	Bad
16	74%	70%	-4%	Good	Bad
17	74%	69%	-5%	Ignore	Bad
18	80%	70%	-10%	Ignore	Bad
19	85%	70%	-15%	Ignore	Bad
20	65%	70%	5%	Good	Bad
21	64%	69%	5%	Ignore	Ignore
Sheet1

 

[zinah]

Thank you Stephen, you gave me an insight to build the logic:

=IF(AND(B2>A2,B2>=70%),"Good",IF(AND(B2<a2,b2>70%),"Good",IF(AND(B2>A2,B2>=69%,C2>=15%),"Good",IF(AND(B2<70%,C2>10%),"Ignore",IF(AND(B2<a2,b2=69%,c2<=5%),"ignore",if(and(b2<a2,b2<70%,c2<0),"bad",if(and(b2<a2,b2<70%),"bad",if(and(c2<10%,c2<0),"bad","ignore"))))))))< html=""></a2,b2=69%,c2<=5%),"ignore",if(and(b2<a2,b2<70%,c2<0),"bad",if(and(b2<a2,b2<70%),"bad",if(and(c2<10%,c2<0),"bad","ignore"))))))))<></a2,b2>

 

[StephenCrump]

Great! Well done for figuring it out.

Your formula is getting truncated because ">' characters are sometimes interpreted as HTML.

Put a space before and after: " > " and you'll be able to post your complete formula.

 

[zinah]

Thank you so much Stephen!

here's the formula:

IF(AND(B12 > A12,B12 >= 70%),"Good",IF(AND(B12 < A12,B12 > 70%),"Good",IF(AND(B12 > A12,B12 >= 69%,C1 2 >= 15%),"Good",IF(AND(B12 < 70%,C12 > 10%),"Ignore",IF(AND(B12 < A12,B12=69%,C12 <= 5%),"Ignore",IF(AND(B12 < A12,B12 < 70%,C12 < 0),"Bad",IF(AND(B12 < A12,B12 < 70%),"Bad",IF(AND(C12 < 10%,C12 < 0),"Bad","Ignore"))))))))

 

[StephenCrump]

Thanks for posting your formula. However, this is not consistent with what you've said in previous posts, and there are some redundancies and boundary gaps in the formula itself, e.g. if 2017 Fav and 2018 Fav are both 100%, your formula returns "Bad".

I am guessing that what you're looking for is as simple as:

Good:
2018 Fav >=70%
or
2018 Fav >=69% and Increase >=15%

Ignore:
Increase >10%
or
2018 Fav >=69% and Increase >=5%

Bad:
Everything else

In which case:

D2: =IF(OR(B2>=70%,AND(B2>=69%,C2>=15%)),"Good",IF(OR(C2>10%,AND(B2>=69%,C2>5%)),"Ignore","Bad"))

Book1
	A	B	C	D	E
1	2017 Fav	2018 Fav	Diff	Outcome	Reason
2	60%	70%	10%	Good	2018 Fav >=70%
3	70%	70%	0%	Good	2018 Fav >=70%
4	80%	70%	-10%	Good	2018 Fav >=70%
5	90%	70%	-20%	Good	2018 Fav >=70%
6	100%	70%	-30%	Good	2018 Fav >=70%
7
8	54%	69%	15%	Good	Diff >= 15%
9	55%	69%	14%	Ignore	Diff > 5%
10	63%	69%	6%	Ignore	Diff > 5%
11	64%	69%	5%	Bad	Diff<= 5%
12	70%	69%	-1%	Bad	Diff <= 5%
13
14	57%	68%	11%	Ignore	Diff > 10%
15	58%	68%	10%	Bad	Diff <= 10%
16	59%	68%	9%	Bad	Diff <= 10%
17	70%	68%	-2%	Bad	Diff <= 10%
18
19	10%	60%	50%	Ignore	Diff > 10%
20	49%	60%	11%	Ignore	Diff > 10%
21	50%	60%	10%	Bad	Diff <= 10%
Sheet1