Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF Statement (Excel 2003)

    I have the worst time with IF statements <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    I need to add an additional IF statement to the formula below that would return a 0 if the date is a holiday. "Holidays" is my named range ($U$3:$U$7). I need to add it to the front of this formula but am not sure how to do this...or at least everything that I have tried is failing. This needs to look at Cell A7 to determine if it falls within the range of holidays.

    =IF($B7=2,0,SUM(SUMPRODUCT((FMonth>=EOMONTH($A7,-1)+1)*COAF)-SUMPRODUCT((FMonth>=EOMONTH($A7,0))*COAF))/$S7)

    I appreciate your help as always.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF Statement (Excel 2003)

    Try

    =IF(ISERROR(MATCH($A7,Holidays,0)),IF($B7=2,0,SUM( SUMPRODUCT((FMonth>=EOMONTH($A7,-1)+1)*COAF)-SUMPRODUCT((FMonth>=EOMONTH($A7,0))*COAF))/$S7),0)

    The formula uses MATCH to look up A7 in the Holidays range. If this results in an error, there is no match i.e. A7 is *not* a holiday, so your formula is used. Otherwise (A7 is a holiday), the formula returns 0.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Statement (Excel 2003)

    Thanks Hans! Have a fabulous weekend!!

Posting Permissions

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