Formula to check cells for any content

[Rick Manasa]

Greetings All -

I have a problem that is giving me fits. I want Excel to check a range
of cells, and branch left or right, depending on if there's anything in
any cell in the range. Here's how the formula looks:

=IF((P$11134=""),"",IF(E134<MIN(E114:E133),E134,""))

The second IF statement works just fine by itself. The first IF
statement is to keep the spreadsheet "neat", by preventing the second
IF from calculating and displaying data I don't need.

Here's what's really got me boggled: If the range *ends* on a cell that
has data, the formula branches to the second IF. If the range merely
*contains* a cell with data - one that isn't the last cell of the range
- it doesn't branch to the second IF. Weird, huh?

Let me know if you need more information. Any help appreciated.
Thanks!

Rick

 

[Jason Morin]

When you equate a range to something (like P$11134=""),
then it produces an array of values like
{FALSE,FALSE,TRUE,...}. So you have to manipulate the
array. Try:

=IF(SUM(N(P$11134=""))>0,"",IF(E134<MIN
(E114:E133),E134,""))

Array-entered (meaning press ctrl/shift/enter, not just
enter).

HTH
Jason
Atlanta, GA

 

[Aladin Akyurek]

One of:

=IF(COUNT(P$11134),IF(E134<MIN(E114:E133),E134,""),"")

=IF(COUNTIF(P$11134,"<>"),IF(E134<MIN(E114:E133),E134,""),"")

(P$11134=""), which you're using as a condition, returns a computed array
(not a scalar/single value) your formula cannot handle.

 

[Rick Manasa]

Thanks for the reply, Aladin. I am amazed at the varieties of solutions
(all workable!) that have been proposed to this problem. Right now, I'm
testing this particular offering:

IF(SUM(N(P$11134=""))>0,"",IF(E134<MIN(E114:E133),E134,""))


This seems to be working fine. If it doesn't hold up in the field, I'll
give your solution a try. Thanks again!

Rick

 

[Rick Manasa]

Thanks to Jason for posting the "winning entry". <g> We're testing his
solution now.

Thanks Jason!

Rick