Set of conditions that outputs specific value between a range of cells.

[timsharper]

Hi,

This might be a bit wordy so I apologise in advance.

Basically, I have a set of data where a range of cells display either "1. Agree", "2. Agree with Recommendations" or "3. Disagree".

For concreteness, let's assume that these are in A1:A5.

Essentially, I would like cell A6 to display one of "1. Agree", "2. Agree with Recommendations" or "3. Disagree" based on the following conditions:

- Display "1. Agree" if All cells A1:A5 are "1. Agree"

- Display "2. Agree with Recommendations" if at-least one cell is "2. Agree with Recommendations" and/or there are up to two "3. Disagree"'s between the cells A1:A5.

- Display "3. Disagree" if three or more cells in A1:A5 contain "3. Disagree".



I'm not sure whether this is a job for multiple COUNTIF's?

Any help would be appreciated! - Let me know if you have any questions!

Thanks,

Tim

 

[inactiveuserps07]

Tim, according to your rules above, if the five cells contained these values were, for instance ...

1. Agree
3. Disagree
3. Disagree
1. Agree
1. Agree

... the result would be "2. Agree with Recommendations" (based on your rule "at least one cell is '2. Agree with Recommendations' and/or​ there are up to two '3. Disagree's between the cells A1:A5.")

Is this what you expect?

If so, the following formula would work in A6 (though I'd recommend listing your three choices somewhere else, perhaps in another sheet, and referencing that list instead of hard-coding the replies into the formula, since that would allow more flexibility to change your wording down the line):

=IF(COUNTIF(A1:A5,"*1*")=5,"1. Agree",IF(OR(AND(COUNTIF(A1:A5,"*2*") > 0,COUNTIF(A1:A5,"*3*") < 3),COUNTIF(A1:A5,"*3*") < 3),"2. Agree with Recommendations","3. Disagree"))

 

[timsharper]

Hi Erik,

Yep that's correct!

It could, in the worst case be...

1. Agree with Recommendations
2. Agree with Recommendations
3. Agree with Recommendations
4. Disagree
5. Disagree

and it'd still display "2. Agree with Recommendations".

But yes that code has worked so thank you very much!



Now, what if there were to be a fourth option. Say "4. N/A" which we would essentially ignore if it popped up between A1:A5?

So for a quick example, lets say we have the following:

1. Agree
2. Agree
3. N/A
4. Agree
5. Agree

I'd want this to display "1. Agree", since the "3. N/A" isn't revelant/is looked over.

Thanks again,

Tim

 

[inactiveuserps07]

Hi Tim:

=IF(COUNTIF(A1:A5,"*1*")[COLOR=#ff0000]+COUNTIF(A1:A5,"*4*")[/COLOR]=5,"1. Agree",IF(OR(AND(COUNTIF(A1:A5,"*2*") > 0,COUNTIF(A1:A5,"*3*") < 3),COUNTIF(A1:A5,"*3*") < 3),"2. Agree with Recommendations","3. Disagree"))

 

[timsharper]

Hi Erik,

Thank you for getting back so fast.

Unfortunately that doesn't work for all the conditions. I'm looking for something where "4. N/A" essentially gets ignored in every case. So maybe treat it as a blank?

Thanks again,

Tim

 

[inactiveuserps07]

Tim, could you provide one or two examples of combinations that aren't working properly given inclusion of "4. N/A"?

 

[Aladin Akyurek]

Hi Erik,

Thank you for getting back so fast.

Unfortunately that doesn't work for all the conditions. I'm looking for something where "4. N/A" essentially gets ignored in every case. So maybe treat it as a blank?

Thanks again,

Tim

What if you have two N/A's? It's probably better to have a percentage rule:

1. Agree: if 100% (all) of the valid cells are 1. Agree.
2.

Agree with Recommendations: if X% of the valid cells are

2. Agree with Recommendations​

.
3.

Disagree: if Y% of the valid cells are

3. Disagree​

.

Try to specify X% and Y%.

Note. A cell is valid if it contains either Agree or Agree with Recommendations or Disagree.​

​

 

[timsharper]

Hi,

Yeah, I suppose that might work!

You could have;

Output: 1. Agree
If 100% of cells A1:A5 are "1. Agree"

Output: 2. Agree with Recommendations
If at-least (>=) 20% of cells A1:A5 are "2. Agree with Recommendations" and/or up to (<=) 40% of the cells A1:A5 are "3. Disagree"

Output: 3 . Disagree
If 60% (<=) or more of the cells A1:A5 are "3. Disagree"

I think that would work?

- I'm slowly learning the more complex functions of Excel so would be great to see how to do the above!

 

[timsharper]

Hi Erik,

So I've got:

2. Agree with Recommendations
2. Agree with Recommendations
1. Agree
4. N/A
1. Agree

and that one is displaying "1. Agree"?

---

Have a look at what I had replied to Aladin, they might be onto something!

Thanks

Tim

 

[inactiveuserps07]

Tim, I don't see how your last set ...

2. Agree with Recommendations
2. Agree with Recommendations
1. Agree
4. N/A
1. Agree

... could result in "1. Agree" given the formula I provided to you (here again for your convenience):

[/COLOR]=IF(COUNTIF(A1:A5,"*1*")+COUNTIF(A1:A5,"*4*")=5,"1. Agree",IF(OR(AND(COUNTIF(A1:A5,"*2*") > 0,COUNTIF(A1:A5,"*3*") < 3),COUNTIF(A1:A5,"*3*") < 3),"2. Agree with Recommendations","3. Disagree"))[COLOR=#333333]

I also tested your set with this formula and receive the expected "2. Agree with Recommendations."

So I'm not sure what might be going wrong on your end.