Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 25th 06, 07:54 AM posted to microsoft.public.excel.worksheet.functions
Dan M.
 
Posts: n/a
Default 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   Report Post  
Old April 25th 06, 08:56 AM posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Why are 1/2 my numbers imported as text and the rest as numbers? KBear Excel Discussion (Misc queries) 2 April 21st 06 01:40 PM
VLOOKUP should compare numbers stored as text to plain numbers. VLOOKUP - Numbers stored as text Excel Worksheet Functions 0 March 31st 06 05:53 PM
Numbers stored as text causes problem with VLOOKUP bpeltzer Excel Worksheet Functions 0 February 4th 06 09:07 PM
Convert text numbers to numbers Barb Excel Worksheet Functions 4 October 22nd 05 07:18 PM
Converting Numbers to Text properly Shirley Munro Excel Discussion (Misc queries) 1 February 16th 05 04:01 PM


All times are GMT +1. The time now is 09:30 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017