If function, need nested more than seven levels

I have many Excel tables with columns with Dates in them in mmm-yyyy format.

I'd like to insert a new column to the right of each of these columns and 
enter a formula in them to convert these formats. In the new column I'd like 
the result to be Q1, Q2, Q3, or Q4.

I tried "=IF(MONTH(C3)=9,"Q3","")", and it works for the new column, if it 
is September, it results with Q3.

But since there are 12 months in the year, I'd need to nest 12 of these IF 
functions to cover all my bases, but Excel only lets me nest 7 deep.

How can I come up with an alternate way of doing this so it will evaluate 
all 12 months in one formula, and each cell in the column result in either 
Q1 to Q4.

Thanks,

Harold 


0
hcgood (53)
9/13/2004 6:49:20 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
476 Views

Similar Articles

[PageSpeed] 39

Hi Harold

There's a numeric logic here (higher month; higher Q) that you can take
advantage of. Try
="Q"&INT((MONTH(C3)+2)/3)

HTH. Best wishes Harald

"HGood" <hcgood@hotmail.com> skrev i melding
news:ewwYjJcmEHA.2504@TK2MSFTNGP14.phx.gbl...
> I have many Excel tables with columns with Dates in them in mmm-yyyy
format.
>
> I'd like to insert a new column to the right of each of these columns and
> enter a formula in them to convert these formats. In the new column I'd
like
> the result to be Q1, Q2, Q3, or Q4.
>
> I tried "=IF(MONTH(C3)=9,"Q3","")", and it works for the new column, if it
> is September, it results with Q3.
>
> But since there are 12 months in the year, I'd need to nest 12 of these IF
> functions to cover all my bases, but Excel only lets me nest 7 deep.
>
> How can I come up with an alternate way of doing this so it will evaluate
> all 12 months in one formula, and each cell in the column result in either
> Q1 to Q4.
>
> Thanks,
>
> Harold
>
>


0
innocent (844)
9/13/2004 6:57:26 PM
Fantastic, this did the job, thanks so much, never thought of using INT for 
this.

Harold


"Harald Staff" <innocent@enron.invalid> wrote in message 
news:O%23kQEOcmEHA.2684@TK2MSFTNGP10.phx.gbl...
> Hi Harold
>
> There's a numeric logic here (higher month; higher Q) that you can take
> advantage of. Try
> ="Q"&INT((MONTH(C3)+2)/3)
>
> HTH. Best wishes Harald
>
> "HGood" <hcgood@hotmail.com> skrev i melding
> news:ewwYjJcmEHA.2504@TK2MSFTNGP14.phx.gbl...
>> I have many Excel tables with columns with Dates in them in mmm-yyyy
> format.
>>
>> I'd like to insert a new column to the right of each of these columns and
>> enter a formula in them to convert these formats. In the new column I'd
> like
>> the result to be Q1, Q2, Q3, or Q4.
>>
>> I tried "=IF(MONTH(C3)=9,"Q3","")", and it works for the new column, if 
>> it
>> is September, it results with Q3.
>>
>> But since there are 12 months in the year, I'd need to nest 12 of these 
>> IF
>> functions to cover all my bases, but Excel only lets me nest 7 deep.
>>
>> How can I come up with an alternate way of doing this so it will evaluate
>> all 12 months in one formula, and each cell in the column result in 
>> either
>> Q1 to Q4.
>>
>> Thanks,
>>
>> Harold
>>
>>
>
> 


0
hcgood (53)
9/13/2004 8:04:41 PM
Hi Harold,

A less elegant solution than Harald's, but

=IF(MONTH(C2)<=3,"Q1",(IF(MONTH(C2)<=6,"Q2",(IF(MONTH(C2)<=9,"Q3",(IF(MONTH(C2)<=12,"Q4","")))))))

also works if you want to keep your IF statements - if the month is greater 
than 3 it will automatically move to the next if statement and will keep 
going until it finds the correct quarter.

