Testing a range of cells in an IF FUNCTION

Trying to test a range of cells in an IF Function.  I would like the function to look at 15 cells in a single row, find a value within those cells, and then return another value if TRUE/FALSE.  As of now, I can only apply the "logical test" in the IF formula for ONE CELL ONLY.  I would like it to test a RANGE of cells in one row, find if any of the values match my logical test, and then return a value.  Anyone understand or know how to do this?  I tried apply "lookup" function, but I don't think it will work

Please help

Aaro
aaronplange at hotmail.com
0
anonymous (74722)
4/2/2004 6:11:09 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
328 Views

Similar Articles

[PageSpeed] 5

Hi
maybe something like
=IF(COUNTIF(A1:A100,"your value")>1,"found","not found")

or
IF(ISNA(VLOOKUP("your_value",A1:B100,2,0)),"not
found",VLOOKUP("your_value",A1:B100,2,0))


--
Regards
Frank Kabel
Frankfurt, Germany


Aaron wrote:
> Trying to test a range of cells in an IF Function.  I would like the
> function to look at 15 cells in a single row, find a value within
> those cells, and then return another value if TRUE/FALSE.  As of now,
> I can only apply the "logical test" in the IF formula for ONE CELL
> ONLY.  I would like it to test a RANGE of cells in one row, find if
> any of the values match my logical test, and then return a value.
> Anyone understand or know how to do this?  I tried apply "lookup"
> function, but I don't think it will work.
>
> Please help!
>
> Aaron
> aaronplange at hotmail.com

0
frank.kabel (11126)
4/2/2004 6:20:08 PM
Hi Aaron

You can use the COUNTIF function

=IF(COUNTIF(A1:O1,"yourvalue")>0,"yes","No")

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Aaron" <anonymous@discussions.microsoft.com> wrote in message news:19846C57-6D1F-46E3-843D-159323B69B6F@microsoft.com...
> Trying to test a range of cells in an IF Function.  I would like the function to look at 15 cells in a single row, find a value
within those cells, and then return another value if TRUE/FALSE.  As of now, I can only apply the "logical test" in the IF formula
for ONE CELL ONLY.  I would like it to test a RANGE of cells in one row, find if any of the values match my logical test, and then
return a value.  Anyone understand or know how to do this?  I tried apply "lookup" function, but I don't think it will work.
>
> Please help!
>
> Aaron
> aaronplange at hotmail.com


0
rondebruin (3790)
4/2/2004 6:23:21 PM
Aaron,

Try something like

=NOT(ISNA(MATCH("user01",A2:H2,0)))

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Aaron" <anonymous@discussions.microsoft.com> wrote in message
news:19846C57-6D1F-46E3-843D-159323B69B6F@microsoft.com...
> Trying to test a range of cells in an IF Function.  I would like the
function to look at 15 cells in a single row, find a value within those
cells, and then return another value if TRUE/FALSE.  As of now, I can only
apply the "logical test" in the IF formula for ONE CELL ONLY.  I would like
it to test a RANGE of cells in one row, find if any of the values match my
logical test, and then return a value.  Anyone understand or know how to do
this?  I tried apply "lookup" function, but I don't think it will work.
>
> Please help!
>
> Aaron
> aaronplange at hotmail.com


0
bob.phillips1 (6510)
4/2/2004 6:25:51 PM
Testing to see if 2 exists in the range A1:O1:

=IF(SUM(--(A1:O1=2))>0,TRUE,FALSE)

Array-entered, meaning press ctrl/shift/enter.

Although this is simpler:

=IF(COUNTIF(A1:O1,2),TRUE,FALSE)

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Trying to test a range of cells in an IF Function.  I 
would like the function to look at 15 cells in a single 
row, find a value within those cells, and then return 
another value if TRUE/FALSE.  As of now, I can only apply 
the "logical test" in the IF formula for ONE CELL ONLY.  I 
would like it to test a RANGE of cells in one row, find if 
any of the values match my logical test, and then return a 
value.  Anyone understand or know how to do this?  I tried 
apply "lookup" function, but I don't think it will work.
>
>Please help!
>
>Aaron
>aaronplange at hotmail.com
>.
>
0
jason.morin (561)
4/2/2004 6:28:12 PM
Reply:

Similar Artilces:

Avoid protected cell warning on BeforeDoubleClick WS event
I am creating an event scheduling worksheet. A grid is generated with days of the month x-axis and users y-axis. Where an event occurs this is logged by a hidden event ID in the appropriate day cell. In order to prevent this ID from being overwritten I protect the worksheet. What I am trying to do is have a worksheet DoubleClick event which either. 1) Captures the event ID in the underlying cell, and opens a custom form for editing the event, or 2) Recognises that no event exists and opens a custom form for logging a new event. What I have tried is to unprotect the workshee...

Click in cell w/ formula and get colors in referenced cells
Hi, When you click in a cell with a formula, Excel will then put color around the cells that are referenced. Somehow I turned that feature off. Now I do not get colors in th other cells. Where is that option to turn it back on???? I've looke and looked but I just can't find that option. Thanks for the help -- albea ----------------------------------------------------------------------- albean's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2875 View this thread: http://www.excelforum.com/showthread.php?threadid=48436 ...

