For Mike H

[dbconn]

Mike,
Earlier today you gave me the following formula
=IF(AND(D5>=5,D5<=12,M32>95),"Eligible","") intended to return a value of
Eligible if D5 was >=5 and ,=12 AND M32 is >95. I changed it slightly to
=IF(AND(D5>=5,D5<=12,M32>=96),"Eligible","") so that a value like 95.5 would
not work.

I have found that I get the ELIGIBLE value when I enter a valid number in
D5, even when M32 is still blank. M32 has a formula in in as follows
=IF(ISERROR(AVERAGEIF(M14:M28,"<>0",M14:M28)),"
",AVERAGEIF(M14:M28,"<>0",M14:M28))

Is there a way to change the formula you gave me so that ELIGIBLE will only
return if D5 is between 5 and 12 AND there is an actual value in M32 that is

=96?

Thanks much

 

[Mike H]

Hi,

I think this is what you want

=IF(AND(D5>=5,D5<=12,AND(M32<>"",M32>=96)),"Eligible","")

Note when doing these yourself you seem to have a (bad) habit if inserting a
space
" " instead of a null string "" for the false condition. As a general rule
it is better to return a NULL string for your false condition.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[dbconn]

You are correct that I use the " " with the space. I thought that in most
formulas this would make the cell blank if there was a false value, as in
"ELIGIBLE", " ", this would return eligible if true and would be blank if
false, which is the result I am looking for

Thanks so much

 

[David Biddulph]

There is a difference between a space " " and the null string "". Strictly
neither of these is the same as a blank cell, but a test for ="" will return
TRUE for either a blank cell or a cell in which "" is returned by a formula,
so I would support Mike's recommendation that you use "", and not " ".