Fiona
"Harald Staff" <innocent@enron.invalid> wrote in message 
news:O%23kQEOcmEHA.2684@TK2MSFTNGP10.phx.gbl...
> Hi Harold
>
> There's a numeric logic here (higher month; higher Q) that you can take
> advantage of. Try
> ="Q"&INT((MONTH(C3)+2)/3)
>
> HTH. Best wishes Harald
>
> "HGood" <hcgood@hotmail.com> skrev i melding
> news:ewwYjJcmEHA.2504@TK2MSFTNGP14.phx.gbl...
>> I have many Excel tables with columns with Dates in them in mmm-yyyy
> format.
>>
>> I'd like to insert a new column to the right of each of these columns and
>> enter a formula in them to convert these formats. In the new column I'd
> like
>> the result to be Q1, Q2, Q3, or Q4.
>>
>> I tried "=IF(MONTH(C3)=9,"Q3","")", and it works for the new column, if 
>> it
>> is September, it results with Q3.
>>
>> But since there are 12 months in the year, I'd need to nest 12 of these 
>> IF
>> functions to cover all my bases, but Excel only lets me nest 7 deep.
>>
>> How can I come up with an alternate way of doing this so it will evaluate
>> all 12 months in one formula, and each cell in the column result in 
>> either
>> Q1 to Q4.
>>
>> Thanks,
>>
>> Harold
>>
>>
>
> 


0
9/13/2004 8:08:23 PM
Reply:

Similar Artilces:

EVALUATE Function
I have looked on the internet but can't seem to find much information on Excel's EVALUATE Function. I see it being used but don't seem to have it in my function list with Excel 2003. Can someone provide an explanation of the EVALUATE function i.e. what it does, a little history, etc. Thanks for your help. Hi Frank, Evaluate is not a function, hence u can't see it in the function list. It is a formula auditing tool, located in Tools > Formula Auditing > Evaluate Formula (EF) . Basicaly if you enter a formula in a cell and use the EF it will show you exactly h...

adding cross-functional flowchart changes page-layout
i would like to add to a "landscape" page a stencil>flowchart>cross-functional flowchart shapes vertical. when adding it the page converse automatically to a "portrait" page, while it should remain "landscape" the same happens if I add cross-functional flowchart shapes horizontal to a "portrait" page. it changes into a "landscape" page. can I avoid this? thx, kjetil I can't think of a way to avoid having the page orientation switch to portrait when you drop the functional band shape out and pick vertical. Visio automatically cha...

Need help bring over GP 7.0 data to new a GP 8.0 server.
I'm setting up a GP 8.0 Test Server with SQL 2000 SP3a installed. How do I bring my Great Plains 7.0 Data into the GP 8.0 envirnoment? Thanks in advance, The easiest way is the following: On the 7.0 server, detach DYNAMICS. Copy the DYNAMICS mdf and ldf file from the 7.0 to the 8.0 server. On the 8.0 server, attach and browse to the mdf that you copied. On the 7.0 server, attach DYNAMICS. Repeat for all databases. If you also need the sql logins from the 7.0 server, there is a TK on how to do this (uses a table called RecoveryMaster within Dynamics db). If you can go without the s...

Microsoft Word crop function
When using the Picture toolbar and the crop function in Word (Word 2002 SP3), what is the most practical way to decrease the coarseness of the cropping of an image? Previously, I was using the freeware 20/20 image editing program to rubberband an image with the mouse and could easily paste it into a Word or PowerPoint file using Windows XP Professional 32-bit at my workplace. We've gone to XP 64-bit and the 20/20 image editing program doesn't work. Does someone know of a licensed image editor that is compatible with XP 64-bit if no other method is practical through cro...

SMALL Function question
Hi All, First off, many thanks that assisted me in getting my golf score sheet running properly with the MATCH & INDEX stuff...it works great. I have a problem with the SMALL function...I have a list of 5 lowest scores returned from a range called 'calcutta'. In the adjacent cell I have the coresponding name returned ato match the score with the player. The problem is with ties. If two players hve the same score the SMALL function returns the first one it finds in each cell. I use it like this G1=SMALL(CALCUTTA,1) G2=SMALL(CALCUTTA,2) ETC.... "Monte Comeau" <...

EARN $ 1,434,892.8 BY E-GOLD (GUARANTEED!!) All you need is 3 People You know #3
EARN $ 1,434,892.8 BY E-GOLD (GUARANTEED!!) All you need is 3 People You know Hello every body , im be glad to introduce one of the best MLM for every body who wants to explod his/her earning with very simple steps and incredible situation! JUST IMAGINE...... If you achieve only 10% success you will still make $ 143,489.28 If you achieve only 1% success you will still make $ 14,348.928 But If we trust to each other and be serious, reaching to $ 1,434,892.8 is not seem so far,just three or four weeks!! The whole works that you should do : 1) You should open an e-gold account ,th...

