Ask Experts Questions for FREE Help !
Ask
    cricky075's Avatar
    cricky075 Posts: 2, Reputation: 1
    New Member
    		  
    #1

    Jul 18, 2012, 08:25 AM
    Levels of nesting in Excel Spreadsheet?
    Hello all,

    Right, here's my problem...
    First of all column C I have the dates of payment transactions, in a hidden column B I have the formula:
    =IF(C5="","",MONTH(C5))

    Using the numbered month, I want in column A to display the word of each month when the month starts. For example:
    =IF(AND(B5=2,B4=1),"February")

    The only problem is that I want a formula to display each month on the left as it starts, this leads me to the huge IF nest formula below:

    =IF(AND(B6=1,B5=12),"January",IF(AND(B6=2,B5=1),"F ebruary",IF(AND(B6=3,B5=2),"March",IF(AND(B6=4,B5= 3),"April",IF(AND(B6=5,B5=4),"May",IF(AND(B6=6,B5= 5),"June",IF(AND(B6=7,B5=6),"July",IF(AND(B6=8,B5= 7),"August",IF(AND(B6=9,B5=8),"September",IF(AND(B 6=10,B5=9),"October",IF(AND(B6=11,B5=10),"November ",IF(AND(B6=12,B5=11),"December","")

    I'm sure the answer to this is yes but is there any way to simplify this to make the Excel software happy?

    Thank you in advance.
    suzier's Avatar
    suzier Posts: 6, Reputation: 1
    New Member
    		  
    #2

    Jul 18, 2012, 09:34 PM
    My recommendation is not exactly what you have asked but obtains results in a less laborious way.

    1. Instead of using the "month" formula, copy all of Column C (C:C) into B. Label the column Month.
    2. Select Column B (B:B). Right Mouse Click and select "Format Cells".
    Select "Custom" in the Category List. The type mmmm in "Type"
    3. Select "Sub-Totals" from the data dropdown menu.
    Select "At each change in:" --> Month
    Use Function: ---> I recommend you select count or sum(if there is a column such as amount).
    Add Subtotal to (I recommend the date column if using count or numeric column for sum).
    Uncheck the "Summary below Data" checkmark

    Select OK

    The description will include the word "count" if you use the count function. You can do a "find" and replace on the word. Additionally, you may want to alter the "Grand Count" row at the beginning.
    cricky075's Avatar
    cricky075 Posts: 2, Reputation: 1
    New Member
    		  
    #3

    Jul 19, 2012, 01:27 AM
    Solved by other means. Thanks for your help.
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #4

    Jul 19, 2012, 10:53 AM
    You've forgotten that you can format a date to display any way you want. You don't need the hidden column for this. If there is a date in C2, then this formula in A2:

    =TEXT(C2, "MMMM")

    ... should give you the simplest results.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

Excel spreadsheet [ 7 Answers ]

Sir I have a set of data in excel sheet A as below: Sheet A Name of the students Marks in english Marks in maths A 45 74 B 38 58 C 52 65 D 45 65 E 88 72 F 36 45

Excel spreadsheet [ 2 Answers ]

I have large numerical data stored in excel sheet. I wanted to write simple formulas to compare the numerical values in the columns A to Z. For Example to compare the values in columns A,B,C,D of the sheet(values of A,B,C,D arranged chronologically). I wanted formulas in the newly inserted...

Levels of nesting in a formula [ 12 Answers ]

Dear All, I created an excel file with extension .xls and entered a forumla =if(... It had 11 levels and it successfully worked and I saved it. Next day when I have opened the file it gave the error #value. I entered the formula again but it refused to accept with the following message: "The...

Excel Spreadsheet [ 2 Answers ]

Does anyone know how I can get an Excel spreadsheet to open at start-up? Thanks!

View more questions Search