Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: alternative for nested IF function?

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi There,
    I want to know if there is a better alternative to the multiple IF construct? This is what i am trying to do - based on the value selected in a cell i want to autofill the other corresponding cells in the same row.

    Example - if A1 (representing month) = October, November or December then B1 should be autofilled with a value "Q4 - Last quarter of the year"

    Will appreciate help on this!

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Wivenhoe, England
    Posts
    912
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can do this with a lookup table. On an empty area of your worksheet or on another sheet, create a column of the twelve months. In the next column to the right type in the descriptions you want, e.g. Q1 - First quarter of the year, Q2 - Second quarter of the year etc. Thus:
    January Q1 - First quarter of the year
    FebruaryQ1 - First quarter of the year
    March Q1 - First quarter of the year
    April Q2 - Second quarter of the year
    May Q2 - Second quarter of the year
    June Q2 - Second quarter of the year
    July Q3 - Third quarter of the year
    August Q3 - Third quarter of the year
    SeptemberQ3 - Third quarter of the year
    October Q4 - Last quarter of the year
    NovemberQ4 - Last quarter of the year
    DecemberQ4 - Last quarter of the year

    Select the range containing all the 24 cells. Name it CalendarRange.
    In B1 type =VLOOKUP(A1,CalendarRange,2,0). Autofill this down if you want to repeat the lookup on other rows. If you change the month in A1, B1 will change automatically.

    This Message was edited by: inarbeth on 2002-05-05 01:34 ]

    [ This Message was edited by: inarbeth on 2002-05-05 01:37 ]

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Or in B1 put =IF(OR(A1="October",A1="November",A1="December"),"Q4 - Last quarter of year","")

  4. #4
    New Member
    Join Date
    May 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Awesome! both the suggestions work. thanks a ton for your help!

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 01:36, Schrödinger’s cat wrote:
    Or in B1 put =IF(OR(A1="October",A1="November",A1="December"),"Q4 - Last quarter of year","")
    a slight timesaver :

    =IF(OR(A1={"October","November","December"}),"Q4 - Last quarter of year","")


    :: Pharma Z - Family drugstore ::

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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