How to have empty cells in an array not effect final outcome.
Posted by Barbara on August 31, 2001 6:42 AM
I have an If statement that tests an array for values between two numbers returning a pass or fail. I would like any empty cells within the array to be ignored and not effect the final results, pass or fail. How do I do that?
Posted by Aladin Akyurek on August 31, 2001 6:56 AM
Care to post your IF-formula?
Aladin
Posted by Mark W. on August 31, 2001 7:44 AM
The array formula, {=SUM(ISNUMBER(A1:A5)*(A1:A5 > -1)*(A1:A5 < 7 ))},
will accurately count the number of values in the
cell range, A1:A5, that are greater than -1 and
less than 7.
Posted by Barbara on August 31, 2001 8:47 AM
I do not want to count, this is the formula, how do I put ISNUMBER in this and have it work?
=IF(AND(H14:H29<=28, H14:H29>=17),"Pass","Fail")
Posted by Barbara on August 31, 2001 8:55 AM
This is my formula, Thanks!
{=(IF(AND(H14:H29<=28,H14:H29>=17),"Pass","Fail"))}
Posted by Aladin Akyurek on August 31, 2001 9:05 AM
I believe the following ordinary formula will do what you want:
=IF(SUMPRODUCT((H14:H29>=17)*(H14:H29<=28))=COUNT(H14:H18),"Pass","Fail")
Barbara, I must admit I don't understand your valuation system for Pass/Fail. It seems you have to have scores between 17 and 28 inclusive to pass. That's the way I interpreted your question.
Aladin
========
Posted by Aladin Akyurek on August 31, 2001 9:14 AM
Typo: H18 must be H29 in COUNT! (NT)
Posted by Mark W. on August 31, 2001 11:45 AM
Try this!!!
=IF(OR(FREQUENCY(H14:H29,{16,27})*{1;0;1}),"Fail","Pass")
Posted by Mark W. on August 31, 2001 1:24 PM
Use this instead...
I was called away to a meeting and published my
formula in a hurry (and, a bit prematurely).
I made the rash assumption that your values were
integers and made a typo (27) on the upper limit.
Use this instead...
=IF(OR(FREQUENCY(H14:H29,{16.9999999999999,28})*{1;0;1}),"Fail","Pass")
Posted by Barbara on September 04, 2001 8:19 AM
Re: Typo: H18 must be H29 in COUNT! (NT)
Aldin,
Your latest formula confuses me but if you look at Mark's lastest formula it did work. Yes, I was trying to test for numbers between and including 17 and 28, without problems with not having numbers for some of the cells. Thanks so very much for your help.
Barbara
Posted by Barbara on September 04, 2001 8:21 AM
Re: Use this instead...
Thanks Mark this worked great...sorry for the delay.
Barbara
Posted by Aladin Akyurek on September 04, 2001 10:03 AM
Mine works too :)
Barbara,
The formula that I proposed works too.
=IF(SUMPRODUCT((H14:H29 >= 17)+0,(H14:H29 <= 28)+0)=COUNT(H14:H29),"Pass","Fail")
I had first a wrong cell ref and my latest post was referring to that. In the above formula I corrected that.
Aladin :)
Posted by Aladin Akyurek on September 04, 2001 10:18 AM
Re: Mine works too :)
Now, I'm confused. The formula that I wanted you to have was/is:
=IF(SUMPRODUCT((H14:H29>=17)*(H14:H29<=28))=COUNT(H14:H29),"Pass","Fail")
============== Barbara, The formula that I proposed works too. =IF(SUMPRODUCT((H14:H29 >= 17)+0,(H14:H29 <= 28)+0)=COUNT(H14:H29),"Pass","Fail") I had first a wrong cell ref and my latest post was referring to that. In the above formula I corrected that. :)
