Results 1 to 7 of 7

Thread: Help with complex IF function

  1. #1

    Unhappy Help with complex IF function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello All,

    Please assist if you can. This is what I'm trying to get..

    What I'm trying to do is to get Column C~N to pick up a IF statement based off of data coming from Column A and B.

    Let's take A2 and A3 for example..

    If A3 is empty.. Then C2:N2 will output B2/12 all throughout Row 2 to Column N (Dec.)

    Now..

    If A3 has a date (this date will always be greater than the previous Aug-13 in this case, then it will start the calculation at J2 (Aug.) going through N2

    I hope this explains what I'm trying to do..

    I've tried to use IF and Lookup formula but it just doesn't work and I can't get the logic right..

    Please support,
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    Excel 2016
    Try:

    =IFERROR(IF(ISNUMBER($A3),IF(C$1>=EOMONTH($A3,-1)+1,$B2/12,0),$B2/12),0)

    copied across and down.


  3. #3
    Hello NBVC,

    Thanks so much for your assistance. I found it working for the first row.. however; when I try to duplicate that same formula across and down.. some cells towards the bottom are not responding correctly. I've checked if its the absolute value that had something to do with it, but its not. Could you please take a look for me, or even better if you could send me the version copied down.

    thank you for your help!

    Han

    Quote Originally Posted by NBVC View Post
    Try:

    =IFERROR(IF(ISNUMBER($A3),IF(C$1>=EOMONTH($A3,-1)+1,$B2/12,0),$B2/12),0)

    copied across and down.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    Excel 2016
    Here is what I get....
    Attached Files Attached Files


  5. #5
    Hi there,

    I see what you did and that was my initial fix too, but then you realize in the following rows (ex: row 6 and 7, where it should start on May-2013.. but it starts at Jan)



    Quote Originally Posted by NBVC View Post
    Here is what I get....

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,490
    Articles
    0
    Excel Version
    Excel 2016
    What I understood from your initial statement:

    ...If A3 is empty.. Then C2:N2 will output B2/12 all throughout Row 2 to Column N (Dec.)...
    is that if the second date cell in the row was empty we would just divide B2 by 12 all the way across. In rows 6:7, A7 is empty, so I just divided B6 by 12 all the way across.

    Did I misunderstand?


  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    778
    Articles
    0
    Excel Version
    2010
    Hi
    Whilst looking at this, I noticed that some of the dollar figures are formatted wrongly because in the pipeline sheet the leading dollar sign has been typed into the cell, making it text rather than a number.
    This will force an error instead of the 1/12 calculation.

    HTH

    Herc

Posting Permissions

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