Using Logical Formulae to determine if all cells are equal to or below a given number, and not blank

[kjgarbutt]

Hi all,

I am analysing some survey data and I am trying to group a set number of questions together so that I can determine if a patient has rated their pain equal to or below 4 (out of 10) for ALL of the chosen questions. An example of the data is below. As well as the formula I am currently using.

The problem I have noticed is that the formula will show "ALL < = 4" even if NO data is in the cells. Logically this makes sense, but a 0 and blank cell are very different in this respect. What I need is:

IF a number is present AND it is EQUAL to or LESS than 4 in each of these cells (ATI2,ATQ2,...), RETURN "ALL < = 4", ELSE RETURN " " (nothing!).

I'm not entirely sure how to do that. Any help or guidance as to how best to do this would be much appreciated.


[TABLE="width: 800"]
<tbody>[TR]
[TD]Question C8[/TD]
[TD]Question C10[/TD]
[TD]Question C13[/TD]
[TD]Question C20[/TD]
[TD]Question C21[/TD]
[TD]Question C27[/TD]
[TD]Question C33[/TD]
[TD]ALL NRS < = 4[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ALL NRS < = 4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ALL NRS < = 4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]ALL NRS < = 4[/TD]
[/TR]
</tbody>[/TABLE]


=IF((AND(ATI2<=4, ATQ2<=4,ATS2<=4,AVV2<=4,AVW2<=4, AWR2<=4,BAF2<=4)), "ALL < = 4", " ")

 

[gaz_chops]

Welcome to Mr Excel,

Try

=IF(AND(COUNTA(A2:G2)>=1,COUNTIFS(A2:G2,"<="&4)=COUNTA(A2:G2)),"All NRS <= 4","")

 

[kjgarbutt]

THANK YOU gaz_chops !


This is so useful and will be used A LOT!

 

[gaz_chops]

You're welcome

Merry Xmas

 

[mikerickson]

How about =IF(COUNTIFS(A2:G2, ">4")>0, "", "All l.t. 4")

 

[kjgarbutt]

Merry Christmas to you too!

Actually, I have a related question: how do I do the same thing, but this time I want to determine if AT LEAST 1 of the cells is LESS than or EQUAL to 4, but NOT including blanks cells?

I've tried to adapt your earlier formula, but I think it needs an OR somewhere. Not sure.

=IF(AND(COUNTA(BCH2:BCN2)>=1,COUNTIFS(BCH2:BCN2,"<"&3)=COUNTA(BCH2:BCN2)),"AT LEAST 1 < 3","")

 

[kjgarbutt]

Merry Christmas to you too gaz_chops!


Actually, I have a related question: how do I do the same thing, but this time I want to determine if AT LEAST 1 of the cells is LESS than or EQUAL to 4, but NOT including blanks cells?


I've tried to adapt your earlier formula, but I think it needs an OR somewhere. Not sure.

=IF(AND(COUNTA(BCH2:BCN2)>=1,COUNTIFS(BCH2:BCN2,"<"&4)=COUNTA(BCH2:BCN2)),"AT LEAST 1 <= 4","")

Note: messed up formatting in an earlier reply and couldn't see an 'edit' button.

 

[gaz_chops]

Does my formula not do that already?

 

[gaz_chops]

Misread, try

=IF(AND(COUNTA(A2:G2)>=1,COUNTIFS(A2:G2,"<="&4)),"All NRS <= 4","")

Change text at end "ALL NRS....." to "AT LEAST...."

 

[kjgarbutt]

You sir or madam are my festive hero!

I need to up my Excel formula knowledge. Thank you so much for the help.