{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Podcast2316","LambdaDevelopment","PT"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":108,"comments":0,"pivot_tables":1},"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":"Podcast2316","index":0,"n_rows":46,"n_cols":29,"rows_kept":46,"cut":false,"hidden":false,"merged":[[4,3,4,6]]}
[]
[]
[]
[null,"This is what I have"]
[null,"Category",null,"Dept. Total",null,null,null,null,"Daisy",null,null,null,null,"Maisie",null,null,null,null,"Fifi",null,null,null,null,"Lulu"]
[null,"Description & Charge rate",null,1,2,3,4,null,"Q1","Q2","Q3","Q4",null,"Q1","Q2","Q3","Q4",null,"Q1","Q2","Q3","Q4",null,"Q1","Q2","Q3","Q4",null,"Validation"]
[null,"Administrative",null,{"$f":" ROWTOTAL(DATAλ(1, {1,2,3,4}, row, _xlfn.SINGLE(qtr.idx)))","v":37.5},{"$f":" ROWTOTAL(DATAλ(1, {1,2,3,4}, row, _xlfn.SINGLE(qtr.idx)))","v":25.5},{"$f":" ROWTOTAL(DATAλ(1, {1,2,3,4}, row, _xlfn.SINGLE(qtr.idx)))","v":1},{"$f":" ROWTOTAL(DATAλ(1, {1,2,3,4}, row, _xlfn.SINGLE(qtr.idx)))","v":0},null,14,4,0,0,null,0,4,0,0,null,0,0,0,0,null,23.5,17.5,1,0,null,1]
[null,95,null,3562.5,2422.5,95,0,null,{"$f":" IF(above<>\"\", above*_xlfn.SINGLE(catRate), \"\")","v":1330},380,0,0,"",0,380,0,0,"",0,0,0,0,"",2232.5,1662.5,95,0,null,2]
[null,"Holiday",null,0,48,8,32,null,0,16,8,32,null,0,8,0,0,null,0,8,0,0,null,0,16,0,0]
[null,0,null,0,0,0,0,null,{"$f":" IF(above<>\"\", above*_xlfn.SINGLE(catRate), \"\")","v":0},0,0,0,"",0,0,0,0,"",0,0,0,0,"",0,0,0,0]
[null,"PTO/LOA/Jury Duty",null,176,156,52,0,null,0,24,0,0,null,0,0,12,0,null,152,32,0,0,null,24,100,40,0]
[null,45,null,7920,7020,2340,0,null,{"$f":" IF(above<>\"\", above*_xlfn.SINGLE(catRate), \"\")","v":0},1080,0,0,"",0,0,540,0,"",6840,1440,0,0,"",1080,4500,1800,0]
[null,"Client A",null,44,58.5,1,0,null,16.5,4.5,0,0,null,0,17,1,0,null,16,29,0,0,null,11.5,8,0,0]
[null,95,null,4180,5557.5,95,0,null,{"$f":" IF(above<>\"\", above*_xlfn.SINGLE(catRate), \"\")","v":1567.5},427.5,0,0,"",0,1615,95,0,"",1520,2755,0,0,"",1092.5,760,0,0]
[null,"Client B",null,60,53,0.5,0,null,53,29.5,0,0,null,0,15,0,0,null,0,8,0,0,null,7,0.5,0.5,0]
[null,95,null,5700,5035,47.5,0,null,{"$f":" IF(above<>\"\", above*_xlfn.SINGLE(catRate), \"\")","v":5035},2802.5,0,0,"",0,1425,0,0,"",0,760,0,0,"",665,47.5,47.5,0]
[null,"Client C",null,122,160.5,10,0,null,60.5,2.5,0,0,null,0,83,10,0,null,48,66,0,0,null,13.5,9,0,0]
[null,105,null,12810,16852.5,1050,0,null,{"$f":" IF(above<>\"\", above*_xlfn.SINGLE(catRate), \"\")","v":6352.5},262.5,0,0,"",0,8715,1050,0,"",5040,6930,0,0,"",1417.5,945,0,0]
[]
[]
[null,"What I want it to look like",null,null,null,null,null,1,null,"Illustration of DATAλ-function returning a single value"]
[null,"Category description","Employee name",{"$f":" quarter","v":"Q1"},"Q2","Q3","Q4","Total \n(sum)",null,null,null,null,null,"Returns",{"$f":"DATAλ(client, therapist, units, qtr)","v":15},null,null,null,null,null,null,null,null,{"$f":"HDRλ(,4,1,1)&CHAR(10)&\"Q1\"","v":"Lulu\nQ1"},"Q2","Q3","Q4"]
[null,{"$f":" CatRateλ(cat.idx,1,1,1)","v":"Administrative"},{"$f":"HDRλ(1, emp.idx, 1, 1)","v":"Daisy"},{"$f":" DATAλ(cat.idx, emp.idx, units, qtr.idx)","v":14},4,0,0,{"$f":" ROWTOTAL( DATAλ(cat.idx, emp.idx, units, qtr.idx) )","v":18},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":" HOURSλ({4;5;6}, 4, {1,2,3,4})","v":11.5},8,0,0]
[null,"Holiday","Daisy",0,16,8,32,56,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,7,0.5,0.5,0]
[null,"PTO/LOA/Jury Duty","Daisy",0,24,0,0,24,null,null,"Therapist",2,null,null,null,null,null,null,null,null,null,null,null,13.5,9,0,0]
[null,"Client A","Daisy",16.5,4.5,0,0,21,null,null,{"$f":" HDRλ(1,L25,1,1)","v":"Maisie"}]
[null,"Client B","Daisy",53,29.5,0,0,82.5]
[null,"Client C","Daisy",60.5,2.5,0,0,63,null,null,2]
[null,"Administrative","Maisie",0,4,0,0,4]
[null,"Holiday","Maisie",0,8,0,0,8,null,null,{"$f":"CatRateλ(L30, 1, 1, 1)","v":"Client B"},5]
[null,"PTO/LOA/Jury Duty","Maisie",0,0,12,0,12]
[null,"Client A","Maisie",0,17,1,0,18]
[null,"Client B","Maisie",0,15,0,0,15]
[null,"Client C","Maisie",0,83,10,0,93]
[null,"Administrative","Fifi",0,0,0,0,0]
[null,"Holiday","Fifi",0,8,0,0,8]
[null,"PTO/LOA/Jury Duty","Fifi",152,32,0,0,184]
[null,"Client A","Fifi",16,29,0,0,45]
[null,"Client B","Fifi",0,8,0,0,8]
[null,"Client C","Fifi",48,66,0,0,114]
[null,"Administrative","Lulu",23.5,17.5,1,0,42]
[null,"Holiday","Lulu",0,16,0,0,16]
[null,"PTO/LOA/Jury Duty","Lulu",24,100,40,0,164]
[null,"Client A","Lulu",11.5,8,0,0,19.5]
[null,"Client B","Lulu",7,0.5,0.5,0,8]
[null,"Client C","Lulu",13.5,9,0,0,22.5]

{"sheet":"LambdaDevelopment","index":1,"n_rows":54,"n_cols":15,"rows_kept":54,"cut":false,"hidden":false,"merged":[]}
[]
[null,"λ-function development sheet"]
[]
[null,null,"1. Declare name","2. Add comment","4. Check syntax                                     5. Upload to Name","3. Develop named formula or Lambda",null,null,"6. Test functionality"]
[null,null,null,null,"(with formula active)"]
[null,null,"Name",null,"FormulaText","Formula Input"]
[null,null,null,null,{"$f":" IF(_xlfn.ISFORMULA(LambdaFormula),\n        _xlfn.FORMULATEXT(LambdaFormula), \"\")","v":""}]
[null,null,null,null,""]
[null,"Nested \nλ-function","ArrayRef","Utility function used to \ndefine multidimensional \narray index λ-functions\nArrayRef( RangeRef, \nvStride, hStride )(indices)","= LAMBDA(A,n₁,n₂,\n        LAMBDA(i₁,i₂,j₁,j₂,\n            INDEX(A, (i₁-1)*n₁+j₁, (i₂-1)*n₂+j₂)\n        )\n    )(\n      recurrentData,2,5\n   )(1,1,1,1)",{"$f":" _xlfn.LAMBDA(_xlpm.A,_xlpm.n₁,_xlpm.n₂,\n        _xlfn.LAMBDA(_xlpm.i₁,_xlpm.i₂,_xlpm.j₁,_xlpm.j₂,\n            INDEX(_xlpm.A, (_xlpm.i₁-1)*_xlpm.n₁+_xlpm.j₁, (_xlpm.i₂-1)*_xlpm.n₂+_xlpm.j₂)\n        )\n    )(\n      recurrentData,2,5\n   )(1,1,1,1)","v":14}]
[null,null,null,null,""]
[null,"Derived\nλ-function","DATAλ","Returns element of 4D array\nDATAλ(cat, emp, hrs$, qtr)","= ArrayRef(\n      recurrentData,2,5\n   )({1;2},1,1,{1,2})",{"$f":" ArrayRef(\n      recurrentData,2,5\n   )({1;2},1,1,{1,2})","v":14},4]
[null,null,null,null,"",0,16]
[null,"Test",null,"Worksheet formula","=DATAλ({1;2},1,1,{1,2})",{"$f":"DATAλ({1;2},1,1,{1,2})","v":14},4]
[null,null,null,null,"",0,16]
[null,null,null,null,""]
[null,"λ-function","HDRλ","Returns element of header \nDATAλ( 1, emp, hrs$, qtr)","= ArrayRef(recurrentHdr, ,5)( ,{1,2,3,4},1,1)",{"$f":" ArrayRef(recurrentHdr, ,5)( ,{1,2,3,4},1,1)","v":"Daisy"},"Maisie","Fifi","Lulu"]
[null,"Test",null,"Worksheet formula","= HDRλ( ,{1,2,3,4},1,1)",{"$f":" HDRλ( ,{1,2,3,4},1,1)","v":"Daisy"},"Maisie","Fifi","Lulu"]
[null,null,null,null,""]
[null,"λ-function","CatRateλ","Returns row header \nCatRateλ(vBlk,1,row,1)","= ArrayRef(catRate,2,)({1;2},,1,1)",{"$f":" ArrayRef(catRate,2,)({1;2},,1,1)","v":"Administrative"}]
[null,null,null,null,"","Holiday"]
[null,"Test",null,null,"= CatRateλ({1,2},,1,1)",{"$f":" CatRateλ({1,2},,1,1)","v":"Administrative"},"Holiday"]
[null,null,null,null,"",null,null,null,null,null,null,"Unichar","u̲","v̲","₀"]
[null,"λ-function","COLUMNTOTAL","Calculates column totals\nCOLUMNTOTAL(array)","= LAMBDA(A,\n      LET(\n         n,  ROWS(A),\n         A₀, IF(ISNUMBER(A), A, 0),\n         u̲,  SEQUENCE(1,n,1,0),\n         MMULT(u̲, A₀)\n      )\n   )({1,2;3,4})",{"$f":" _xlfn.LAMBDA(_xlpm.A,\n      _xlfn.LET(\n         _xlpm.n,  ROWS(_xlpm.A),\n         _xlpm.A₀, IF(ISNUMBER(_xlpm.A), _xlpm.A, 0),\n         _xlpm.u̲,  _xlfn.SEQUENCE(1,_xlpm.n,1,0),\n         MMULT(_xlpm.u̲, _xlpm.A₀)\n      )\n   )({1,2;3,4})","v":4},6]
[null,null,null,null,""]
[null,null,null,null,"=COLUMNTOTAL( {1,2;3,4} )",{"$f":"COLUMNTOTAL( {1,2;3,4} )","v":4},6]
[null,null,null,null,""]
[null,null,null,null,""]
[null,"λ-function","ROWTOTAL","Calculates row totals\nROWTOTAL(array)","= LAMBDA(A,\n      LET(\n         n, COLUMNS(A),\n         A₀, IF(ISNUMBER(A), A, 0),\n         v̲, SEQUENCE(n,1,1,0),\n         MMULT(A₀, v̲)\n      )\n   )({1,2;3,4})",{"$f":" _xlfn.LAMBDA(_xlpm.A,\n      _xlfn.LET(\n         _xlpm.n, COLUMNS(_xlpm.A),\n         _xlpm.A₀, IF(ISNUMBER(_xlpm.A), _xlpm.A, 0),\n         _xlpm.v̲, _xlfn.SEQUENCE(_xlpm.n,1,1,0),\n         MMULT(_xlpm.A₀, _xlpm.v̲)\n      )\n   )({1,2;3,4})","v":3}]
[null,null,null,null,"",7]
[null,null,null,null,""]
[null,null,null,null,"= ROWTOTAL({1,2;3,4})",{"$f":" ROWTOTAL({1,2;3,4})","v":3}]
[null,null,null,null,"",7]
[null,"Specialisation\nof DATAλ","HOURSλ","HOURSλ(cat,emp,qtr)","= LAMBDA(cat,emp,qtr,\n         DATAλ(cat,emp,1,qtr)\n   )(4,1,2)",{"$f":" _xlfn.LAMBDA(_xlpm.cat,_xlpm.emp,_xlpm.qtr,\n         DATAλ(_xlpm.cat,_xlpm.emp,1,_xlpm.qtr)\n   )(4,1,2)","v":4.5}]
[null,null,null,null,"= HOURSλ({4;5;6},3,2)",{"$f":" HOURSλ({4;5;6},3,2)","v":29}]
[null,null,null,null,"",8]
[null,null,null,null,"",66]
[null,null,null,null,""]
[null,"Specialisation\nof DATAλ","CHARGESλ","CHARGESλ(cat,emp,qtr)","= LAMBDA(cat,emp,qtr,\n         DATAλ(cat,emp,2,qtr)\n   )(4,1,2)",{"$f":" _xlfn.LAMBDA(_xlpm.cat,_xlpm.emp,_xlpm.qtr,\n         DATAλ(_xlpm.cat,_xlpm.emp,2,_xlpm.qtr)\n   )(4,1,2)","v":427.5}]
[null,"Test",null,null,"= CHARGESλ({4;5;6},3,2)",{"$f":" CHARGESλ({4;5;6},3,2)","v":2755}]
[null,null,null,null,"",760]
[null,null,null,null,"",6930]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]
[null,null,null,null,""]