Do I need these?
I was looking through my Add/Remove Programs list and saw the following. I have a Dell Latitude D600 with XP SP3. I use VB 6.0 (Classic). The MSXML KBs stacked up? The .NET updates? Do I really need these? -- Windows Live OneCare Scanner -- MSXML 4.0 SP2 KBs 936181, 954430, 973688 w/ MSXML 6 SP2 KB 973686 -- VIsual C++ 2005 & 2008 Redistributables -- MS .NET Framework 1.1, 2.0 SP2, 3.0 SP2, 3.5 SP1 -- Memory Key Boot Utility Ed Ed from AZ wrote: > I was looking through my Add/Remove Programs list and saw the > following. I have a Dell Latitude D600 with XP SP...

Exchange Information Store -- Stops
Hi, Recently at one of the Exchange server, emails stops working. When I recycle "MSExchangeIS --- Microsoft Exchange Information Store", it doesn't stop gracefully, so have to kill "Store.exe". This just happening since last week. No sure when it happens, as System logs doesn't have any errors. Any help, direction would be applicated. Thanks One of the warning messages I get is below Log Name: System Source: Microsoft-Windows-WAS Date: 6/4/2010 4:35:52 AM Event ID: 5138 Task Category: None Level: Warnin...

Problem with data using IF and Nested IF statements possibly???
Afternoon All I am attempting to analyse data from multiple worksheets from numerous people the incoming data all has one thing in common column A this is a certain frequency a job is done. The problem is that there are many ways of entering the data ie 12 months or 52 weeks or 365 days all essentially meaning the same thing. My idea is to collate the data in col A and then using the Helper column as the standard frequencies ie if cell A1 = 12 months, closes frequency in helper is 52 weeks therefore value in C1 = 52 weeks. By using an IF statement I can change the value of one frequen...

How do I delete aliases at the store level?
We have hundreds of items in our database which we no longer use but would like to keep for sales history purposes. These items still have aliases associated with them. As we receive new items we input the SKUs as aliases in Headquarters Manager. Although RMS should not allow the same alias to be entered for more than one item, we have multiple new items that appear as an old item which has the same SKU as the new item when scanned in POS. In an effort to eliminate this problem, we went through and manually deleted all the aliases from the old items we no longer wish to use in Headqu...

