Results 1 to 6 of 6

Thread: Creating nested IF(AND) statement to produce correct data

  1. #1

    Creating nested IF(AND) statement to produce correct data



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

    Hi there,

    I am working with a spreadsheet that deals with construction costs that is giving me trouble. In this spreadsheet, I am given the total cost of construction as well as the start and end date for the project. My goal is to approximate (on a monthly basis) what the total spend per project is. For instance, if the project costs $10mm and lasts from Jan-May 2015, I want Excel to show $2mm in each cell from Jan-15 to May-15 and shows zeros in all other months and years.

    The current formula that I'm using is:

    =IF(AND($G3>T$1,T$1<$K3)=TRUE,+$O3,0)

    Where column G is the project start date (formatted as mm/dd/yyyy), column T is the month and year (formatted as mmm-yy, or Jan-15), column K is the project end date, and column O is the approximated monthly spend. My logic, then, is trying to say:

    - If the project start date is greater than or equal to the month and year AND

    - If the project end date is less than or equal to the month and year, place the monthly spend approximation in this cell.

    Currently, my formula is taking projects greater than or equal to 2014 and placing the monthly approximation in all twelve months. For the life of me, I cannot figure out why this is...

    Don't hesitate to let me know if I can help with further information. Any insight is much appreciated!

    Thanks,

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Should it be?

    =IF(AND($G3<=T$1,T$1<$K3)=TRUE,$O3,0)


  3. #3
    Thanks, NBVC! That worked partially...the only downside is that I'm still getting zeros for anything past a certain date (in this case, March '15) even if the project starts or extends past that date. Could that be a formatting issue, or is something still off with the formula?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Are the dates in Row 1 (starting I guess at T1) all listed as the first of the month? If so, then maybe you need to adjust the dates in the G and K to 1st of the months, via the formula...

    e.g.. trry

    =IF(AND(EOMONTH($G3,-1)+1<=T$1,T$1<=EOMONTH($K3,-1)+1)=TRUE,$O3,0)


  5. #5
    No need to overcomplicate. The correct numbers were given with a slightly-modified version of your original suggestion:

    =IF(AND($G3<=T$1,T$1<=$K3)=TRUE,+$O3,0)

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Well, I wasn't sure how exactly your dates were set up... so great that you got it.


Posting Permissions

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