logical test, array, text and numbers

[Dan M.]

I am trying to create a formula in which excel searchs an array of alpha +
numeric values for a number >0 and if true, marks the formula's cell with an
"X". I have the logic of the formula set up like this: =IF('B1.1 &
B1.1.1'!E31:L31>0,"X","")

Excel is looking in the right place at the correct data and identifying the
cell with the value I'm looking for. However, it is returning the "#VALUE"
error message because it is assigning the "" value to those cells in the
array which are false to the argument and doesn't understand the intent of
the formula. The purpose of the formula is to simply look through an array
of values and tell me if a number greater than 0 exisits in any one of the
cells of the array.

 

[aidan.heritage]

Surely the problem is that the alpha numeric fields mean that they
won't take a numeric test - add in an ISNUMBER test first, so nest your
if statements - this should get round the problem