I'm trying to create a formula that auto-populates colum that meets a spefic criteria

Posted on 2014-08-13
Last Modified: 2014-08-13
COndition 1: (D3):I'm trying to figure a formula or way if the rank is AB or AMN or A1C or SRA or SSGT or TSGT then return "AF Form 931", If not return "AF Form 932"
  My attempt:  =IF(B3=AB,AF Form 931,IF(B3=AMN,AF Form 931,IF(B3=A1C,AF Form 931, AF Form 931)))

Condition 2: (G3)I'm trying to figure out how if status is equal to 1, or 2 and rank equals a specific rank midterm will equal a specific dates

Rank            Initial ACA Closeout      Midterm ACA Closeout
CMSgt             N/A                  31 Jan 2015
SMSgt             31 Jul 2014            31 Jul 2015
MSgt             N/A                  30 Sep 2014
TSgt             30 Nov 2014            30 Nov 2015
SSgt             31 Jan 2015            31 Jan 2016
SrA             N/A                  31 Mar 2015

If the status equals 3 and rank equals a specific rank midterm will equal a specific date:
Rank            Midterm ACA Closeout
CMSgt             31 Jan 2015
SMSgt             31 Mar 2015
MSgt             31 May 2015
TSgt             31 Jul 2015
SSgt             30 Sep 2015
SrA             30 Nov 2015
Question by:cfhasan1
    LVL 27

    Assisted Solution

    by:Glenn Ray
    Condition 1:  Insert this formula in cell D3 and copy down:
    =IF(OR(A3="AB",A3="AMN",A3="A1C",A3="SRA",A3="SSGT",A3="TSGT"),"AF Form 931","AF Form 932")

    Your example above listed B3 as the test, but your description says that you want to test the "rank" which is in column A.

    Alternatively, since you have a master list of valid ranks on the Data sheet and your test values are in cells C1:C6, you could use this formula:
    =IF(ISERROR(VLOOKUP(A3,Data!$C$1:$C$6,1,FALSE)),"AF Form 932","AF Form 931")

    I'll address your second condition separately.

    LVL 27

    Expert Comment

    by:Glenn Ray
    I have a few questions about Condition 2:  
    1) Do you want the formula that determines the default midterm to be in column H (starting in H3).  You list cell G3, but that is for the "Initial ACA Closeout Date."
    2) Do you only want to see the default "Midterm ACA Closeout Date" for status = 1 or 2, because you include some values in your example above for the "Initial ACA Closeout Date"
    3) What should be the default Midterm dates be for the other three possible ranks (AB, AMC, A1C)?

    I have a likely solution, but need to you clarify these questions before presenting it.


    Author Comment

    1) Yes, my mistake
    2)  If status=3 then,
    If the status equals 3 and rank equals a specific rank midterm will equal a specific date:
     Rank            Midterm ACA Closeout
     CMSgt             31 Jan 2015
     SMSgt             31 Mar 2015
     MSgt             31 May 2015
     TSgt             31 Jul 2015
     SSgt             30 Sep 2015
     SrA             30 Nov 2015

    3) N/A
    LVL 27

    Accepted Solution

    Here's my solution for condition 2:
    1a) Add a table for the default closeout dates for each possible rank and status.  This table would be added on a separate sheet, named appropriately "Closeout Dates".
    1b) Insert the default dates as per your specifications above.  For the three other ranks (AB, AMN, A1C), intentionally insert a formula (=1/0) to produce an error (#DIV/0!).  That's needed for the next formula.
    2) Insert the following formula in cell H3 and copy down:
    =IFERROR(SUMIFS('Closeout Dates'!$C$2:$C$28,'Closeout Dates'!$A$2:$A$28,C3,'Closeout Dates'!$B$2:$B$28,A3),"N/A")
    Because there are nine ranks and three status levels, that makes 27 possible combinations (rows 2:28).
    3) Change the formulas for G3 and I3 and copy down:
    G3: =IF(H3<>"N/A",H3-30,"N/A")
    I3: =IF(H3<>"N/A",H3+30,"N/A")

    I've attached a modified workbook for you to test.  Let me know if you have any questions.


    PS  I've also added Data Validation to the test cells in column A to reference the list of ranks on the "Data" sheet.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
    Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
    The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now