{"sheet":"PT","index":2,"n_rows":44,"n_cols":15,"rows_kept":44,"cut":false,"hidden":false,"merged":[]}
["Record","Quarter","Category","EmpName","Hrs","USD",null,null,"Sum of Hrs",null,"Quarter"]
[1,"Q1","Administrative","Daisy",14,1330,null,null,"EmpName","Category","Q1","Q2","Q3","Q4","Grand Total"]
[4,"Q1","Client A","Daisy",16.5,1567.5,null,null,"Daisy","Administrative",14,4,null,null,18]
[5,"Q1","Client B","Daisy",53,5035,null,null,null,"Client A",16.5,4.5,null,null,21]
[6,"Q1","Client C","Daisy",60.5,6352.5,null,null,null,"Client B",53,29.5,null,null,82.5]
[15,"Q1","PTO/LOA/Jury Duty","Fifi",152,6840,null,null,null,"Client C",60.5,2.5,null,null,63]
[16,"Q1","Client A","Fifi",16,1520,null,null,null,"Holiday",null,16,8,32,56]
[18,"Q1","Client C","Fifi",48,5040,null,null,null,"PTO/LOA/Jury Duty",null,24,null,null,24]
[19,"Q1","Administrative","Lulu",23.5,2232.5,null,null,"Fifi","Client A",16,29,null,null,45]
[21,"Q1","PTO/LOA/Jury Duty","Lulu",24,1080,null,null,null,"Client B",null,8,null,null,8]
[22,"Q1","Client A","Lulu",11.5,1092.5,null,null,null,"Client C",48,66,null,null,114]
[23,"Q1","Client B","Lulu",7,665,null,null,null,"Holiday",null,8,null,null,8]
[24,"Q1","Client C","Lulu",13.5,1417.5,null,null,null,"PTO/LOA/Jury Duty",152,32,null,null,184]
[25,"Q2","Administrative","Daisy",4,380,null,null,"Lulu","Administrative",23.5,17.5,1,null,42]
[26,"Q2","Holiday","Daisy",16,0,null,null,null,"Client A",11.5,8,null,null,19.5]
[27,"Q2","PTO/LOA/Jury Duty","Daisy",24,1080,null,null,null,"Client B",7,1,0.5,null,8.5]
[28,"Q2","Client A","Daisy",4.5,427.5,null,null,null,"Client C",13.5,9,null,null,22.5]
[29,"Q2","Client B","Daisy",29.5,2802.5,null,null,null,"Holiday",null,16,null,null,16]
[30,"Q2","Client C","Daisy",2.5,262.5,null,null,null,"PTO/LOA/Jury Duty",24,100,40,null,164]
[31,"Q2","Administrative","Maisie",4,380,null,null,"Maisie","Administrative",null,4,null,null,4]
[32,"Q2","Holiday","Maisie",8,0,null,null,null,"Client A",null,null,1,null,1]
[34,"Q2","Client A","Maisy",17,1615,null,null,null,"Client B",null,15,null,null,15]
[35,"Q2","Client B","Maisie",15,1425,null,null,null,"Client C",null,83,10,null,93]
[36,"Q2","Client C","Maisie",83,8715,null,null,null,"Holiday",null,8,null,null,8]
[37,"Q2","Holiday","Fifi",8,0,null,null,null,"PTO/LOA/Jury Duty",null,null,12,null,12]
[38,"Q2","PTO/LOA/Jury Duty","Fifi",32,1440,null,null,"Maisy","Client A",null,17,null,null,17]
[39,"Q2","Client A","Fifi",29,2755,null,null,"Grand Total",null,439.5,502,72.5,32,1046]
[40,"Q2","Client B","Fifi",8,760]
[41,"Q2","Client C","Fifi",66,6930]
[42,"Q2","Administrative","Lulu",17.5,1662.5]
[43,"Q2","Holiday","Lulu",16,0]
[44,"Q2","PTO/LOA/Jury Duty","Lulu",100,4500]
[45,"Q2","Client A","Lulu",8,760]
[46,"Q2","Client B","Lulu",0.5,47.5]
[47,"Q2","Client C","Lulu",9,945]
[48,"Q3","Holiday","Daisy",8,0]
[49,"Q3","PTO/LOA/Jury Duty","Maisie",12,540]
[50,"Q3","Client A","Maisie",1,95]
[51,"Q2","Client B","Lulu",0.5,47.5]
[52,"Q3","Client C","Maisie",10,1050]
[53,"Q3","Administrative","Lulu",1,95]
[54,"Q3","PTO/LOA/Jury Duty","Lulu",40,1800]
[55,"Q3","Client B","Lulu",0.5,47.5]
[56,"Q4","Holiday","Daisy",32,0]