Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical test, array, text and numbers
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:L310,"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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
logical test, array, text and numbers
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Numbers stored as text causes problem with VLOOKUP | Excel Worksheet Functions | |||
Convert text numbers to numbers | Excel Worksheet Functions | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) |