Tracking data in Excel

Greetings All -

I have a problem that is giving me fits. I want Excel to check a range of cells, and then 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. 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

 

Is P$11134 a correct cell range or a typo ?
regards

 

Thanks for the reply. That is a range of cells, not a typo.

I have more exact information on the nature of the problem. Type this into A1 in your copy of Excel (I use Excel 2000, but it shouldn't matter):

=IF(AND(B1:B2)=" ", "n ", "y ")

I expect to see "n" if there is no data in either B1 or B2, and a "y" if there's anything in either B1 or B2. It does display "y" if there is data in either cell, but I get the "#VALUE!" error message if the cells are empty. In short, the formula does not successfully test for "TRUE ".

Any help you could provide would be greatly appreciated. Thanks!

Rick

 

(IF(B1='" ") AND(B2=" "), "n ", "y "))

but for sure you have tried that ...
regards

 

Thanks for the second reply, Merlin. It turns out that I needed to reposition an end parentheses and do an "array save ":

{=IF(AND(B1:C2=" "), "n ", "y ")}

It seems to work fine now, but I'll be back if it blows up in production. <g> Thanks again!

Rick

 

Rick, pleased you got it sorted - I should have thought about
the curly bracket !
regards