Miscellaneous words appear in Excel 2002 cells
I am working on an Excell 2002 workbook and when I put my cursor in a cell , spurious words get entered into the cell e.g. "and thee the and ...." is one example. I have cable modem and it feels like "someone" has taken control of my PC - by the way I have also noticed this hapening in MS Word but is not as bad as Excel - can some one help. thanks KK Have you enabled speech tools? Click Tools / Speech and examine your options. /i. "KK" <anonymous@discussions.microsoft.com> wrote in message news:061d01c39d1c$36fc85f0$a601280a@phx.gbl... > I am w...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

test 05-11-10
hiiiiiiiiiiiiii ...

outlook test account settings error: POP3
I receive an Outlook 2002 error message regarding my account (POP3)even though I am using the same successful settings as in Outlook Express. The error message is The POP3 e-mail account you created does not support Secure Password Authentication (SPA). 1. The account used does work with SPA. I have used it successfully on other computers and on the same computer using Outlook Express. 2. The connection is through AT&T WorldNet and the operating system is Windows XP. 3. The account is an administrator account. 4. The server I am attempting to connect with is a Exchange 5...

Upgrading to v3.0 & Utilizing a Test Server
I would like to be able to build a test environment to test upgrade a real copy of our production CRM db (v1.2) and test the upgrade to v3. I know the best way to build a test copy is a clean install of 1.2 and migrate data and import our current schema. But that does not give me my actual db in test, all it gives me is data and schema. I want to be able to have a test db the mirrors production with all the store procedures and triggers we have or may have in place. That way I know really what will happen when I upgrade. Is there a way to do this? I have a second domain that I can use...

Export a range to a text file
Hello need some advise on how to procede I need to be able to create a text file containg some text as well as data that is within a named range in excel and then some more text. I can handle printing to the text files using cell values etc but am unsure of the best way to print the ranges data. Is there a way or procedure to just print the range as is in csv format? As well my range will contain about 6 columns, each containg a number field (formatting of decimal places is important, some have 2 dec some 3 etc) Also the range has a max of 50 rows however will always contain lower rows of...

Seeking Improvement on excel function
Dear sir, I have one question regarding an "index" formular together with "match". I used to set the following excel function to pick the data from the database: =INDEX("database",MATCH("column argument","column range",0),"target column range"). One of key thing of this excel funation is "target column range", which only allow to cover ONE column only. My question: Is there any way to improve or change the above excel function in order to make "target column range" can cover more column ranges. I have also...

Formatting multiple object types in output from a function?
I'm stumped on this one, and have searched and searched for a solution or how-to with no luck. Perhaps it just can't be done. I have a function that may or may not return objects of multiple types. I've setup the type and formatting information for each of the individual types in format.ps1xml files, and when only 1 type is returned by the function the formatting works as I would expect. However, if two or more types are returned by the function, only the formatting for the first object type is applied and then the other types are displayed however PoSH deems ap...

