Multiple IF Functions #3

I'm new at this and am trying to put several different, unrelated IF formulas into a single cell but am having trouble with it. How do you differentiate between one formula and the next, but still have it in the same cell
Is there a limit to the number of IF formulas you can put in one cell? If so, how many?
0
5/12/2004 1:06:06 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
983 Views

Similar Articles

[PageSpeed] 42

Hi

There is a limit of 7 nested IFs in a cell.
A simple multiple IF scenario could be:
=IF(A1<5,"under 5",IF(A1>5,"over 5","5"))
This checks the value in A1 and gives the result in text. If the first IF
returns FALSE, it continues into the next IF etc.

-- 
Andy.


"Confused" <elderrparke@hotmail.com> wrote in message
news:D8A2C586-C4AB-46C7-A26A-ED4B850B44D1@microsoft.com...
> I'm new at this and am trying to put several different, unrelated IF
formulas into a single cell but am having trouble with it. How do you
differentiate between one formula and the next, but still have it in the
same cell?
> Is there a limit to the number of IF formulas you can put in one cell? If
so, how many?


0
andyb1 (494)
5/12/2004 1:13:19 PM
You're probably used to seeing If statements with the following structure or something simila
If condition the
execute if tru
els
execute if fals

The If statement in excel looks like the following
if(condition, execute if true, execute if false

If you want to add multiple unrelated if statements into a single cell then simply put them in one after another.  However, you must somehow link the two if statements together with concatenation or math operator. 
Example
=if(A1<5, 1, 0) & if(B1<5,1,0

if A1=2 and B1=3, then you would get "11
Here we concatenated 1 and 1 to get 11

If we change the formula to be..
=if(A1<5,1,0) + if(B1<5,1,0

if A1=2 and B1=3, then you would get "2
Here we added 1 and 1 to get 2. 

To add more than one unrelated if statement insert them one after another, but make sure to link them somehow by concatenation or a mathematical operation. 

Adding ifs this way there is no limit, but the formula length can only be a maximum 1,024 characters long.


0
anonymous (74722)
5/12/2004 5:51:09 PM
Reply:

Similar Artilces:

Mail Enabled Public Folder #3
Is there any way to prevent a public folder from being mail enabled by default? This is getting really annoying... Also, is there a way to Un-mail-enable a PF? We are running Exchange 2003 on Windows 2003 (SBS 2003 Premium) TIA, Carlos. "Carlos M. Perez" <cperez@nospamruncentralnospam.com> wrote: >Is there any way to prevent a public folder from being mail enabled by >default? This is getting really annoying... Is the Exchange organization running in "mixed" or "native" mode? A mixed-mode organization will mail-enable public folders by dfaul...

easier generation of multiple POs from SOP Orders
When using the "Purchase" button from SOP Transaction Entry window - If you want to do more than one PO for the SOP Transaction, you have to use the "Purchase" button, choose which items to include, then click on "Generate" and the Generate PO window is gone and your SOP Transaction Entry window is cleared. To do the next PO you have to again choose the SOP document and click on the "Purchase" button. It would be a huge improvement if instead of only "Generate", you had a "Generate and New" button that would allow you to choo...

>NET 3.5 on SBS 2003R2
I have a client who needs to install .NET 3.5 on an SBS 2003 R2 server because of a server application that requires it. I have run into a few problems in the past updating .NET on SBS boxes, and since then, I have always avoided it. Are there any issues with the updating to .NET 3.5 in my environment? From what I've read, I will need to set up a separate application pool for .NET 3.5. Any other caveats or pitfalls? Thanks. Depends on who you ask. <g> I have had no issues except for occasionaly (once per server) having to reset the shipping apps that rely on IIS to .ne...

Function help and example
Dear All My question is: (1) I want get real access 2003 function example. (2) I want pass argument to the function using text box or input box. (3) Can you please write this example for me: so I can understand the argument and the function return value Example: Public Myfunction (argument here) as data type - Pass Number to the function>>>Function give me(return) text - Pass Number to the function>>> Function give me (return) date - Pass text to the function >>> Function give me (return) Number - Pass Text to the function >>> Function give ...

XML Functionality in Excel Office 2003 Standard
Hi, I would like to be able to use the XML support that is available with Microsoft Office Excel 2003 , however I am having some problems. The install of Excel 2003 at my company seems to be part of the Office Standard package, which this document states doesn't have the required functionality: http://msdn2.microsoft.com/en-us/library/aa203739(office.11).aspx Using the Excel 2003 XML Tools Add-in Version 1.1 However many other articles make no mention of this dependency, which I think is unfair: http://msdn2.microsoft.com/en-us/library/aa192869(office.11).aspx http://office.micr...

External Trusts and Forest Functional Level
Our domain is at Windows Server 2003 functional level and our Forest is at Windows Server 2000 functional level. We also have 4 external trusts with domains in a variety of modes. DomainB is running in 2000 Mixed Mode domain level and 2000 forest mode. We'd like to move our forest functional level to 2003, but our concerned that this may effect / break the external trusts. Does anyone have any first hand experience? Hello MStefani, Raising the functional levels have influence on the DCs used in the domain, if you switch to Windows server 2003 no lower OS version DC can...

Plotting multiple times against one date succesively for multiple
IOW...I have an event that happens multiple irregular times each day. I would like to do a scatter graph that would show x=dates, y=time hh:mm:ss. So each data point on x has multiple y's. Is this possible? I've had no luck thus far and have tried all the usual suspects. Not exactly sure what trouble you're running into, but this is what I was able to do: With no group of cells selection, open the chart wizard, select XY (scatter) graph. On the next screen, click on the Series tab, Add a series. Give the series a name (if desired) then select your range of dates as t...

Multiple worksheets
I export my shop sales/stock reports to Excel format. The problem i that each report sheet is imported as a separate worksheet - so months sales report may have over 300 worksheets. I have to transfe the data from each individual worksheet on to one in order t filter/sort/subtotal the information. At the moment I am using Macro to cut/paste the information - this has speeded the process up but i is still very time consuming and I have to record different macros fo each new type of report. Can I summarize the information from all th worksheets with a Pivot Table? and if so HOW! - I have t...

Functions and their Charts
Hi. In Excel it would be great to have sample charts of common functions (e.g.: SIN, COS, LN, LOG, etc.). In A1 and B1 enter heading such as X and Sin(X) In columns A put a series of X values In B2 use =SIN(A2) (or =LN(A2)); copy down the column and chart Hold it! Did I tell you Excel's trig expects radians (the only REAL way to measure angles) So if you x-values are degrees we nee =SIN(RADIANS(A2) best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "Ana" <Ana@discussions.microsoft.com> wrote in message news:B621F0F1-1795-4824...

[newbie] multiple SDI to MDI
Hi, I'm an MFC beginer. I've written a small (non functionnal) app with multiple dialogs based on CDialog to see if I can build an interface this way. It works. Now, I wan to use a graphic library that uses MDI instead of SDI, so I would like to transform my CDialogs into something that can be automatically built, like a dialog, but not using MFC document/view. Is it possible ? Is it a bad idea ? I've looked at the MDI example in the samples, but it provides its own painting routine. FormView looks like what I need, but FormView is a View, and I don't want to use that. Adv...

Multiple bins per item
We have a clothing store. We have floor space for about 3000 garments, distributed over several different racks and fixtures. We also have two stockrooms. At any given time, item XYZ could be in multiple locations. We may have 10 items total, with 3 on the "Featured Item" rack, 2 on the regular rack, 1 in the front stockroom, and 4 in the secured stock cage in the back. RMS only has a single bin location record per Item record. Is there any way, using the out-of-the-boxs RMS software, that we can have multiple bins per item? We really aren't looking to buy a third party t...

Caching block from multiple domains
Hi! My application spans multiple domains on the server side. I need to cache some db records into Microsoft Caching block. Is there a way to make it accessible from all domains the application explicitly creating? I know there is a SetData() method on the AppDomain, but it requires serializable objects - something I can not guarantee. So, is there a way to access the Caching block from multiple domains? Many thanks in advance for your help. Sincerely, --M "Mike" <Mike@discussions.microsoft.com> wrote in message news:05BF5290-5718-488E-8857-A8570C1...

Embedding multiple Videos in one slide
I would like to have multiple links on one slide to be linked to one video field or multiple overlaid video fields. I like to have the player window as part of the slide not as an link which opens a separate video player. As alternative I could make the embedded video small as selections on the page. On selection the video window would have to resize to a bigger window and return to its original size in the presentation after the video is finished. I guess I don't understand what your question is. Insert | Movies and Sounds | Movie from File will let you put the video on...

Macros
I have created the following macro. Where I am calling in the "Details" Worksheet I would like to call in the same info for all of the Worksheets in the Workbook. How do I do that? Thanks in advance for any help!!!! Sub TMSTabulate() ' For each workbook in source directory ' open workbook ' '* TMSVersion = ThisWorkbook.CustomDocumentProperties("TMSVersion") FiscalYear = 2005 TabCell = 2 Ledger = "BUDGET" Period = 1 With Application.FileSearch .NewSearch .LookIn = ThisWorkbook.Worksheets("Settings").Range("BDIF...

Criteria to delete last 3 days of data
I have searched Access Monster and I have been unable to answer my question. I am trying to come up with a formula to put in my query that would remove records based on my field "Date Scanned". I need to remove the last 3 days, including today. One of my problems is the weekend. If it is Monday then I would need it to delete, Thursday, Friday, Saturday, Sunday and Monday. If it is Tuesday, I would need to delete, Friday, Saturday, Sunday, Monday and Tuesday, and then on Wednesday, I would delete, Monday, Tuesday, Wednesday, and so on. I found this in Access Monster: IIf(W...

Naming ranges on multiple worksheets
I have a workbook with multiple worksheets, the worksheets are structured identical to each other. I need to name certain ranges in each of the worksheets. On the first worksheet, I was able to name the ranges without difficulity, however when I attempt to name the ranges on the worksheet number 2, it throws me back to sheet 1. The range name that I entered on sheet 2 was not accepted, the name box is still showing the cell number. Can anyone assist? Using Excel 2003. Thanks, Jim One method On sheet1 Insert>Name>Define MyName Refers to: =INDIRECT(&quo...

Inserting rows in multiple sheets
Hey guys, Im trying to find out if Excel has a feature were if I have say 5 sheets in one workbook file and I inserted a row in one of the sheets that in the other 4 sheets a row would automatically be inserted in the same place? If you select all 5 worksheet tabs and insert a row in one, it wil insert it into all of them. Be careful to unselece them again befor continuing, since it will do the same thing if you enter data into on (enters into all). -- kkkni ----------------------------------------------------------------------- kkknie's Profile: http://www.excelforum.com/member.p...

RMS 1.3 #3
i read in discussion board that RMS 1.3 will only work with windows 2003 and XP. Is it just for the work stations or our server has to be also 2003 and higher also. ...

Migration with DMF (CRM 3.0) doesn't migrate any records
I've populated the cdf_Contacts (cdf_Contacts_ext and cdf_Contacts_info are also updated with the stored procedures). Every time the migration wizard finds all records but doesn't migratie any of them. In the log i see only (per every record): 6/23/2006 - 3:35:27 PM -- Recording migration status 6/23/2006 - 3:35:27 PM -- Creating object 6/23/2006 - 3:35:27 PM -- Failed to migrate object: 6/23/2006 - 3:35:27 PM -- <contact><statecode>0</statecode><owninguser>{B0D067DE-A5CF-DA11-BE91-00065B8DE0EC}</owninguser><emailaddress1>user@company.com</e...

Chart figures from multiple sheets
Is Excel 2002 capable of creating one chart based on figures from multiple sheets in one workbook? Yes it is. Create a chart from your Sheet1 data. Right click on th chart, Goto the series tab and Click Add. Choose the range from Sheet or 3 in the Values box. Regards, Lenin PS: It would be appreciated if you could please initiate only on thread for your query -- LeninVM ----------------------------------------------------------------------- LeninVMS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=668 View this thread: http://www.excelforum.com/showthread.php?...

And and Or Function
Can't figure out what is wrong with this? If (PaymentMethod = "Cheque" Or "Cash") Then the error state it is a mismatch. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200710/1 is it possible to put If (pyamentmethod = Cheque" Or "InterBank Giro") Then Syphonics wrote: >Can't figure out what is wrong with this? > >If (PaymentMethod = "Cheque" Or "Cash") Then > >the error state it is a mismatch. -- Message posted via AccessMonster.com http://www.acce...

HOW DO I MAKE A BANNER USING MULTIPLE PIECES OF PAPER
USING PUBLISHER 2003 HELP! Seems like this should not be hard to do, but I am stumped. Don't want to pay for one continuous piece of banner paper, but want to piece together paper to form a "fake" banner Using Paper size : 11 x 17 4 sheets of it to total 44" wide I need to type a long word like "CLEARANCE" on top line and "SALE" on bottom line 6" high font and want it to center on the 4 pieces of paper (filling it like a banner when pieces are taped together). So page 1 would have "CL" on top half of paper, lower half blank...

Deselect cells on multiple sheets after unhide
I have the following code I use to remove the autofilters and hidden rows and columns within a workbook. It works great. My problem is that after it runs, the columns that were unhidden remain selected. I have more code later in the process that won't run because there are cells selected. Is there an easy snippet of code so that I can select cell "A2" on every sheet in a workbook at once? I've tried a couple on my own but no luck. Thanks! -- Thanks, Sherry Oops...forgot the code. Sorry. Sub RemoveFiltersAndHiddenRows() Dim oWS As Worksheet For Each...

Many to Many relationship with CRM 3.0 entities
I saw in another post that you can do Many-To-Many relationships in CRM 3.0 by creating an additional entity with many-to-one relationships. Does anybody have an example of this? I have created a seperate entitiy with many-to-one relationships to both entitie's that I want to join. Now how do I get a form to display all the other entities records so I can pick from them and link one as i see fit. Garrett When you have created the correct relationships, then there are lookup fields available to be put onto the form. If you put both lookupfields onto your form, then you've creat...

Money Backup #3
Is there a way to add the d drive to the options for the bi-monthly floppy drive back-up? Currently I have to go to extra steps to backup to the cdrom. Would be alot more convenient if the cdrom was an option. Thanks for your help. If you are running XP then the CD drive that XP thinks of as a hard disk should appear in the drop down list. If using earlier versions of the OS then you need to do the dump to hard disk > burn the CD ROM shuffle! -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggest...