Simplifying a formula

[MartinW]

Hi Group,
The following formula works fine but is a bit long, particularly as I will
have to
expand it to comparing 10 or 12 values in the future. Does anyone know of a
way to shrink it to something more usable and flexible.

=IF(C15=0,"",IF(AND(C15>=D15,C15<=E15,F15>=$M$7,
F15<=$N$7,G15>=H15,G15<=I15,J15>=$M$7,J15<=$N$7),"PASS","FAIL"))

What it does is take 4 values (C15,F15,G15 and J15) and compare each against
a different upper and lower limit, if all are within their limits then
"pass" if one misses
then "fail".

Thank you for any help,
Martin

 

[Bob Phillips]

You might find this easier to adapt

=CHOOSE((AND(C15>=D15,C15<=E15))*
(AND(F15>=$M$7,F15<=$N$7))*
(AND(G15>=H15,G15<=I15))*
(AND(J15>=$M$7,J15<=$N$7))+1,"FAIL","PASS")

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[MartinW]

Thanks Bob,
You knew what I wanted even though I didn't explain it very well.
It wasn't shrinking that I needed, just better structure so that others
could follow what I was doing without being bombarded by an
endless stream of references that would send them looking for
the headache pills.

Thanks again
Martin

 

[Bob Phillips]

Hi Martin,

You gave a big hint when you said ... I will have to expand it to comparing
10 or 12 values in the future ... <G>

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)