IF function - limit to the number of IFs?

Is there a limit to the number of IF statements you can have in one function?

i.e. =IF(a1<a10,a10,(IF(a1<a11,a11,(IF(a1<a12,12,(IF(.................. and 
so on.

I seem to get to 8 IFs and then get an error.
0
jonrtait (4)
10/20/2005 9:41:09 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
614 Views

Similar Articles

[PageSpeed] 15

That's it..........take a look at the VLOOKUP function.........

Vaya con Dios,
Chuck, CABGx3


"jonrtait" <jonrtait@discussions.microsoft.com> wrote in message
news:1809AD7F-DF73-4B49-AEEC-E06D791CBE2A@microsoft.com...
> Is there a limit to the number of IF statements you can have in one
function?
>
> i.e. =IF(a1<a10,a10,(IF(a1<a11,a11,(IF(a1<a12,12,(IF(..................
and
> so on.
>
> I seem to get to 8 IFs and then get an error.


0
croberts (1377)
10/20/2005 9:54:48 PM
Thought so.... 

Only problem with the LOOKUP functions is that if an exact match is not 
found, the next largest value that is less than lookup_value is returned.... 
but I need the next largest value that is GREATER than the lookup_value....

Any thoughts?

"CLR" wrote:

> That's it..........take a look at the VLOOKUP function.........
> 
> Vaya con Dios,
> Chuck, CABGx3
> 
> 
> "jonrtait" <jonrtait@discussions.microsoft.com> wrote in message
> news:1809AD7F-DF73-4B49-AEEC-E06D791CBE2A@microsoft.com...
> > Is there a limit to the number of IF statements you can have in one
> function?
> >
> > i.e. =IF(a1<a10,a10,(IF(a1<a11,a11,(IF(a1<a12,12,(IF(..................
> and
> > so on.
> >
> > I seem to get to 8 IFs and then get an error.
> 
> 
> 
0
jonrtait (4)
10/20/2005 10:27:02 PM
If your data increments are equally spaced, like 100, 200, 300,
etc.........maybe something like.....

=IF(ISNA((YourVlookupFormula,FALSE), (YourVLookupFormula+OneIncrement,True),
(YourVlookupFormula,FALSE))

Vaya con Dios,
Chuck, CABGx3


"jonrtait" <jonrtait@discussions.microsoft.com> wrote in message
news:69A79394-4853-4F58-9124-CB36A12A70C5@microsoft.com...
> Thought so....
>
> Only problem with the LOOKUP functions is that if an exact match is not
> found, the next largest value that is less than lookup_value is
returned....
> but I need the next largest value that is GREATER than the
lookup_value....
>
> Any thoughts?
>
> "CLR" wrote:
>
> > That's it..........take a look at the VLOOKUP function.........
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "jonrtait" <jonrtait@discussions.microsoft.com> wrote in message
> > news:1809AD7F-DF73-4B49-AEEC-E06D791CBE2A@microsoft.com...
> > > Is there a limit to the number of IF statements you can have in one
> > function?
> > >
> > > i.e.
=IF(a1<a10,a10,(IF(a1<a11,a11,(IF(a1<a12,12,(IF(..................
> > and
> > > so on.
> > >
> > > I seem to get to 8 IFs and then get an error.
> >
> >
> >


0
croberts (1377)
10/20/2005 10:45:52 PM
Reply:

Similar Artilces:

Message numbers?
In Exchange 2003 is there a way to track simply how many email messages have been sent and recieved on a month by month status? All that is really needed is the numbers but not where the went or who sent. Of course if there are more granular ways to do this, please include. Thanks! No easy way although you could turn on journaling and do it manually, write a transport event sink to start and increment a count based on date. Nue "Transam388" <Transam388@discussions.microsoft.com> wrote in message news:FDDA6E49-D467-4C41-B696-FD4E0F2BCAA6@microsoft.com... > In Exchan...

Convert time stored as decimalised number to time format
Hello How can I convert 3.5 hours to 3:30:00? -- Emma Emma A1/24 and formate as time Mike Rogers "Emma" wrote: > Hello > How can I convert 3.5 hours to 3:30:00? > -- > Emma ...

how i can make the numbers
hi i am fatih fistly sorry my english is not good my trouble is haw can i will write the number of the contribution for example 1.000.000.000 =yaziyla(b42) (one milyar) and tahan my office version is turkish version thank you so your company will interesting my problem Fatih, If I understand you correctly, you want a formula to translate numbers to words (in Turkish). Laurent Longre has a language version of an function which offers this capability. It is at http://longre.free.fr/english/ I don't know if it supports Turkish, but if not, send me the Turkish words for all...

Limited User Account
I am thinking of buying Microsoft Money Plus 2008 to use on a computer with Windows XP. I read in the user guide that I must install it with an administrator account, which I would expect. After it is installed, can I import data from Quicken 2006 and use MS Money Plus from a limited user account? The reason I ask is I do all my routine work with a limited user account and only use the administrator account when absolutely necessary. Gerry In microsoft.public.money, Geber wrote: >I am thinking of buying Microsoft Money Plus 2008 to use on a computer >with Windows XP. I read in the...

What's the function of folder subscription in MS Outlook 2003
Hi, I would like my MS Outlook to show all imap folders in my account, but to run routine updating only on my inbox folder. I would like each of my folders to be updated only when I choose a folder. I checked my folder list and identified a distinction between "all" folders and "subscribed folders". For some reason, all my folders were already subscribed. I've unsubscribed from my folders, but the complete list keeps showing up. Is there anything wrong with my folder list routine, or is my logic erronous? Please suggest a solution? Ran ...

Outlook 2003 contact limit
Hi I have a excel spred sheet with 1300 contacts in it, i have some problems importing them into Outlook 2003 a lot of the contacts are not imported Is there a limit on how many contacts you can have? or am i doing something wrong Here is what i have done I have exported my contacts to a spread sheet and then added a lot of contacts and then tryed to import it again without success You are far below what the item count limit is. Outlook 97-2002 Personal folders 65,000 items per folder (max file size for pst is 2GB) Personal Folders (unicode) personal folders Unknown limit (artificial lim...

adding a zero in front of number
how do you add a zero in front of other numbers, I am using item numbers and most start with zero, just shows whole numbers when I enter. example 095421 when I enter shows 95421. help. Hi When the number must remain numeric data, then format the cell as Custom "00000" (the number of 0's determines to which length is the entry padded). When you want the number to be converted to string, then use the formula (in my example the original number resides in cell A1) =TEXT(A1,"00000") (again, the number of 0's in format string determines the length of padding) Arvi ...

MATCH function; comparing letter grades in columns
I am currently comparing the contents on two cells in columns H & I (for example, cells H10 and I10). The contents are letter grades (i.e., A, A-, B+, B, etc., through E). I have my function tell me if there is any difference between the two columns and report the result (i.e., =93no change,=94 rating decrease,=94 or =93rating increase=94) in th= e adjoining column, J10. I use the MATCH function, and since it can=92t distinguish between the letter grades, I have placed the letter grades in cells M2 through M13. My function is as follows: =3DIF(MATCH(H10,M$2:M$13,0)=3DMATCH(I10,M$2:M$1...

Item number/location code does not exist in inventory error
I'm using eConnect to do an SOP transaction and it has been working for months but with a new customer and a new site I'm getting an error. The error is: "Item number/location code does not exist in inventory". The same transactions are still working with other customers on other sites so it seems like it must be a set up issue in GP. The customer and site look like they're configured the same as the ones that work so I'm stumped. Does anyone know what the issue might be? Thanks, Matt Have you confirmed that the item number has been assigned to the new sit...

Conditional Format
Hi, I want to assign a number to a certain conditional format. When the condition is true it should put a 1 in the cell and when the condition is false a zero or nothing should be assigned. Anyone? I seem to make the wrong code all the time. Thanks, Johanna Use the same test that the CF uses, and return 1 for TRUE and "" for FALSE. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jootje" <Jootje@discussions.microsoft.com> wrote in message news:CB0B3201-2DD9-466A-94FF-108691C77FAF@microsoft.com... > Hi, > >...

Can I use solver functions in a protected (locked) worksheet?
I am using the solver function to optimize a blend of products. I want to lock (protect) some areas of the worksheet, but when doing so the solver function will not work and the following translated message arrives: The active document is not a worksheet, is protected or shared. Hope someone can help me. Thank you. Niels ...

get cell of function
Hi Can I somehow determine, in which column (A,B,C,...) a selfmade function is called? Or: Can I tell the function, placed f.e. in cell C1, to loop through the column left (in this example B) of it? thanks lot for help materphilch Dim rng as Range set rng = Application.Caller to see the value: MsgBox(rng.address) Gary's Student "masterphilch" wrote: > Hi > > Can I somehow determine, in which column (A,B,C,...) a selfmade function > is called? > Or: Can I tell the function, placed f.e. in cell C1, to loop through the > column left (in this example ...

Custom Formatting numbers question
I have a spreadsheet with lot of millions numbers floating around. When I make graphs out of it, there's too much zeros. I don't want to divide all the numbers per 1 million. Is there a way to custom format those numbers with a mask that would take care of that problem for me ? i.e. 7 456 890.98$ would be represented with 7,457 M$ tia Double-click the numbers on your chart. Hit the Scale tab. From the Display units drop-down, choose Millions. ******************* ~Anne Troy www.OfficeArticles.com "Junkyard Engineer" <jevandenbroucke@hotmail.com> wrote in messag...

One of the System Attendant's task is blocked. Function: CMonitoringTask::Work
Hi All, Any help on this is very much appreciated!! Right, here goes! I have two exchange servers which are both running on Win 2k3 SP1. Exchange is 2003 SP1. One of my exchange servers comes to a halt and the only way to get it back up and running is by hard resetting the server! When this has been rebooted the server works fine and reports no errors in the logs after the reboot. However, I am getting the following event logs showing errors prior to reboot: Event Type: Error Event Source: MSExchangeSA Event Category: General Event ID: 1031 Date: 16/10/2006 Time: 1:35:46 AM User: N/A Com...

Variable Functions
I have a spreadsheet that contains a column of numbers. Sometimes I want to know the max of the numbers in the column, sometimes the min, sometimes the average, etc. Is there a way for me to type the word MAX or MIN or AVERAGE or . . . into a cell and have the appropriate value appear right below the word I typed? That would mean that the function would have to be set by the words I type in the cell above where the function is located. Any input would be appreciated. Thanks. Why not just assign 6 cells to provide this info for you. Enter MAX, MIN, and AVG in 3 cells, and under ea...

Exponential number format
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm trying to write exponential formulas in excel 2008, but it doesn't recognize the symbol ^ that we use on excel for windows. <br> example: <br> Excel Windows : = 2^2 -> result: 4 <br><br>Excel Mac : = 2^2 -> result: error <br><br>When we import some sheet from windows to mac with this formula works, but when I try to create on mac not recognize. <br><br>My question is: There is any other way to use this Symbol, for this kind of formula? The sam...

Control the number of records shown in a report/subreport.
I've posted this question before and when I follow the advice, I keep getting a circular reference error. So I'm hoping that if I post some more info, I may get insight as to what is going wrong. I have a report (BartS1report) it uses a query (BartS1Report). This report as a subreport (BartS1Sub2) that uses the query (BartS1). The report lists the customer name, address, and systems serviced. The subreport lists the systems serviced. The reports are connected through the Master/Child links Customer ID and the ServiceAddress. When there are more than 20 systems per ...

Help requested in formulating Functions and script to create a mat
Visual Studio.net 2003, SQL Server Report Designer, SQL Query Analyser. I have a table of milestones with data such as project, miletone name, baseline date, planned date and actual date. My task is to produce a chart that has months in the columns and for each month a set of blocks of data, made by concatenating fileds form the table, for milestones due in that month. I can extract the 'blocks' of data easily enough but when I put them in columns each successive month starts on a new line below those of the previous month. I need it so that the first milestone of each ...

Maximum MBOX file size limits and attachments
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: Exchange I am paring down a huge identity (16GB) by creating saved searches (6-month groups) of an offline copy of an exchange identity and pulling them to the desktop as MBOX files. One has 2314 items, another 14,521 and yet another has 18,845. Curiously they are all about 2GB in file size. Is this a limit? What happens when I exceed it? Are attachments stripped to preserve the messages? Thanks! On 3/29/10 2:35 PM, theocmg@officeformac.com wrote: > I am paring down a huge identity (16GB) by...

Limit selected recipients to receive mail from only known addresses??
I am looking to block all mail messages from Internet senders except for those from a list of known good ones (local are allowed to communicate) on a per-user basis. Is this possible with Ex2k3 Thanks in advance Mike Mike B <anonymous@discussions.microsoft.com> wrote: >I am looking to block all mail messages from Internet senders except for those from a list of known good ones (local are allowed to communicate) on a per-user basis. >Is this possible with Ex2k3? Not exactly in the way you describe. You can use the Active Directory as the source of valid SMTP addressses, and yo...

How can I use the vlookup function to return a sum of the values?
Hi, I am trying to use the vlookup function to return several values as 1 result into 1 single cell. For example, i have a spreadsheet which lists several different dpeartments. These departments may be repaeted many times in the spreadsheet. I need a sum of thoses values to be returned in a separate cell using the vlookup function. Can someone help me on this please? Thanks Chaandni Wrote: > Hi, > > I am trying to use the vlookup function to return several values as 1 > result > into 1 single cell. For example, i have a spreadsheet which lists > several > differ...

adding number in next cell
Every month I have to change the sales amount in my worksheet. I would like this amount to be added automatically to the total for the year sales amount in the next cell. Is this possible? Of course it's possible! If you would like a suggested formula: =SUM($A$1:A1) Now, if this doesn't work for you, how would I know that, with all the information that you gave us? -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! -----------------------------...

Possible to convert a + number to a
Is it possible to take a specific column and specific row, in an access database (Access 2003), and convert that positive number into a negative number? Please let me know if you have any information. Also, if you need more information to answer this question, please don't hesitate to ask. -- MC Yes, find the row based on its primary key and then multiply the number by -1 CurrentProject.Connection.Execute "UPDATE myTable SET myField = myField * -1 WHERE myKey = " & lngKey -Dorian "MMC" wrote: > Is it possible to take a specific column and specific r...

sort number with fractions
How can i sort this 1/1 1/2 10 191/2 2 201 202/5 280 3 3/2 to this 1/1 1/2 2 3 3/2 10 191/2 201 202/5 280? Please help! If you have these values in column T from T1 down then enter formula =VALUE(LEFT(T1,IF(ISERROR(SEARCH("/",T1)),LEN(T1),SEARCH("/",T1)-1))) in U1, select range T1:U10 and sort selection by column U (choose no header)! Regards, Stefi „surveyer” ezt írta: > How can i sort this 1/1 1/2 10 191/2 2 201 202/5 280 3 3/2 to this 1/1 1/2 2 > 3 3/2 10 191/2 201 202/5 280? Please help! ...

How to add a column to an existing table and fill it (i.e., fill down) with a particular word / number
Hello - I have a basic question. How do I add a new column to an existing table, and place a word in it so that it repeats all the way down the column? Thanks. Hi Mike What is the point of doing this. If you have a million records you will have a million repetitions of the same text string. If you "really" don't want to add the word as and when needed (very simple in a query report or form) you could use an update querry (if the word may change) or just add a default to the table field which will allow overwritting But again there seems to be no point in this -- Wayn...