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.

Question C8	Question C10	Question C13	Question C20	Question C21	Question C27	Question C33	ALL NRS < = 4
7	7	7	8	5	9	9
		2					ALL NRS < = 4
							ALL NRS < = 4
3	3				2	0	ALL NRS < = 4

<tbody>
</tbody>

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