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
Bookmarks