Printing with formula

[Claudio]

I am trying to check the value of a range of cells and print PASS or Fail,
depending if all the cells fall within those parameters or not.
This is what I am using:

=IF(AND(A590:J590<41, A590:J590>33),"PASS","FAIL")

It seems that it only checks the first cell, A590. If I change the value of
any of the other cells, it doesn't work.
--
Claudio

[bpeltzer]

To avoid an array formula, I'd use sumproduct:
=if(sumproduct(--(a590:j590<41),--(a590:j590>33))=10,"PASS","FAIL")
(If you know that the cells a590:j590 are numeric, you could use
count(a590:j590) rather than the number 10.)


"Claudio" wrote:

> I am trying to check the value of a range of cells and print PASS or Fail,
> depending if all the cells fall within those parameters or not.
> This is what I am using:
>
> =IF(AND(A590:J590<41, A590:J590>33),"PASS","FAIL")
>
> It seems that it only checks the first cell, A590. If I change the value of
> any of the other cells, it doesn't work.
> --
> Claudio
>

[Bernard Liengme]

You cannot use Boolean (AND , OR) with an array formula.
Try this
=IF(SUMPRODUCT(--(A1:A4>33),--(A1:A4<41))=COUNT(A1:A4),"PASS","FAIL") you
commit it with a simple ENTER; it is not an array formula.
Just change the cell references - I needed something simple to test.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Claudio" <Claudio@discussions.microsoft.com> wrote in message
news:94579115-3E3B-4838-9CF9-AF95EE4109DE@microsoft.com...
>I am trying to check the value of a range of cells and print PASS or Fail,
> depending if all the cells fall within those parameters or not.
> This is what I am using:
>
> =IF(AND(A590:J590<41, A590:J590>33),"PASS","FAIL")
>
> It seems that it only checks the first cell, A590. If I change the value
> of
> any of the other cells, it doesn't work.
> --
> Claudio
>

[Claudio]

That worked great, thank you.

Claudio


"bpeltzer" wrote:

> To avoid an array formula, I'd use sumproduct:
> =if(sumproduct(--(a590:j590<41),--(a590:j590>33))=10,"PASS","FAIL")
> (If you know that the cells a590:j590 are numeric, you could use
> count(a590:j590) rather than the number 10.)
>
>
> "Claudio" wrote:
>
> > I am trying to check the value of a range of cells and print PASS or Fail,
> > depending if all the cells fall within those parameters or not.
> > This is what I am using:
> >
> > =IF(AND(A590:J590<41, A590:J590>33),"PASS","FAIL")
> >
> > It seems that it only checks the first cell, A590. If I change the value of
> > any of the other cells, it doesn't work.
> > --
> > Claudio
> >