{"text_format":"1","workbook":"xlsx","n_sheets":12,"sheets":["Cover","Navigator","Style Guide","Model Parameters","Timing","IFS Examples","SWITCH Example","CONCAT Example","TEXTJOIN Example","MAXIFS Example","MINIFS Example","Error Checks"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":42,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Cover","index":0,"n_rows":22,"n_cols":7,"rows_kept":22,"cut":false,"hidden":false,"merged":[[16,2,16,9],[17,2,17,9],[20,6,20,8],[21,6,21,8]]}
[]
[]
["Navigator"]
[]
[null,null,{"$f":"Client_Name","v":"SumProduct Pty Ltd"}]
[null,null,{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Primary Developer:  Liam Bastick"]
[]
[null,null,"General Cover Notes:"]
[null,null,"PLEASE NOTE: New Excel 2016 functions will obviously not work in many versions of Excel!!"]
[]
[]
[]
[null,null,"Any queries, please e-mail:",null,null,null,"liam.bastick@sumproduct.com"]
[null,null,"Website:",null,null,null,"www.sumproduct.com"]

{"sheet":"Navigator","index":1,"n_rows":19,"n_cols":7,"rows_kept":19,"cut":false,"hidden":false,"merged":[]}
["Navigator"]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,null,null,null,"Error Checks:",null,{"$f":"Overall_Error_Check","v":0}]
[]
[]
[null,1,"Table of Contents"]
[]
[null,null,null,null,null,"Cover"]
[null,null,null,null,null,"Style Guide"]
[null,null,null,null,null,"Model Parameters"]
[null,null,null,null,null,"Timing"]
[null,null,null,null,null,"IFS Examples"]
[null,null,null,null,null,"SWITCH Example"]
[null,null,null,null,null,"CONCAT Example"]
[null,null,null,null,null,"TEXTJOIN Example"]
[null,null,null,null,null,"MAXIFS Example"]
[null,null,null,null,null,"MINIFS Example"]
[null,null,null,null,null,"Error Checks"]

{"sheet":"Style Guide","index":2,"n_rows":74,"n_cols":11,"rows_kept":74,"cut":false,"hidden":false,"merged":[[16,2,16,6],[2,0,2,4],[7,2,7,6],[8,2,8,6],[9,2,9,6],[10,2,10,6],[11,2,11,6],[12,2,12,6],[13,2,13,6],[14,2,14,6],[15,2,15,6],[34,2,34,6],[17,2,17,6],[18,2,18,6],[19,2,19,6],[24,2,24,6],[25,2,25,6],[26,2,26,6],[27,2,27,6],[28,2,28,6],[29,2,29,6],[30,2,30,6],[31,2,31,6],[46,2,46,6],[35,2,35,6],[36,2,36,6],[37,2,37,6],[38,2,38,6],[39,2,39,6],[40,2,40,6],[41,2,41,6],[42,2,42,6],[43,2,43,6],[44,2,44,6],[45,2,45,6],[61,2,61,6],[47,2,47,6],[48,2,48,6],[49,2,49,6],[50,2,50,6],[51,2,51,6],[52,2,52,6],[53,2,53,6],[54,2,54,6],[57,2,57,6],[59,2,59,6],[60,2,60,6],[73,2,73,6],[62,2,62,6],[63,2,63,6],[64,2,64,6],[65,2,65,6],[66,2,66,6],[67,2,67,6],[68,2,68,6],[69,2,69,6],[70,2,70,6],[71,2,71,6],[72,2,72,6],[80,2,80,6],[74,2,74,6],[75,2,75,6],[76,2,76,6],[77,2,77,6],[78,2,78,6],[79,2,79,6]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Style Guide"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,null,null,null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Formatting of Headers / Dividers"]
[]
[null,null,"Description",null,null,null,null,null,"Display",null,"Style Name"]
[]
[null,null,"Sheet Title",null,null,null,null,null,{"$f":"C10","v":"Sheet Title"},null,"Sheet Title"]
[null,null,"Model Name",null,null,null,null,null,{"$f":"C11","v":"Model Name"},null,"Model Name"]
[]
[null,null,"Header 1",null,null,null,null,null,{"$f":"C13","v":"Header 1"},null,"Header 1"]
[null,null,"Header 2",null,null,null,null,null,{"$f":"C14","v":"Header 2"},null,"Header 2"]
[null,null,"Header 3",null,null,null,null,null,{"$f":"C15","v":"Header 3"},null,"Header 3"]
[null,null,"Header 4",null,null,null,null,null,{"$f":"C16","v":"Header 4"},null,"Header 4"]
[]
[null,null,"Notes",null,null,null,null,null,{"$f":"C18","v":"Notes"},null,"Notes"]
[]
[null,null,"Table Heading",null,null,null,null,null,{"$f":"C20","v":"Table Heading"},null,"Table Heading"]
[]
[]
[null,{"$f":"MAX($B$5:$B22)+1","v":2},"Individual Cell Styles"]
[]
[null,null,"Description",null,null,null,null,null,"Display",null,"Style Name"]
[]
[null,null,"Assumption",null,null,null,null,null,"Assumption",null,{"$f":"C27","v":"Assumption"}]
[]
[null,null,"Constraint",null,null,null,null,null,{"$f":"C29","v":"Constraint"},null,{"$f":"C29","v":"Constraint"}]
[]
[null,null,"Empty",null,null,null,null,null,null,null,{"$f":"C31","v":"Empty"}]
[]
[null,null,"Error Check",null,null,null,null,null,0,null,{"$f":"C33","v":"Error Check"}]
[]
[null,null,"Hyperlink",null,null,null,null,null,"Hyperlink",null,{"$f":"C35","v":"Hyperlink"}]
[]
[null,null,"Internal Reference",null,null,null,null,null,{"$f":"'Error Checks'!E12","v":"No checks present"},null,{"$f":"C37","v":"Internal Reference"}]
[]
[null,null,"Line Calculation",null,null,null,null,null,77,null,"Line Calc"]
[]
[null,null,"Line Total",null,null,null,null,null,{"$f":"I39","v":77},null,{"$f":"C41","v":"Line Total"}]
[]
[null,null,"Parameter",null,null,null,null,null,365,null,{"$f":"C43","v":"Parameter"}]
[]
[null,null,"Range Name Description",null,null,null,null,null,"Not_Named",null,{"$f":"C45","v":"Range Name Description"}]
[]
[null,null,"Row Reference",null,null,null,null,null,{"$f":"ROW(C47)","v":47},null,"Row Ref"]
[]
[null,null,"Row Summary",null,null,null,null,null,{"$f":"I41","v":77},null,{"$f":"C49","v":"Row Summary"}]
[]
[null,null,"Units",null,null,null,null,null,"A$",null,{"$f":"C51","v":"Units"}]
[]
[null,null,"WIP",null,null,null,null,null,null,null,{"$f":"C53","v":"WIP"}]
[]
[]
[null,{"$f":"MAX($B$5:$B55)+1","v":3},"Numerical Styles"]
[]
[null,null,"Description",null,null,null,null,null,"Display",null,"Style Name"]
[]
[null,null,"Comma",null,null,null,null,null,123456.789,null,{"$f":"C60","v":"Comma"}]
[]
[null,null,"Comma [0]",null,null,null,null,null,-123456.789,null,"Comma [0]"]
[]
[null,null,"Currency",null,null,null,null,null,123456.789,null,"Currency"]
[]
[null,null,"Currency [0]",null,null,null,null,null,123456.789,null,"Currency [0]"]
[]
[null,null,"Date",null,null,null,null,null,{"$f":"TODAY()","v":"2016-04-12"},null,{"$f":"C68","v":"Date"}]
[]
[null,null,"Date Heading",null,null,null,null,null,{"$f":"TODAY()","v":"2016-04-12"},null,{"$f":"C70","v":"Date Heading"}]
[]
[null,null,"Numbers 0",null,null,null,null,null,-123456.789,null,{"$f":"C72","v":"Numbers 0"}]
[]
[null,null,"Percent",null,null,null,null,null,0.5,null,{"$f":"C74","v":"Percent"}]

{"sheet":"Model Parameters","index":3,"n_rows":31,"n_cols":9,"rows_kept":31,"cut":false,"hidden":false,"merged":[[0,9,0,10],[2,0,2,4],[10,6,10,13],[11,6,11,13]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Model Parameters"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,null,null,null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"General"]
[]
[null,null,"Key Inputs"]
[]
[null,null,null,null,"General"]
[null,null,null,null,"Model Name",null,{"$f":"IF(ISERROR(OR(FIND(\"[\",CELL(\"filename\",A1)),FIND(\"]\",CELL(\"filename\",A1)))),\"\",MID(CELL(\"filename\",A1),FIND(\"[\",CELL(\"filename\",A1))+1,FIND(\"]\",CELL(\"filename\",A1))-FIND(\"[\",CELL(\"filename\",A1))-1))","v":"SP - New Excel 2016 Functions.xlsm"}]
[null,null,null,null,"Client Name",null,"SumProduct Pty Ltd"]
[]
[]
[null,{"$f":"MAX($B$5:$B14)+1","v":2},"General Range Names"]
[]
[null,null,"Technical Assumptions"]
[]
[null,null,null,null,"Days in Year",null,365]
[null,null,null,null,"Months in Month",null,1]
[null,null,null,null,"Months in Quarter",null,3]
[null,null,null,null,"Months in Half Yr",null,6]
[null,null,null,null,"Months in Year",null,12]
[null,null,null,null,"Quarters in Year",null,4]
[]
[null,null,null,null,"Rounding Accuracy",null,5]
[]
[null,null,null,null,"Very Large Number",null,9.9999999999999997E+98]
[null,null,null,null,"Very Small Number",null,1E-8]
[]
[null,null,null,null,"Thousand",null,1000]

{"sheet":"Timing","index":4,"n_rows":23,"n_cols":14,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,8,0,9],[2,0,2,4]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Timing"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[null,null,null,null,null,null,null,null,null,{"$f":"J$7","v":"2016-06-30"},{"$f":"K$7","v":"2016-09-30"},{"$f":"L$7","v":"2016-12-31"},{"$f":"M$7","v":"2017-03-31"},{"$f":"N$7","v":"2017-06-30"}]
[null,null,"Start Date",null,null,null,null,null,null,{"$f":"IF(J$9=1,Model_Start_Date,I$7+1)","v":"2016-04-12"},{"$f":"IF(K$9=1,Model_Start_Date,J$7+1)","v":"2016-07-01"},{"$f":"IF(L$9=1,Model_Start_Date,K$7+1)","v":"2016-10-01"},{"$f":"IF(M$9=1,Model_Start_Date,L$7+1)","v":"2017-01-01"},{"$f":"IF(N$9=1,Model_Start_Date,M$7+1)","v":"2017-04-01"}]
[null,null,"End Date",null,null,null,null,null,null,{"$f":"EOMONTH(J$6,MOD(Periodicity+Reporting_Month_Factor-MONTH(J$6),Periodicity))","v":"2016-06-30"},{"$f":"EOMONTH(K$6,MOD(Periodicity+Reporting_Month_Factor-MONTH(K$6),Periodicity))","v":"2016-09-30"},{"$f":"EOMONTH(L$6,MOD(Periodicity+Reporting_Month_Factor-MONTH(L$6),Periodicity))","v":"2016-12-31"},{"$f":"EOMONTH(M$6,MOD(Periodicity+Reporting_Month_Factor-MONTH(M$6),Periodicity))","v":"2017-03-31"},{"$f":"EOMONTH(N$6,MOD(Periodicity+Reporting_Month_Factor-MONTH(N$6),Periodicity))","v":"2017-06-30"}]
[null,null,"Number of Days",null,null,null,null,null,null,{"$f":"J7-J6+1","v":80},{"$f":"K7-K6+1","v":92},92,90,91]
[null,null,"Counter",null,null,null,null,null,null,{"$f":"N(I$9)+1","v":1},{"$f":"N(J$9)+1","v":2},3,4,5]
[]
[null,{"$f":"MAX($B$10:$B10)+1","v":1},"Timing Assumptions"]
[]
[null,null,"Data (do not change once modelling has commenced)"]
[]
[null,null,null,"Model Start Date",null,null,null,{"$f":"TODAY()","v":"2016-04-12"}]
[]
[null,null,null,"Number of Months in a Full Period",null,null,null,3]
[]
[null,null,null,"Example Reporting Month",null,null,null,12,{"$f":"\"e.g. \"&TEXT(DATE(YEAR(Model_Start_Date)+IF(Example_Reporting_Month<MONTH(Model_Start_Date),1,0),Example_Reporting_Month+1,1)-1,\"dd-Mmm-yy\")","v":"e.g. 31-Dec-16"}]
[]
[null,null,null,"Reporting Month Factor",null,null,null,{"$f":"MOD(Example_Reporting_Month-1,Periodicity)+1","v":3}]
[]
[null,null,null,"Months per Year",null,null,null,12]

{"sheet":"IFS Examples","index":5,"n_rows":41,"n_cols":11,"rows_kept":41,"cut":false,"hidden":false,"merged":[[2,0,2,4],[38,10,38,16],[40,10,40,16]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"IFS Examples"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Excel Guru Example"]
[]
[null,null,"Becoming a Qualified Excel 2016 Guru"]
[]
[null,null,null,"Criteria"]
[]
[null,null,null,null,null,null,"Criteria","Yes / No","Grade"]
[null,null,null,null,null,null,"Already qualified?","Yes","3 Star"]
[null,null,null,null,null,null,"Work for Microsoft?",null,"2 Star"]
[null,null,null,null,null,null,"Passed exam?","Yes","1 Star"]
[null,null,null,null,null,null,"Studying?",null,"Student"]
[]
[null,null,null,null,null,null,"Grade Achieved",null,{"$f":"_xlfn.IFS(H13=\"Yes\",I13,H14=\"Yes\",I14,H15=\"Yes\",I15,H16=\"Yes\",I16,TRUE,\"Not a Guru\")","v":"3 Star"},null,{"$f":"_xlfn.FORMULATEXT(I18)","v":"=IFS(H13=\"Yes\",I13,H14=\"Yes\",I14,H15=\"Yes\",I15,H16=\"Yes\",I16,TRUE,\"Not a Guru\")"}]
[]
[null,null,null,null,null,null,"Alternative Approach",null,{"$f":"IF(H13=\"Yes\",I13,IF(H14=\"Yes\",I14,IF(H15=\"Yes\",I15,IF(H16=\"Yes\",I16,\"Not a Guru\"))))","v":"3 Star"},null,{"$f":"_xlfn.FORMULATEXT(I20)","v":"=IF(H13=\"Yes\",I13,IF(H14=\"Yes\",I14,IF(H15=\"Yes\",I15,IF(H16=\"Yes\",I16,\"Not a Guru\"))))"}]
[]
[]
[null,{"$f":"MAX($B$5:$B22)+1","v":2},"Leap Year Example"]
[]
[null,null,"Determining The Number of Days in a Year"]
[]
[null,null,null,"Assumptions"]
[]
[null,null,null,null,null,null,"Type of Year","# Days"]
[null,null,null,null,null,null,"Standard Year",365]
[null,null,null,null,null,null,"Leap Year",366]
[]
[]
[null,null,null,"Year Selection"]
[]
[null,null,null,null,null,null,"Year",2016]
[]
[null,null,null,null,null,null,"Determination",{"$f":"LOOKUP(H39,H30:H31,G30:G31)","v":"Leap Year"},null,null,{"$f":"_xlfn.FORMULATEXT(H38)","v":"=LOOKUP(H39,H30:H31,G30:G31)"}]
[null,null,null,null,null,null,{"$f":"H29","v":"# Days"},{"$f":"_xlfn.IFS(MOD(Year_Selected,400)=0,Leap_Year,MOD(Year_Selected,4)<>0,Standard_Year,MOD(Year_Selected,100)=0,Standard_Year,TRUE,Leap_Year)","v":366},null,null,{"$f":"_xlfn.FORMULATEXT(H39)","v":"=IFS(MOD(Year_Selected,400)=0,Leap_Year,MOD(Year_Selected,4)<>0,Standard_Year,MOD(Year_Selected,100)=0,Standard_Year,TRUE,Leap_Year)"}]
[]
[null,null,null,null,null,null,{"$f":"G20","v":"Alternative Approach"},{"$f":"IF(OR(MOD(Year_Selected,400)=0,AND(MOD(Year_Selected,4)=0,MOD(Year_Selected,100)<>0)),Leap_Year,Standard_Year)","v":366},null,null,"=IF(OR(MOD(Year_Selected,400)=0,AND(MOD(Year_Selected,4)=0,MOD(Year_Selected,100)<>0)),Leap_Year,Standard_Year)"]

{"sheet":"SWITCH Example","index":6,"n_rows":46,"n_cols":9,"rows_kept":46,"cut":false,"hidden":false,"merged":[[2,0,2,4],[45,7,45,15],[43,7,43,15]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"SWITCH Example"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"That's About the Size of it"]
[]
[null,null,"What Size Clothing?"]
[]
[null,null,null,"Illustration"]
[]
[null,null,null,null,null,null,"Item Code","Size","Alternative"]
[null,null,null,null,null,null,"38370-XL-Yellow",{"$f":"_xlfn.SWITCH(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1),\"XS\",\"Extra Small\",\"S\",\"Small\",\"M\",\"Medium\",\"L\",\"Large\",\"XL\",\"Extra Large\",\"Not Specified\")","v":"Extra Large"},{"$f":"IF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"XS\",\"Extra Small\",\nIF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"S\",\"Small\",\nIF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"M\",\"Medium\",\nIF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"L\",\"Large\",\nIF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"XL\",\"Extra Large\",\"Not Specified\")))))","v":"Extra Large"}]
[null,null,null,null,null,null,"62141-L-Red",{"$f":"_xlfn.SWITCH(MID($G14,SEARCH(\"-\",$G14)+1,SEARCH(\"-\",$G14,(SEARCH(\"-\",$G14)+1))-SEARCH(\"-\",$G14)-1),\"XS\",\"Extra Small\",\"S\",\"Small\",\"M\",\"Medium\",\"L\",\"Large\",\"XL\",\"Extra Large\",\"Not Specified\")","v":"Large"},{"$f":"IF(MID($G14,SEARCH(\"-\",$G14)+1,SEARCH(\"-\",$G14,(SEARCH(\"-\",$G14)+1))-SEARCH(\"-\",$G14)-1)=\"XS\",\"Extra Small\",\nIF(MID($G14,SEARCH(\"-\",$G14)+1,SEARCH(\"-\",$G14,(SEARCH(\"-\",$G14)+1))-SEARCH(\"-\",$G14)-1)=\"S\",\"Small\",\nIF(MID($G14,SEARCH(\"-\",$G14)+1,SEARCH(\"-\",$G14,(SEARCH(\"-\",$G14)+1))-SEARCH(\"-\",$G14)-1)=\"M\",\"Medium\",\nIF(MID($G14,SEARCH(\"-\",$G14)+1,SEARCH(\"-\",$G14,(SEARCH(\"-\",$G14)+1))-SEARCH(\"-\",$G14)-1)=\"L\",\"Large\",\nIF(MID($G14,SEARCH(\"-\",$G14)+1,SEARCH(\"-\",$G14,(SEARCH(\"-\",$G14)+1))-SEARCH(\"-\",$G14)-1)=\"XL\",\"Extra Large\",\"Not Specified\")))))","v":"Large"}]
[null,null,null,null,null,null,"88756-XL-Blue","Extra Large","Extra Large"]
[null,null,null,null,null,null,"25110-XS-Black","Extra Small","Extra Small"]
[null,null,null,null,null,null,"63333-S-Blue","Small","Small"]
[null,null,null,null,null,null,"64794-XL-Blue","Extra Large","Extra Large"]
[null,null,null,null,null,null,"87168-XS-Green","Extra Small","Extra Small"]
[null,null,null,null,null,null,"60786-XL-Yellow","Extra Large","Extra Large"]
[null,null,null,null,null,null,"97198-L-Red","Large","Large"]
[null,null,null,null,null,null,"16592-L-White","Large","Large"]
[null,null,null,null,null,null,"31352-M-Yellow","Medium","Medium"]
[null,null,null,null,null,null,"30466-2XL-White","Not Specified","Not Specified"]
[null,null,null,null,null,null,"51887-XS-Black","Extra Small","Extra Small"]
[null,null,null,null,null,null,"55233-XL-Yellow","Extra Large","Extra Large"]
[null,null,null,null,null,null,"79163-S-Blue","Small","Small"]
[null,null,null,null,null,null,"33154-XL-Green","Extra Large","Extra Large"]
[null,null,null,null,null,null,"73618-XS-Black","Extra Small","Extra Small"]
[null,null,null,null,null,null,"46011-M-Red","Medium","Medium"]
[null,null,null,null,null,null,"35653-XL-Black","Extra Large","Extra Large"]
[null,null,null,null,null,null,"32210-S-Blue","Small","Small"]
[null,null,null,null,null,null,"24501-L-Red","Large","Large"]
[null,null,null,null,null,null,"41877-XL-Yellow","Extra Large","Extra Large"]
[null,null,null,null,null,null,"25117-XS-Green","Extra Small","Extra Small"]
[null,null,null,null,null,null,"79749-XL-Red","Extra Large","Extra Large"]
[null,null,null,null,null,null,"93833-L-Green","Large","Large"]
[null,null,null,null,null,null,"24112-S-Blue","Small","Small"]
[null,null,null,null,null,null,"21479-M-White","Medium","Medium"]
[null,null,null,null,null,null,"98718-XL-Yellow","Extra Large","Extra Large"]
[null,null,null,null,null,null,"28012-L-White","Large","Large"]
[]
[]
[null,null,null,null,null,null,"SWITCH",{"$f":"_xlfn.FORMULATEXT(H13)","v":"=SWITCH(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1),\"XS\",\"Extra Small\",\"S\",\"Small\",\"M\",\"Medium\",\"L\",\"Large\",\"XL\",\"Extra Large\",\"Not Specified\")"}]
[]
[null,null,null,null,null,null,"Alternative",{"$f":"_xlfn.FORMULATEXT(I13)","v":"=IF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"XS\",\"Extra Small\",\nIF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"S\",\"Small\",\nIF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"M\",\"Medium\",\nIF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"L\",\"Large\",\nIF(MID($G13,SEARCH(\"-\",$G13)+1,SEARCH(\"-\",$G13,(SEARCH(\"-\",$G13)+1))-SEARCH(\"-\",$G13)-1)=\"XL\",\"Extra Large\",\"Not Specified\")))))"}]

{"sheet":"CONCAT Example","index":7,"n_rows":27,"n_cols":11,"rows_kept":27,"cut":false,"hidden":false,"merged":[[2,0,2,4],[21,6,21,8],[22,6,22,8],[23,6,23,8],[26,6,26,8],[25,6,25,8]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"CONCAT Example"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Let's Get Together"]
[]
[null,null,"CONCAT Examples"]
[]
[null,null,null,"Illustration"]
[]
[null,null,null,null,null,null,"Words"]
[null,null,null,null,null,null,"Red"]
[null,null,null,null,null,null,"Orange"]
[null,null,null,null,null,null,"Yellow"]
[null,null,null,null,null,null,"Green"]
[null,null,null,null,null,null,"Blue"]
[null,null,null,null,null,null,"Indigo"]
[null,null,null,null,null,null,"Violet"]
[]
[]
[null,null,null,null,null,"CONCATENATE",{"$f":"CONCATENATE(G13,\", \",G14,\", \",G15,\", \",G16,\", \",G17,\", \",G18,\", \",G19)","v":"Red, Orange, Yellow, Green, Blue, Indigo, Violet"},null,null,null,{"$f":"_xlfn.FORMULATEXT(G22)","v":"=CONCATENATE(G13,\", \",G14,\", \",G15,\", \",G16,\", \",G17,\", \",G18,\", \",G19)"}]
[null,null,null,null,null,"&",{"$f":"G13&\", \"&G14&\", \"&G15&\", \"&G16&\", \"&G17&\", \"&G18&\", \"&G19","v":"Red, Orange, Yellow, Green, Blue, Indigo, Violet"},null,null,null,{"$f":"_xlfn.FORMULATEXT(G23)","v":"=G13&\", \"&G14&\", \"&G15&\", \"&G16&\", \"&G17&\", \"&G18&\", \"&G19"}]
[null,null,null,null,null,"CONCAT",{"$f":"_xlfn.CONCAT(G13,\", \",G14,\", \",G15,\", \",G16,\", \",G17,\", \",G18,\", \",G19)","v":"Red, Orange, Yellow, Green, Blue, Indigo, Violet"},null,null,null,"=CONCAT(G13,\", \",G14,\", \",G15,\", \",G16,\", \",G17,\", \",G18,\", \",G19)"]
[]
[null,null,null,null,null,{"$f":"F22","v":"CONCATENATE"},{"$f":"CONCATENATE(G13:G19)","v":{"$error":"#VALUE!"}},null,null,null,{"$f":"_xlfn.FORMULATEXT(G26)","v":"=CONCATENATE(G13:G19)"}]
[null,null,null,null,null,{"$f":"F24","v":"CONCAT"},{"$f":"_xlfn.CONCAT(G13:G19)","v":"RedOrangeYellowGreenBlueIndigoViolet"},null,null,null,"=CONCAT(G13:G19)"]

{"sheet":"TEXTJOIN Example","index":8,"n_rows":55,"n_cols":11,"rows_kept":55,"cut":false,"hidden":false,"merged":[[53,6,53,8],[54,6,54,8],[2,0,2,4],[21,6,21,8],[22,6,22,8],[23,6,23,8],[26,6,26,8],[25,6,25,8],[50,6,50,8],[52,6,52,8],[27,6,27,8],[46,6,46,8],[47,6,47,8],[48,6,48,8],[51,6,51,8]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"TEXTJOIN Example"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Let's Get Together Again"]
[]
[null,null,"TEXTJOIN Examples"]
[]
[null,null,null,"Illustration"]
[]
[null,null,null,null,null,null,"Words"]
[null,null,null,null,null,null,"Red"]
[null,null,null,null,null,null,"Orange"]
[null,null,null,null,null,null,"Yellow"]
[null,null,null,null,null,null,"Green"]
[null,null,null,null,null,null,"Blue"]
[null,null,null,null,null,null,"Indigo"]
[null,null,null,null,null,null,"Violet"]
[]
[]
[null,null,null,null,null,"CONCATENATE",{"$f":"CONCATENATE(G13,\", \",G14,\", \",G15,\", \",G16,\", \",G17,\", \",G18,\", \",G19)","v":"Red, Orange, Yellow, Green, Blue, Indigo, Violet"},null,null,null,{"$f":"_xlfn.FORMULATEXT(G22)","v":"=CONCATENATE(G13,\", \",G14,\", \",G15,\", \",G16,\", \",G17,\", \",G18,\", \",G19)"}]
[null,null,null,null,null,"&",{"$f":"G13&\", \"&G14&\", \"&G15&\", \"&G16&\", \"&G17&\", \"&G18&\", \"&G19","v":"Red, Orange, Yellow, Green, Blue, Indigo, Violet"},null,null,null,{"$f":"_xlfn.FORMULATEXT(G23)","v":"=G13&\", \"&G14&\", \"&G15&\", \"&G16&\", \"&G17&\", \"&G18&\", \"&G19"}]
[null,null,null,null,null,"CONCAT",{"$f":"_xlfn.CONCAT(G13,\", \",G14,\", \",G15,\", \",G16,\", \",G17,\", \",G18,\", \",G19)","v":"Red, Orange, Yellow, Green, Blue, Indigo, Violet"},null,null,null,"=CONCAT(G13,\", \",G14,\", \",G15,\", \",G16,\", \",G17,\", \",G18,\", \",G19)"]
[]
[null,null,null,null,null,{"$f":"F22","v":"CONCATENATE"},{"$f":"CONCATENATE(G13:G19)","v":{"$error":"#VALUE!"}},null,null,null,{"$f":"_xlfn.FORMULATEXT(G26)","v":"=CONCATENATE(G13:G19)"}]
[null,null,null,null,null,{"$f":"F24","v":"CONCAT"},{"$f":"_xlfn.CONCAT(G13:G19)","v":"RedOrangeYellowGreenBlueIndigoViolet"},null,null,null,"=CONCAT(G13:G19)"]
[null,null,null,null,null,"TEXTJOIN",{"$f":"_xlfn.TEXTJOIN(\", \",TRUE,G13:G19)","v":"Red, Orange, Yellow, Green, Blue, Indigo, Violet"},null,null,null,{"$f":"_xlfn.FORMULATEXT(G28)","v":"=TEXTJOIN(\", \",TRUE,G13:G19)"}]
[]
[]
[null,{"$f":"MAX($B$5:$B30)+1","v":2},"Let's Get Together Again and Get Spaced Out"]
[]
[null,null,"TEXTJOIN Examples"]
[]
[null,null,null,"Illustration"]
[]
[null,null,null,null,null,null,"Words"]
[null,null,null,null,null,null,"Red"]
[null,null,null,null,null,null,"Orange"]
[]
[null,null,null,null,null,null,"Green"]
[null,null,null,null,null,null,"Blue"]
[]
[null,null,null,null,null,null,"Violet"]
[]
[]
[null,null,null,null,null,"CONCATENATE",{"$f":"CONCATENATE(G38,\", \",G39,\", \",G40,\", \",G41,\", \",G42,\", \",G43,\", \",G44)","v":"Red, Orange, , Green, Blue, , Violet"},null,null,null,{"$f":"_xlfn.FORMULATEXT(G47)","v":"=CONCATENATE(G38,\", \",G39,\", \",G40,\", \",G41,\", \",G42,\", \",G43,\", \",G44)"}]
[null,null,null,null,null,"&",{"$f":"G38&\", \"&G39&\", \"&G40&\", \"&G41&\", \"&G42&\", \"&G43&\", \"&G44","v":"Red, Orange, , Green, Blue, , Violet"},null,null,null,{"$f":"_xlfn.FORMULATEXT(G48)","v":"=G38&\", \"&G39&\", \"&G40&\", \"&G41&\", \"&G42&\", \"&G43&\", \"&G44"}]
[null,null,null,null,null,"CONCAT",{"$f":"_xlfn.CONCAT(G38,\", \",G39,\", \",G40,\", \",G41,\", \",G42,\", \",G43,\", \",G44)","v":"Red, Orange, , Green, Blue, , Violet"},null,null,null,"=CONCAT(G38,\", \",G39,\", \",G40,\", \",G41,\", \",G42,\", \",G43,\", \",G44)"]
[]
[null,null,null,null,null,{"$f":"F47","v":"CONCATENATE"},{"$f":"CONCATENATE(G38:G44)","v":{"$error":"#VALUE!"}},null,null,null,{"$f":"_xlfn.FORMULATEXT(G51)","v":"=CONCATENATE(G38:G44)"}]
[null,null,null,null,null,{"$f":"F49","v":"CONCAT"},{"$f":"_xlfn.CONCAT(G38:G44)","v":"RedOrangeGreenBlueViolet"},null,null,null,{"$f":"_xlfn.FORMULATEXT(G52)","v":"=CONCAT(G38:G44)"}]
[null,null,null,null,null,"TEXTJOIN",{"$f":"_xlfn.TEXTJOIN(\", \",TRUE,G38:G44)","v":"Red, Orange, Green, Blue, Violet"},null,null,null,"=TEXTJOIN(\", \",TRUE,G38:G44)"]
[null,null,null,null,null,"TEXTJOIN",{"$f":"_xlfn.TEXTJOIN(\", \",,G38:G44)","v":"Red, Orange, Green, Blue, Violet"},null,null,null,"=TEXTJOIN(\", \",,G38:G44)"]
[null,null,null,null,null,"TEXTJOIN",{"$f":"_xlfn.TEXTJOIN(\", \",FALSE,G38:G44)","v":"Red, Orange, , Green, Blue, , Violet"},null,null,null,"=TEXTJOIN(\", \",FALSE,G38:G44)"]

{"sheet":"MAXIFS Example","index":9,"n_rows":40,"n_cols":10,"rows_kept":40,"cut":false,"hidden":false,"merged":[[2,0,2,4]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"MAXIFS Example"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Take it to the MAX"]
[]
[null,null,"MAXIF Example"]
[]
[null,null,null,"Illustration"]
[]
[null,null,null,null,null,null,"Product","Sales Person","Business Unit","Amount"]
[null,null,null,null,null,null,"Guns","Al","North",100]
[null,null,null,null,null,null,"Drugs","Bri","North",200]
[null,null,null,null,null,null,"Drugs","Jo","South",300]
[null,null,null,null,null,null,"Guns","Jo","East",400]
[null,null,null,null,null,null,"Guns","Al","North",500]
[null,null,null,null,null,null,"Roses","Al","West",600]
[null,null,null,null,null,null,"Guns","Jo","East",700]
[null,null,null,null,null,null,"Guns","Jo","South",800]
[null,null,null,null,null,null,"Drugs","Al","North",900]
[null,null,null,null,null,null,"Drugs","Bri","North",1000]
[null,null,null,null,null,null,"Roses","Al","South",1100]
[null,null,null,null,null,null,"Roses","Bri","South",1200]
[null,null,null,null,null,null,"Roses","Jo","North",1300]
[null,null,null,null,null,null,"Guns","Bri","South",1400]
[null,null,null,null,null,null,"Roses","Bri","East",1500]
[null,null,null,null,null,null,"Drugs","Al","West",1600]
[null,null,null,null,null,null,"Drugs","Al","East",1700]
[null,null,null,null,null,null,"Guns","Bri","East",1800]
[null,null,null,null,null,null,"Roses","Bri","South",1900]
[]
[]
[null,null,null,null,null,null,{"$f":"G12","v":"Product"},"Guns"]
[null,null,null,null,null,null,{"$f":"H12","v":"Sales Person"},"Al"]
[null,null,null,null,null,null,{"$f":"I12","v":"Business Unit"},"North"]
[]
[null,null,null,null,null,null,{"$f":"J12","v":"Amount"},{"$f":"_xlfn.MAXIFS(J13:J31,G13:G31,H34,H13:H31,H35,I13:I31,H36)","v":500},null,{"$f":"_xlfn.FORMULATEXT(H38)","v":"=MAXIFS(J13:J31,G13:G31,H34,H13:H31,H35,I13:I31,H36)"}]
[]
[null,null,null,null,null,null,"Alternative",{"$f":"MAX(IF(G13:G31=H34,IF(H13:H31=H35,IF(I13:I31=H36,J13:J31))))","v":500},null,{"$f":"_xlfn.FORMULATEXT(H40)","v":"{=MAX(IF(G13:G31=H34,IF(H13:H31=H35,IF(I13:I31=H36,J13:J31))))}"}]

{"sheet":"MINIFS Example","index":10,"n_rows":40,"n_cols":10,"rows_kept":40,"cut":false,"hidden":false,"merged":[[2,0,2,4]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"MINIFS Example"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Take it to the MIN"]
[]
[null,null,"MINIF Example"]
[]
[null,null,null,"Illustration"]
[]
[null,null,null,null,null,null,"Product","Sales Person","Business Unit","Amount"]
[null,null,null,null,null,null,"Guns","Al","North",100]
[null,null,null,null,null,null,"Drugs","Bri","North",200]
[null,null,null,null,null,null,"Drugs","Jo","South",300]
[null,null,null,null,null,null,"Guns","Jo","East",400]
[null,null,null,null,null,null,"Guns","Al","North",500]
[null,null,null,null,null,null,"Roses","Al","West",600]
[null,null,null,null,null,null,"Guns","Jo","East",700]
[null,null,null,null,null,null,"Guns","Jo","South",800]
[null,null,null,null,null,null,"Drugs","Al","North",900]
[null,null,null,null,null,null,"Drugs","Bri","North",1000]
[null,null,null,null,null,null,"Roses","Al","South",1100]
[null,null,null,null,null,null,"Roses","Bri","South",1200]
[null,null,null,null,null,null,"Roses","Jo","North",1300]
[null,null,null,null,null,null,"Guns","Bri","South",1400]
[null,null,null,null,null,null,"Roses","Bri","East",1500]
[null,null,null,null,null,null,"Drugs","Al","West",1600]
[null,null,null,null,null,null,"Drugs","Al","East",1700]
[null,null,null,null,null,null,"Guns","Bri","East",1800]
[null,null,null,null,null,null,"Roses","Bri","South",1900]
[]
[]
[null,null,null,null,null,null,{"$f":"G12","v":"Product"},"Guns"]
[null,null,null,null,null,null,{"$f":"H12","v":"Sales Person"},"Al"]
[null,null,null,null,null,null,{"$f":"I12","v":"Business Unit"},"North"]
[]
[null,null,null,null,null,null,{"$f":"J12","v":"Amount"},{"$f":"_xlfn.MINIFS(J13:J31,G13:G31,H34,H13:H31,H35,I13:I31,H36)","v":100},null,{"$f":"_xlfn.FORMULATEXT(H38)","v":"=MINIFS(J13:J31,G13:G31,H34,H13:H31,H35,I13:I31,H36)"}]
[]
[null,null,null,null,null,null,"Alternative",{"$f":"MIN(IF(G13:G31=H34,IF(H13:H31=H35,IF(I13:I31=H36,J13:J31))))","v":100},null,{"$f":"_xlfn.FORMULATEXT(H40)","v":"{=MIN(IF(G13:G31=H34,IF(H13:H31=H35,IF(I13:I31=H36,J13:J31))))}"}]

{"sheet":"Error Checks","index":11,"n_rows":17,"n_cols":9,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,8,0,9],[2,0,2,4]]}
[{"$f":"IF(ISERROR(RIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1)))),\n\"\",\nRIGHT(CELL(\"filename\",A1),LEN(CELL(\"filename\",A1))-FIND(\"]\",CELL(\"filename\",A1))))","v":"Error Checks"}]
[{"$f":"Model_Name","v":"SP - New Excel 2016 Functions.xlsm"}]
["Navigator"]
[null,"Error Checks:",null,null,null,{"$f":"Overall_Error_Check","v":0}]
[]
[null,{"$f":"MAX($B$5:$B5)+1","v":1},"Error Checks"]
[]
[null,null,"Summary of Errors"]
[]
[null,null,null,"Assumptions"]
[]
[null,null,null,null,"No checks present"]
[]
[]
[]
[]
[null,null,null,null,{"$f":"C8","v":"Summary of Errors"},null,null,null,{"$f":"MIN(1,SUM(I11:I15))","v":0}]