A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

logical test, array, text and numbers



 
 
Thread Tools Display Modes
  #1  
Old April 25th 06, 07:54 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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: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.
Ads
  #2  
Old April 25th 06, 08:56 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 08: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 03:01 PM


All times are GMT +1. The time now is 10:25 AM.


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