help needed with repeated copy/paste special
[image: http://home.cogeco.ca/~rbhayana/backload.gif] [image: http://home.cogeco.ca/~rbhayana/mastertable.gif] I ran into a lot of work and am wondering if anyone can help me out. Help would be greatly appreciated because going thru 19272 records for copying and pasting is driving me insane. :mad: These are the 2 files I'm dealing with (see pictures above). Top one is backload file and the one underneath is mastertable. As you can see, FundataKey is common to both files. The values of Fundatakey are not repeated in mastertable however are repeated in the backload file. What...

Countifs function equivalent for Excel 2002 (XP)
I need the functionality of COUNTIFS but for Excel 2002. I don't want to sum but rather count. Below is the formula I would use if I had the COUNTIFS function. =COUNTIFS(A3:A7764,"PGA",J3:J7764,"x") Any suggestions? =sumproduct(--(a3:a7764="pga"),--(j3:j7764="x")) Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail here: http://www.x...

Need a ZERO as the first character in a cell
How do you make the first number in a string of numbers a zero and keep it there? Depends on if you want a *true* number, or a text number. For Text, precede the entry with an apostrophe ( ' ), OR, pre-format the cell as text. For true numbers, custom format the cell with the number of digits you're using: 00000 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Mr_Jim" <MrJim@discussions.microsoft.com> wrote in message news...

need some help with: formatting of x2 dates in 1 cell
Hi and thanks to anyone who reads this. I have a worksheet which contains two columns of dates. In a second worksheet i have a column which adds the two dates together as TEXT and ommits dates which are blank which works perfectly, however: I would like to know how i could format each of the 2 dates in the 1 cell to have different font colors? Here is my existing cell formula: =IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" "&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy"))) I have a feeling its not...

Help: Need to delete duplicates
I have an access table that has over 470,000 rows. I need to delete the duplicates. None of the "append" query ideas will work as it involves copying and pasing the structure, and Access won't allow me to copy 470,000 rows. Does anyone have a suggestion on how to accomplish this? What error do you get when you run the append query? It could be that the file size is nearing the 2 GB limit. First make a backup copy of the database then run a compact and repair. Then try the append query ideas. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bont...

Two Level Search
I have simplified the example ----- I would like to search the table shown below and determine the amount Pledge by each family. However since there are last names that are the same, a simple vlookup does not work. For example, I must define my search so I get Bill Johnson, not Frank Johnson. For the example, I would like the search result to appear in the column labeled "Search Result". Fund Raiser Invitations and Followup Roberts 22-May Y/N Invited Attended Pledged (amount) Search Result Smith Bill Marla N 1000 Smith Jon S...

Sumif function and negative value together
Hi, Is there a way to combine these two functions together? =SUMIF(AP:AP,B4,AS:AS) =IF(F3<0,J3*F3,J3) What I am trying to do is to pick up cost from a different table b using sumif function. But sometimes, there is a return - I want th sumif function to pick up cost and if it is a negative quantity, I wan it to reverse the sign. Thanks for your help. Regards, BG -- Message posted from http://www.ExcelForum.com =ABS(SUMIF(AP:AP,B4,AS:AS)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) &q...

need a finacial planner for home use.
i just need a simple planner to help budget my finaces. Browse through the Excel Templates found at the MS Template Gallery. http://office.microsoft.com/en-us/templates/default.aspx Gord Dibben Excel MVP On Mon, 16 May 2005 18:33:33 -0700, "howrangi" <howrangi@discussions.microsoft.com> wrote: >i just need a simple planner to help budget my finaces. Hi If you need a simple planner I use Financial Fire Drill - You can find it on the Web. It's very simple and it's off line. There's a 30 day trial - Hope it helps "howrangi" wrote: > i jus...

Need Help
Hi, We are using a Great Plains Dynamics 5.0 system. The client machine was recently attacked with viruses and I am trying to set up a new machine as a client. The error I am currently getting is "Files for this company have not been updated." when I try to log into one of the companies in Dynamics. I accidentally installed (halfway) the 5.5 client rather than the 5.0 client. Is it possible this updated some files on the server that I can roll back from a backup tape? Somethings I have read mention something about performing an update from within Dynamics Utilities, howe...

Need help modifying XML file...
I have this xml file... <?xml version="1.0" encoding="utf-8" standalone="yes"?> <images> <pic> <image>http://www.somesite.com/mypic.jpg</image> <caption>Picture 1 is here</caption> </pic> <pic> <image>http://www.somesite.com/mypic2.jpg</image> <caption>Picture 2 is here</caption> </pic> </images> Using VB.NET how could I add, edit and remove a <pic> item, consisting of an <image> and <caption>? Any sample c...

Need Help with Illegal Operation Error
Hi - I am hoping someone can help me. I have a file that I set up that links to other files. I created i and I can open it fine but the problem is when anyone else tries t open it they get an error similar to illegal operation and their exce closes. Any ideas would be greatly appreciated. Please email me at - Dreams1106@hotmail.co -- Message posted from http://www.ExcelForum.com ...

QS Hook Function Question
Based on the surfpro.dll example, I have roughed out a prospective function that I hope will allow me to initiate a membership style control over POS transactions. I'm not 100% sure how to call a QSBridge FireEvent -- ClearCustomer as the POS requires a Customer to complete a sale, so I figure no customer, or take away the customer, no sale... right? In VB6 this morning, the Date function, literally returned 6/28/2005. Great! If the Customer Expiration Date is stored in CustomDate1, then a quick subtraction and a few conditionals should do it. The subtraction returned an integer calc of t...

If to sum function.
I created a cell so that everytime I enter a text from some other cells, it will add 1 to the value of the cell, otherwise, it won't add anything. If I enter no text or a number in the other cells, this cell will display 0. The formulas for the cell is like this: =SUM(IF(ISTEXT(D13:S13),1,0)) After entering text to three other cells, this cell will display a value of 3. However, I want that valued to be 1 less than. I cannot do this. If I put minus 1 with an if statement, this cell will display a value of -1 when no text is entered in other cells. Any folks have any idea how ...

Nested IF statements ...
What is the maximum number of nested IFs Excel can handle? Is the limiting factors the number of characters a cell can accommodate or is it the number of nested IFs? Seven functions may be nested - it's a limitation of the nesting, not the characters. In my experience, whenever nesting gets deeper than about 4, perhaps 5 levels, there's an easier way to solve the problem using mathematical relationships or lookup tables. Deep nesting is the hardest type of formula to maintain, especially if it's not just for your use. In article <42f701c37c61$a50dc1c0$a501280a@phx.gb...

Need help on this formula!
Hello, I need help with a formula I'm trying to create. I have data in column but the data is scattered. For example, there will be a numbe in row 20 then it's blank until row 35, then blank until row 68, and s on. I need a formula that will copy the data to the rows that are blan below it. For example, if row 20 has a number in it I want to cop that number in the blank rows until it reaches the next number. An once it reaches row 35 it will copy that number to the rows below i and so on. I would do this manually but the spreadsheet is huge. Thank you for your help, Olimits...