Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Avoiding ifs within ifs

  1. #1
    New Member
    Join Date
    Mar 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Avoiding ifs within ifs

    Hello Everybody again,

    I'm having difficulty figuring out how to set up an if statement without putting 12 ifs within each other.

    I'm trying to get a formula that recognizes what month it is and then displays either FYyy or yyyy based on the month it shows and the date.

    This is what I have. I want a way to get the bolded result based on the columns already there.

    Nov FY09 11/5/2008 12/5/2008 2008
    Dec FY09 12/6/2008 1/6/2009 2008
    Jan FY09 1/7/2009 2/4/2009 2009
    Feb FY09 2/5/2009 3/5/2009 2009
    Mar FY09 3/6/2009 4/3/2009 2009
    Apr FY09 4/4/2009 5/5/2009 FY09
    May FY09 5/6/2009 6/3/2009 FY09
    Jun FY09 6/4/2009 7/6/2009 FY09
    Jul FY10 7/7/2009 8/5/2009 FY10
    Aug FY10 8/6/2009 9/3/2009 FY10
    Sep FY10 9/4/2009 10/5/2009 FY10
    Oct FY10 10/6/2009 11/3/2009 2009

    Is there a way to list off say:
    {Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec} and then have an equivalent array with the possible outcomes?

    Thank you very much,
    Robin

  2. #2
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,720
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Avoiding ifs within ifs

    Is there a way to list off say:
    {Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec} and then have an equivalent array with the possible outcomes?
    Potentially, yes with the LOOKUP formula, but I think we need more information.
    What exactly is the critiera for determining the bolded column? What determines whether to use FYyy or yyyy? Which date column are you using for yyyy (column 3 or column 4)?
    I use Excel 2010 at work.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

  3. #3
    New Member
    Join Date
    Mar 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Avoiding ifs within ifs

    FYyy is meant to be any month between Apr and Sep. Oct to Mar will be yyyy. The columns are start date and end date. I'm using the month that correlates most to that time frame. I'd have yyyy calculated in the same manner.

    So that is why 12/6/2008 - 1/6/2009 is set to 2008 and not 2009.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,098
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Avoiding ifs within ifs

    Quote Originally Posted by TheNewGuy22 View Post
    FYyy is meant to be any month between Apr and Sep. Oct to Mar will be yyyy. The columns are start date and end date. I'm using the month that correlates most to that time frame. I'd have yyyy calculated in the same manner.

    So that is why 12/6/2008 - 1/6/2009 is set to 2008 and not 2009.
    Which month is being used to determine that... the one shown in Column A or the one shown in Column C?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    New Member
    Join Date
    Mar 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Avoiding ifs within ifs

    Column A.

    Column A is already using the following equation:

    Code:
    =IFERROR(TEXT(DATE(2000,MODE(MONTH(C2+(ROW(INDIRECT("1:"&1+D2-C2))-1))),1),"mmm"),"")
    This simply states which month has more dates within the range.

    Column B uses the following equation:
    Code:
    =IF(C2=0,"","FY"&RIGHT(YEAR(C2)-(MONTH(C2)<=6)+1,2))
    Column C and D are inputted. I want an equation for Column E that reflects these columns that have already been calculated.

  6. #6
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,720
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Avoiding ifs within ifs

    OK, Here I go...

    First, may I recommend this much simpler formula for column A
    =TEXT(C2+((D2-C2)/2),"MMM")

    Secondly, try this for column E using the same concept
    =IF(AND(MONTH(C2+((D2-C2)/2))>=4,MONTH(C2+((D2-C2)/2))<=9),B2,TEXT(C2+((D2-C2)/2),"YYYY"))
    I use Excel 2010 at work.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,098
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Avoiding ifs within ifs

    Quote Originally Posted by TheNewGuy22 View Post
    Column A.

    Column A is already using the following equation:

    Code:
    =IFERROR(TEXT(DATE(2000,MODE(MONTH(C2+(ROW(INDIRECT("1:"&1+D2-C2))-1))),1),"mmm"),"")
    This simply states which month has more dates within the range.

    Column B uses the following equation:
    Code:
    =IF(C2=0,"","FY"&RIGHT(YEAR(C2)-(MONTH(C2)<=6)+1,2))
    Column C and D are inputted. I want an equation for Column E that reflects these columns that have already been calculated.
    What would columns A, B and E contain if the date range was, say, 11/29/2008 and 12/29/2008?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Mar 2014
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Avoiding ifs within ifs

    Thank you BiocideJ! That worked exactly how I wanted it too. The month equation works, but February seems to give it issues due to the days difference. Is there a way to make sure it doesn't skip February like it did in the following example?

    Jul FY09 7/16/2008 8/14/2008 FY09
    Aug FY09 8/15/2008 9/15/2008 FY09
    Sep FY09 9/16/2008 10/14/2008 FY09
    Oct FY09 10/15/2008 11/14/2008 2008
    Nov FY09 11/15/2008 12/16/2008 2008
    Dec FY09 12/17/2008 1/15/2009 2008
    Jan FY09 1/16/2009 2/13/2009 2009
    Mar FY09 2/14/2009 3/16/2009 2009
    Mar FY09 3/17/2009 4/15/2009 2009
    Apr FY09 4/16/2009 5/14/2009 FY09
    May FY09 5/15/2009 6/12/2009 FY09
    Jun FY09 6/13/2009 7/15/2009 FY09

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,098
    Post Thanks / Like
    Mentioned
    58 Post(s)
    Tagged
    20 Thread(s)

    Default Re: Avoiding ifs within ifs

    I would still be interested in knowing the answer to the question I asked in Message #7.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular BiocideJ's Avatar
    Join Date
    Jan 2012
    Location
    Florida, USA
    Posts
    1,720
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Avoiding ifs within ifs

    The month equation works, but February seems to give it issues due to the days difference. Is there a way to make sure it doesn't skip February like it did in the following example?
    Can you verify that your examples are not working? I am getting the same results with my formula and your old formula.
    Just to test, I manually typed out all of the days between 2/14/2009 and 3/16/2009 (inclusively) and there are 15Feb and 16Mar which, if I understand your convention correctly, should show as MAR.
    I use Excel 2010 at work.
    Notusingindentsincodeislikenotusingspacesinsentences.Youcanmakeitout,butonlywithdifficulty.

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
  •