Discrete functions: max and min relatives
Hy guys: here is the thing, let=B4s say column A has a list of 500 values (discrete function). Is there a way to identify max and min relatives in that list ? (of course, I=B4m not interested in max and min absolutes!!!) . I guess some type of criteria need to be done. The result could be something like: Colum A, Column B,,,,,, A1 A2 I'm a max rel. .. A40 I=B4m a max rel. .. .. A105 I'm a min rel. .. .. A500 One way, if there are no local duplications: in B2: =CHOOSE(2-(A2>A1)*(A2>A3)+(A2<A1)*(A2<A3),"rel max","","rel min...

Sum of a cell in all worksheets?
I usually just use the search here and quickly find what I need, but can't seem to get it work this morning I am trying to get a sum of a certain cell in all the sheets in workbook. For example of what I mean, Sum(all worksheets, d64). Ca someone possibly help me out Thanks Michae Hi Michael, Look up 3D in your Excel Help. Surely you do not want to include the same sheet. if sheet2 is your second sheet tab and "sheet 24" is the last tab. =SUM(sheet2:'sheet 24'!C14) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: htt...

Insert a graphic in a cell
I'd like to be able to enbed a logo in a cell. Not possible. Graphic objects reside on the drawing layer "above" the cells. Cells can contain formulae or values only. Workaround. Insert your graphic, and size it so that it exactly covers the cell. Right-click it, choosing Format Picture. In the Properties tab of the Format Picture dialog, choose the Move and Size with cells radio button. In article <F253C07B-E71A-445E-B612-0189187A09D9@microsoft.com>, Pete_Escher fan <Pete_Escher fan@discussions.microsoft.com> wrote: > I'd like to be able to enbed a...

Routines or functions to do conversions
I do a lot of conversions of different types most frequently involving measurements, for example from square meters to square feet, gallons to cubic feet, etc (sometimes involving unusual measures such as Chinese mu, a measure of area). Are there any functions or routines to do conversions? Also, if I keep constants such as SFPerM2 (square feet per square meter), is there an easy way to make them quickly accessible to all my spreadsheets or new spreadsheets? Thanks for any help Huck Excel has a Convert Function in the Analysis Toolpak. I use a handy utility called Converter, f...

LOOKUP Function #6
I discovered LOOKUP today and it worked fine until I made changes to the table of data. Now it's returning incorrect figures. Any suggestions? AC Sales wrote: > I discovered LOOKUP today and it worked fine until I made changes to the > table of data. Now it's returning incorrect figures. Any suggestions? --------- For LOOKUP() to work properly the data all has to be sorted into order first. Did you perhaps put an out of sequence value into the table? Bill ...

keeping footer from resizing when using the fit to page function
How do I keep the footer from shrinking in excel when i use the "fit to page" option for my worksheets. I'd like it to stay the same size no matter what the font of the worksheet is. ...

can one cell contain more than one independent number
A cell can contain several numbers, separated by space characters or line breaks (Alt+Enter). However, if you want to perform calculations on the numbers, it's best to keep them in separate cells. John K wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html there's also the option a using matrix formula (list of data) for example you may enter ={1;2;3} in a single cell or selection 'and validate wih CTRL+SHIFT+ENTER but the manipulation of this kinda of formula need to read more about it... ...

Ignore Blank Cells
I update a workbook weekly in separate worksheets. My master worksheet links to the appropriate cells for the updated data. These are simple percentage numbers (not forumlas, etc.) and only need to be a one-on-one link. The problem I have is that if one of the cells is blank, it returns a 0 and I need to to stay blank. Any ideas? There are about 57,000 cells I am working with so I really don't want to have to update manually all blanks. Hi SEF, As long as you want to hide all zero values on a sheet you can use in excel 2003 From the Tools menu select Options On tab V...

Conditional formatting / blank cells
Hello, I need help with a Conditional Format. This is my worksheet. Row 4 A B C D E F G H I $200 $210 I want a conditional format in G4 that states if G4 is greater than or equal to F4 the fill colour is green. If G4 is less than F4, the fill colour is red. If G4 is blank, the fill colour is white. I've tried numerous combinations, but cannot seem to get this to work. Thanks torkattack. Test for the blank first. -- David Biddulph "torkattack" <torkattack@discussions.microsoft.com> wrote ...

Clipart will not scale with array of cells
I want to print out an array of cells, some of which contain clipart. When I try to scale up the array to fill the printed page, the clipart scales differently (the clipart objects move down on the page from their normal position within the cells). The artwork size does not change, nor its spacing; it's as though the clipart is reacting to a larger top margin. ...

Missing Function in Excel 2003
Dear All In Excel 2000 I used to use a function called "MS Access Form" , which was located under the DATA menu. Since I have upgraded the Office version to 2003, I cannot find this function anywhere in Excel. This function takes a highlighted table, and makes a suitable entry form in Access to facilitate the data entry process Best Regards and Thanks in advance Ahmed Gaafar You need to load the Access Links Add-In from the Tools menu. HTH, Greg "Ahmed Gaafar" <elameen@intouch.com> wrote in message news:1c60a01c42218$270e3ae0$a401280a@phx.gbl... > Dear...

test #65
test RICK BORGMAN <rborgman@worldnet.att.net> wrote: > test Wrong newsgroup for test messages. -- Brian Tillman ...

MB2-631 'Customization and Configuration' Cert Test Anyone?
Man, I got a 50% on my MB2-631 today, and I really thought I was prepared. I've done nothing but CRM 4.0 for 6 months, and read a couple of books on it, studed the 8912 pdf, etc. I don't know how I hosed it up so bad, I could have sworn I did much better. Most of the questions I thought I knew the answers to, or thought I picked the right answers at least. I know some I probably read too fast and will get next time. Anyone know of any good, non-braindump sites or materials to study? I have the Second Shot lined up, so I know some of what I got wrong and what to look over more, but boy...

SendMessage vs function call
Hello, I wonder is there any difference in using SendMessage() and calling a function directly when in the same class, e.g. CView? // Anders -- English is not my first, or second, language so anything strange, or insulting, is due to the translation. Please correct me so I may improve my English! >I wonder is there any difference in using SendMessage() and calling a >function directly when in the same class, e.g. CView? They are obviously different but perhaps the most significant difference would be if you have different threads in your application. If you call a function it'l...

formula for visible cells
Can I copy a worksheet so that the new worksheet looks exactly like the visible portion of the old worksheet. For example, if I filter and sort, I only want what is left visible to appear in my new worksheet, and I want it to do it automatically without my having to copy and paste. Automatic means VBA code. See Ron de Bruin's site for copying filtered results. http://www.rondebruin.nl/copy5.htm Gord Dibben MS Excel MVP On Tue, 4 May 2010 11:32:01 -0700, jpstormy <jpstormy@discussions.microsoft.com> wrote: >Can I copy a worksheet so that the new workshee...