+ Reply to Thread
Results 1 to 6 of 6

Added to an IF/AND array formula

  1. #1
    Forum Contributor
    Join Date
    02-09-2011
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    161

    Added to an IF/AND array formula

    Hi,

    In the attached example spreadsheet I have the following array formula:

    =IF(AND(G$1>=$A2,G$1<=$B2),1,0)

    It returns “1” between the Start and Finish dates provided in the table A1:C4.

    This works well but I have added a column to the table for Completion Certificate dates.

    I want “2” to be returned when G1 = C2 to show the completion certification date (I will use conditional formatting to generate a blue bar for the Start – Finish duration and a red cell for certification date).

    The purpose of this is checking for cases where the certification date is earlier than Finish date so that the certification date can be rescheduled.

    I think the first part of the formula needs to be something like, If G1 = C2 return 2, but I have not had any success in trying to add this to my array formula.

    How would the formula need to be modified to do this?

    Thanks very much

    Dave
    Attached Files Attached Files

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Added to an IF/AND array formula

    First its not array formula, you can just ENTERED as regular formula, and Im end up with this. put this on G2 and copied down and cross:

    =IF(AND(G$1>=$A2;G$1<=$B2);1;IF($C2=G$1;2;0))

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Added to an IF/AND array formula

    @ Motox.

    Your profile shows Excel 2003 yet the upload is a newer file format.

    Please take the time to update your profile. Members often consider this when offering solutions.

    If you are unfamiliar with how to do that go to the top of this page and click 'Forum Actions' then 'Edit profile'.

    Thank you.
    Dave

  4. #4
    Forum Contributor
    Join Date
    02-09-2011
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    161

    Re: Added to an IF/AND array formula

    Hi azumi,

    Thanks, but it doesn't work if the Completion Certificate Issued date is earlier than the Finish date (as in row 3 of the table). This is precisely what I am checking for.

    Thanks

    Dave

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Added to an IF/AND array formula

    Please try at G2
    =(G$1>=$A2)*(G$1<=$B2)+2*(G$1=$C2)

  6. #6
    Forum Contributor
    Join Date
    02-09-2011
    Location
    Perth, Australia
    MS-Off Ver
    2016
    Posts
    161

    Re: Added to an IF/AND array formula

    Thanks very much, Bo-Ry.

    I can't see how, but it works!

    Thanks again

    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Setting a dynamic file list in array to be added as mail attachments
    By brezgesla in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2018, 07:32 PM
  2. [SOLVED] Title to be added automatically once managers name added.
    By studio52 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2015, 05:37 AM
  3. Replies: 2
    Last Post: 04-15-2015, 05:33 PM
  4. [SOLVED] Way to have Array Formula automatically adjust when rows added
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2014, 04:00 PM
  5. [SOLVED] Array formula not expanding to match lines added to table
    By justmatt in forum Excel General
    Replies: 13
    Last Post: 05-03-2014, 02:16 PM
  6. Dynamic array in VBA of newly added worksheets
    By greglittle in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-22-2012, 06:00 PM
  7. Replies: 0
    Last Post: 09-29-2010, 04:50 PM

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