+ Reply to Thread
Results 1 to 9 of 9

Neste IF statements with 3 variables

  1. #1
    Registered User
    Join Date
    06-29-2013
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Question Neste IF statements with 3 variables

    I am using Excel 2010 and trying to write and a nested IF statement that does 4 things.
    1. Find text 2014 in column B, for budget year 2014.
    2. If it finds 2014 in column B, then find two digit month text in column C.
    3. If 1 & 2 are true then place column D in that month of 2014.
    4. If column C has more than 1 month code in 2014 than split $ amount of column D by total number of month codes in column C.

    Here is my current formula that needs some work.
    =IF(OR(MID($B4,1,4)="2014",MID($B4,6,4)="2014",),IF(OR(MID($C4,1,1)=TEXT(F$2,"#"),MID($C4,3,1)=TEXT(F$2,"#"),MID($C4,5,1)=TEXT(F$2,"#"),),IF(LEN($C4)<3,$D4,IF(LEN($C4)<=4,$D4/2,IF(LEN($C4)>4,$D4/3,0)))))

    Thanks for reading,
    Ken
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Neste IF statements with 3 variables

    Hi and welcome to the forum

    looking at your formula, what is this part supposed to be doing?
    IF(OR(MID($C13,1,1)=TEXT(F$2,"#"),MID($C13,3,1)=TEXT(F$2,"#"),MID($C13,5,1)=TEXT(F$2,"#")),
    F2=01 and C13 = 10,11,12

    I would have thought that if you were looking for 01 in 10,11,12, you would not find a match, yet in F13, you have a value?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-29-2013
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Neste IF statements with 3 variables

    Here is another modification I tried that works a little better for mont 01 through 09. Not sure why I am getting FALSE in some cells.

    =IF(OR(MID($B10,1,4)="2014",MID($B10,6,4)="2014",),IF(OR(MID($C10,2,1)=TEXT(I$2,"#"),MID($C10,5,1)=TEXT(I$2,"#"),MID($C10,8,1)=TEXT(I$2,"#"),),IF(LEN($C10)<3,$D10,IF(LEN($C10)<=5,$D10/2,IF(LEN($C10)>5,$D10/3,0)))))

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Neste IF statements with 3 variables

    I would have thought that if you were looking for 01 in 10,11,12, you would not find a match, yet in F13, you have a value?
    before I go any further, can you comment on that please?

  5. #5
    Registered User
    Join Date
    06-29-2013
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Neste IF statements with 3 variables

    Here is a third iteration that fixes the FALSE but has problems when month cover 2 years (2013,2014 or 2014,2015). See row 30 for example, I only want data in January 2014 but also get December from 2013. I understand why it doesn't work but not sure how to fix it.

    =IF(OR(MID($B30,1,4)="2014",MID($B30,6,4)="2014",),IF(OR(MID($C30,1,2)=F$2,MID($C30,4,2)=F$2,MID($C30,7,2)=F$2,),IF(LEN($C30)<3,$D30,IF(LEN($C30)<=5,$D30/2,IF(LEN($C30)>5,$D30/3,0))),0),0)

  6. #6
    Registered User
    Join Date
    06-29-2013
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Neste IF statements with 3 variables

    The Text function was truncating the F column to a single digit, now I am looking for the full text string "01","02","03" etc.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Neste IF statements with 3 variables

    OK give this a try....

    =IF(ISNUMBER(SEARCH(2014,$B13,1)),IF(ISNUMBER(SEARCH(F$2,$C13,1)),IF(LEN($C13)<3,$D13,IF(LEN($C13)<=4,$D13/2,IF(LEN($C13)>4,$D13/3,0))),""),"")

  8. #8
    Registered User
    Join Date
    06-29-2013
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Neste IF statements with 3 variables

    There is still the issue of row 30 where December is actually 2013 data and shouldn't appear. But the Month(s) column does not distinguish what year the month lies. This might require a little manual editing but will still save lots of time. The worksheet is actuall much bigger and linked to another worksheet that is still in flux. Once they approve the final update I will only need some minor edits for the 2014 cash flow budget. This is probabaly solved for now.
    Thanks again,
    Ken

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,116

    Re: Neste IF statements with 3 variables

    Happy to help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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