+ Reply to Thread
Results 1 to 6 of 6

Sumproduct, Max, Index, Match and Randbetween in one Formula

  1. #1
    Registered User
    Join Date
    05-02-2017
    Location
    Scranton, PA
    MS-Off Ver
    2010
    Posts
    11

    Sumproduct, Max, Index, Match and Randbetween in one Formula

    Greetings!

    I would appreciate some assistance in trouble shooting a formula.

    Formula should be placed in C2

    =IF(AND(SUMPRODUCT((Table_General[Fee Effect Date]=MAX(Table_General[Fee Effect Date]))*(Table_General[Frm]=[@[FRM Age]])*(Table_General[Thru]=[@[THRU Age]]))),INDEX(Table_General[Proc Code],RANDBETWEEN(1,COUNTA(Table_General[Proc Code]))),0)

    What I am attempting to do is get at random a procedure code on the General tab if the following criteria is met:

    If Test plan tab POS is 11 (Cell E2).
    1.The date is MAX on General Tab Colum L
    2.The General Tab Frm Column and Thru Column matches Test Plan Tab Frm Age (Cell G2) and Thru Age (Cell H2)
    OR
    If Test plan tab POS is 22 (Cell E2).
    1.The date is MAX on General Tab Colum U
    2.The General Tab Frm Column and Thru Column matches Test Plan Tab Frm Age (Cell G2) and Thru Age (Cell H2)
    OR
    If Test plan tab POS is 12 (Cell E2).
    1.The date is MAX on General Tab Colum L
    2.The Note Codes on the General Tab Column O = "P2"
    3.The General Tab Frm Column and Thru Column matches Test Plan Tab Frm Age (Cell G2) and Thru Age (Cell H2)

    Select at random from all possible matches a proc code from the General Tab (Column C)

    I have attached a template and an image. Yellow highlighted is the criteria that should match. Green highlighted is the Procedure code that should be selected at random and placed in Red highlighted.
    I do generate a procedure code however, the date is not max, I haven't included a reference to the POS, and the from and thru are not on the same "line" on the General tab

    Example3.jpg
    Attached Files Attached Files
    Last edited by shpaul; 06-07-2017 at 03:35 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Sumproduct, Max, Index, Match and Randbetween in one Formula

    In your formula

    "INDEX(Table_General,MATCH(1,(Table_General[Frm]='Test Plan'!G2)*(Table_General[Thru]='Test Plan'!H2),RANDBETWEEN(1,COUNTA(Table_General[Proc Code]))))"

    which field for Index(TABLE_GENERAL are your expecting to capture?

    I was assuming you're indexing for range C27:C3723 (within the General tab); and if this is the case, then state it.

    I aslo changed the following:
    - 'Test Plan'!G2 to [@[FRM Age]]
    - 'Test Plan'!H2 to [@[THRU Age]]

    See attachment
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-02-2017
    Location
    Scranton, PA
    MS-Off Ver
    2010
    Posts
    11

    Re: Sumproduct, Max, Index, Match and Randbetween in one Formula

    Hi Syrkrasi,

    I have since updated the criteria in the initial post of this thread. I am unable to validate because it needs to pull codes with the Max date and the Proc code would also take into consideration the POS. Also, yes I should be indexing the Proc Code of the General tab.

    Thanks,
    Sherline

  4. #4
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Sumproduct, Max, Index, Match and Randbetween in one Formula

    I've changed the calculation in Test Plan field C4

    From: INDEX(Table_General[Proc Code],MATCH(1,(Table_General[Frm]=[@[FRM Age]])*(Table_General[Thru]=[@[THRU Age]]),RANDBETWEEN(1,COUNTA(Table_General[Proc Code]))))

    To: INDEX(Table_General[Proc Code],MATCH(1,(Table_General[Frm]=[@[FRM Age]])*(Table_General[Thru]=[@[THRU Age]])*(General!L24=Table_General[Fee Effect Date]),0))

    Let me know if this is what you're trying to acheive. See attachment
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-02-2017
    Location
    Scranton, PA
    MS-Off Ver
    2010
    Posts
    11

    Re: Sumproduct, Max, Index, Match and Randbetween in one Formula

    Thank for the revision. However it is not working either!... I'm going to keep plugging away as I have to have this Template submitted by Friday.

    Thank,
    Sherline

  6. #6
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Sumproduct, Max, Index, Match and Randbetween in one Formula

    Sherline, when you go to the General tab and performed the process manually, what is the result (the proc code that should be received)?

+ 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. [SOLVED] Help with If, Match, Index, and Randbetween all in one formula
    By shpaul in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-07-2017, 10:30 AM
  2. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  3. Sumproduct or index match dynamic formula
    By jw01 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-18-2013, 07:54 PM
  4. [SOLVED] VBA or formula. match, index and sumproduct
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-18-2012, 02:46 PM
  5. Index/Match or Sumproduct formula
    By pauldaddyadams in forum Excel General
    Replies: 5
    Last Post: 01-19-2012, 11:32 AM
  6. Sumproduct, or Index / Match Formula
    By bountifulgrace in forum Excel General
    Replies: 6
    Last Post: 11-03-2006, 04:55 PM

Tags for this Thread

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