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]

You're welcome, def not madam