+ Reply to Thread
Results 1 to 6 of 6

Working around Excel 97 limit of 7 nested IFs.

  1. #1
    Registered User
    Join Date
    05-29-2007
    Location
    Colorado
    Posts
    3

    Working around Excel 97 limit of 7 nested IFs.

    This may be a super easy question to answer or an impossibility to do. My lack of experience prevents me from judging. But my need is this:
    I wish to test a cell that has a DATE() function and to then return to a different cell a value found in any one of 12 cells arranged in monthly order.
    I successfully used a nested IF string for about the first 6 months, until I found that there is a limit of 7 nested IFs.
    Ideally, I only need to determine the month associated with the DATE() field, but don't know how to do this.
    Does anyone have an idea about how I can accomplish my task? Maybe a table lookup? Or something even simpler?
    Help will be greatly appreciated.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    I'm sure whatever you're trying to do is possible. Could you post an example of your spreadsheet with sample data and highlight the info you're after?

    Just thinking about it, you should be able to use a VLOOKUP or HLOOKUP function to pull the correct value you're seeking (depending if your data is laid out horizontally or vertically).

    You can reference the month of a date value in a cell by simply calling the MONTH function, like so:
    =MONTH(A1)

    If A1 had 4/15/2007 in it, that formula would return the number 4. If you wanted to return (or use as part of a lookup) the name of the month instead of the number, you could modify that formula to something like:
    =TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)),"mmmm")

  3. #3
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,230
    Yes there is a way around the 7 nested if condition.

    However, it sounds like you don't need IF. CHOOSE might interest you.

    if A1 contains 5, then
    =CHOOSE(MONTH(A1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
    will return "May"

    if A1 contains the date 3-Aug-06
    =TEXT(A1,"mmm") returns "Aug" and
    =TEXT(A1,"mmmm") returns "August".
    Formatting A1 to a custom format, "mmm" or "mmmm", will display "Aug" or "August" in A1 (even though it contains the date not the string.)

    I hope this helps.

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881
    Please hold off the laughter while I smack myself on the forehead for my TEXT formula suggestion. I use TEXT formulas quite a bit, and for the life of me it just didn't click tonight.

  5. #5
    Registered User
    Join Date
    05-29-2007
    Location
    Colorado
    Posts
    3

    Thanks for the ideas. I learned a lot.

    The worksheet is to keep track of income information that can be examined to determine estimated taxes owed each quarter. I am attempting to avoid ugly surprises near the end of the year or at tax time, by not knowing my tax liability and tax amounts already paid or withheld.

    At the top is a Frozen Pane summary table of amounts inputted to TurboTax's estimator program from which was calculated the estimated taxes I will owe for the year. In addition there is a column of figures that shows the amounts of prior year refund applied to this years taxes, "Taxes Withheld", and estimate payments made. The total of these can be compared visually to the amount of total tax owed for the year, as calculated by the TurboTax estimator, and adjustments made, as necessary, throughout the year.

    Below the summary section are 12 tables, one for each month, with column headings "Cap Gain - ST", "Cap Gain - LT", "Dividends", "Taxes Withheld", etc.
    At the bottom of each monthly table is a cumulative total for each of the column headings. The cells containing the cumulative totals for each column are fixed. For example, the "Taxes Withheld" for January is cell G28, February is cell G41, March is cell G54, etc.

    Each month I want the cumulative total for "Taxes Withheld" as of that month to be automatically copied to cell J11 in the summary table.
    Cell A1 contains the function DATE(). By knowing the month value in A1, I hope to select the cell for that month in the table and copy it to cell J11.

    I will educate myself on the VLOOKUP function, and thank those who showed me that the DATE function works with a cell, not just a text date.

  6. #6
    Registered User
    Join Date
    05-29-2007
    Location
    Colorado
    Posts
    3

    Talking Thank you. Thank You very much.

    VLOOKUP is a winner. I created a table with 12 rows and 2 columns off to the side of my spreadsheet in the cells K18 through L29. In column 1 are the numbers 1 through 12, the months of the year. In column 2 are the cells representing the cumulative values I want to copy to the summary cell I14. The table looks like this:
    1 =G29
    2 =G42
    3 =G55
    4 =G68
    and so forth thru 12.

    I1 is the cell containing the function TODAY().

    The function in cell I14 looks like this:
    =VLOOKUP(MONTH(I1),K18:L29,2,FALSE

    It works perfectly and has given me ideas for tracking other amounts in the spreadsheet, even highlighting or coloring cells (if that can be done) that exceed values used in the TurboTax estimator.

    Thanks Paul and Mike for your help. Someday, I will learn how to attach a sample spreadsheet, to make it easier to visualize.

    Bill

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1