{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Dataset 1","OFFSET Function","INDEX_MATCH Function","LARGE_COLUMN Function","COUNTIF Function","VBA Code","Last n Values","Dataset 2","3 Cells"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"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":"Dataset 1","index":0,"n_rows":10,"n_cols":9,"rows_kept":10,"cut":false,"hidden":false,"merged":[[3,2,3,8],[1,1,1,8]]}
[]
[null,"How to Sum of Last 5 Values in Excel"]
[]
[null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021]
[null,"AC",300,350,400,425,450,450,500]
[null,"TV",200,250,200,500,300,200,200]
[null,"Monitor",150,180,200,220,220,250,250]
[null,"Fan",70,80,100,120,120,100,110]
[null,"Speaker",50,50,55,60,70,70,80]

{"sheet":"OFFSET Function","index":1,"n_rows":11,"n_cols":25,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,16,1,24],[3,17,3,23],[4,16,5,16],[4,17,5,17],[4,18,5,18],[4,19,5,19],[4,20,5,20],[4,21,5,21],[4,22,5,22],[4,23,5,23],[4,24,5,24],[4,1,5,1],[1,1,1,9],[4,4,5,4],[4,3,5,3],[4,2,5,2],[3,2,3,8],[4,9,5,9],[4,8,5,8],[4,7,5,7],[4,6,5,6],[4,5,5,5]]}
[]
[null,"How to Sum of Last 5 Values in Excel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Practice Section"]
[]
[null,null,"Sales Per Item in Year",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values",null,null,null,null,null,null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[]
[null,"AC",300,350,400,425,450,450,500,{"$f":"SUM(OFFSET($C7,0,COUNT($C7:$I7)-1,,-5))","v":2225},null,null,null,null,null,null,"AC",300,350,400,425,450,450,500]
[null,"TV",200,250,200,500,300,200,200,{"$f":"SUM(OFFSET($C8,0,COUNT($C8:$I8)-1,,-5))","v":1400},null,null,null,null,null,null,"TV",200,250,200,500,300,200,200]
[null,"Monitor",150,180,200,220,220,250,250,1140,null,null,null,null,null,null,"Monitor",150,180,200,220,220,250,250]
[null,"Fan",70,80,100,120,120,100,110,550,null,null,null,null,null,null,"Fan",70,80,100,120,120,100,110]
[null,"Speaker",50,50,55,60,70,70,80,335,null,null,null,null,null,null,"Speaker",50,50,55,60,70,70,80]

{"sheet":"INDEX_MATCH Function","index":2,"n_rows":11,"n_cols":25,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,16,1,24],[3,17,3,23],[4,16,5,16],[4,17,5,17],[4,18,5,18],[4,19,5,19],[4,20,5,20],[4,21,5,21],[4,22,5,22],[4,23,5,23],[4,24,5,24],[4,2,5,2],[4,3,5,3],[4,4,5,4],[4,5,5,5],[1,1,1,9],[4,1,5,1],[4,6,5,6],[4,7,5,7],[4,8,5,8],[4,9,5,9],[3,2,3,8]]}
[]
[null,"Using INDEX-MATCH Function","Using INDEX-MATCH Function",null,null,null,null,null,null,null,null,null,null,null,null,null,"Practice Section"]
[]
[null,null,"Sales Per Item in Year",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values",null,null,null,null,null,null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[]
[null,"AC",300,350,400,425,450,450,500,{"$f":"SUM(INDEX($C7:$I7,MATCH(9.9E+307,$C7:INDEX($C7:$I7,MATCH(9.9E+307,$C7:$I7)-4))):$I7)","v":2225},null,null,null,null,null,null,"AC",300,350,400,425,450,450,500]
[null,"TV",200,250,200,500,300,200,200,{"$f":"SUM(INDEX($C8:$I8,MATCH(9.9E+307,$C8:INDEX($C8:$I8,MATCH(9.9E+307,$C8:$I8)-4))):$I8)","v":1400},null,null,null,null,null,null,"TV",200,250,200,500,300,200,200]
[null,"Monitor",150,180,200,220,220,250,250,{"$f":"SUM(INDEX($C9:$I9,MATCH(9.9E+307,$C9:INDEX($C9:$I9,MATCH(9.9E+307,$C9:$I9)-4))):$I9)","v":1140},null,null,null,null,null,null,"Monitor",150,180,200,220,220,250,250]
[null,"Fan",70,80,100,120,120,100,110,{"$f":"SUM(INDEX($C10:$I10,MATCH(9.9E+307,$C10:INDEX($C10:$I10,MATCH(9.9E+307,$C10:$I10)-4))):$I10)","v":550},null,null,null,null,null,null,"Fan",70,80,100,120,120,100,110]
[null,"Speaker",50,50,55,60,70,70,80,{"$f":"SUM(INDEX($C11:$I11,MATCH(9.9E+307,$C11:INDEX($C11:$I11,MATCH(9.9E+307,$C11:$I11)-4))):$I11)","v":335},null,null,null,null,null,null,"Speaker",50,50,55,60,70,70,80]

{"sheet":"LARGE_COLUMN Function","index":3,"n_rows":11,"n_cols":25,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,16,1,24],[3,17,3,23],[4,16,5,16],[4,17,5,17],[4,18,5,18],[4,19,5,19],[4,20,5,20],[4,21,5,21],[4,22,5,22],[4,23,5,23],[4,24,5,24],[4,2,5,2],[4,3,5,3],[4,4,5,4],[4,5,5,5],[1,1,1,9],[4,1,5,1],[4,6,5,6],[4,7,5,7],[4,8,5,8],[4,9,5,9],[3,2,3,8]]}
[]
[null,"Using LARGE & COLUMN Functions","Using LARGE & COLUMN Functions",null,null,null,null,null,null,null,null,null,null,null,null,null,"Practice Section"]
[]
[null,null,"Sales Per Item in Year",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values",null,null,null,null,null,null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[]
[null,"AC",300,350,400,425,450,450,500,{"$f":"SUM((COLUMN($C7:$I7)>=LARGE(COLUMN($C7:$I7)*(N(+$C7:$I7)>0),5))*N(+$C7:$I7))","v":2225},null,null,null,null,null,null,"AC",300,350,400,425,450,450,500]
[null,"TV",200,250,200,500,300,200,200,{"$f":"SUM((COLUMN($C8:$I8)>=LARGE(COLUMN($C8:$I8)*(N(+$C8:$I8)>0),5))*N(+$C8:$I8))","v":1400},null,null,null,null,null,null,"TV",200,250,200,500,300,200,200]
[null,"Monitor",150,180,200,220,220,250,250,{"$f":"SUM((COLUMN($C9:$I9)>=LARGE(COLUMN($C9:$I9)*(N(+$C9:$I9)>0),5))*N(+$C9:$I9))","v":1140},null,null,null,null,null,null,"Monitor",150,180,200,220,220,250,250]
[null,"Fan",70,80,100,120,120,100,110,{"$f":"SUM((COLUMN($C10:$I10)>=LARGE(COLUMN($C10:$I10)*(N(+$C10:$I10)>0),5))*N(+$C10:$I10))","v":550},null,null,null,null,null,null,"Fan",70,80,100,120,120,100,110]
[null,"Speaker",50,50,55,60,70,70,80,{"$f":"SUM((COLUMN($C11:$I11)>=LARGE(COLUMN($C11:$I11)*(N(+$C11:$I11)>0),5))*N(+$C11:$I11))","v":335},null,null,null,null,null,null,"Speaker",50,50,55,60,70,70,80]

{"sheet":"COUNTIF Function","index":4,"n_rows":14,"n_cols":25,"rows_kept":14,"cut":false,"hidden":false,"merged":[[1,16,1,24],[3,17,3,23],[4,16,5,16],[4,17,5,17],[4,18,5,18],[4,19,5,19],[4,20,5,20],[4,21,5,21],[4,22,5,22],[4,23,5,23],[4,24,5,24],[13,1,13,9],[4,2,5,2],[4,3,5,3],[4,4,5,4],[4,5,5,5],[1,1,1,9],[4,1,5,1],[4,6,5,6],[4,7,5,7],[4,8,5,8],[4,9,5,9],[3,2,3,8]]}
[]
[null,"Using COUNTIF Function",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Practice Section"]
[]
[null,null,"Sales Per Item in Year",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values",null,null,null,null,null,null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[]
[null,"AC",300,350,400,425,450,450,500,{"$f":"IF(COUNTIF($C7:$I7,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":2225},null,null,null,null,null,null,"AC",300,350,400,425,450,450,500]
[null,"TV",200,250,200,500,300,200,200,{"$f":"IF(COUNTIF($C8:$I8,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C8,0,LARGE(IF($C8:$I8>0,COLUMN($C8:$I8)-COLUMN($C8)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":1400},null,null,null,null,null,null,"TV",200,250,200,500,300,200,200]
[null,"Monitor",150,180,200,220,220,250,250,{"$f":"IF(COUNTIF($C9:$I9,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C9,0,LARGE(IF($C9:$I9>0,COLUMN($C9:$I9)-COLUMN($C9)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":1140},null,null,null,null,null,null,"Monitor",150,180,200,220,220,250,250]
[null,"Fan",70,80,100,120,120,100,110,{"$f":"IF(COUNTIF($C10:$I10,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C10,0,LARGE(IF($C10:$I10>0,COLUMN($C10:$I10)-COLUMN($C10)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":550},null,null,null,null,null,null,"Fan",70,80,100,120,120,100,110]
[null,"Speaker",50,50,55,60,70,70,80,{"$f":"IF(COUNTIF($C11:$I11,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C11,0,LARGE(IF($C11:$I11>0,COLUMN($C11:$I11)-COLUMN($C11)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")","v":335},null,null,null,null,null,null,"Speaker",50,50,55,60,70,70,80]
[]
[null,null,null,null,null,"Formula"]
[null,{"$f":"_xlfn.FORMULATEXT(J7)","v":"=IF(COUNTIF($C7:$I7,\">0\")>=5,SUM(LARGE(SUBTOTAL(9,OFFSET($C7,0,LARGE(IF($C7:$I7>0,COLUMN($C7:$I7)-COLUMN($C7)+1)-1,{1,2,3,4,5}))),{1,2,3,4,5})),\"< 5\nValues\")"}]

{"sheet":"VBA Code","index":5,"n_rows":11,"n_cols":25,"rows_kept":11,"cut":false,"hidden":false,"merged":[[1,1,1,9],[4,1,5,1],[1,16,1,24],[3,17,3,23],[4,16,5,16],[4,17,5,17],[4,18,5,18],[4,19,5,19],[4,20,5,20],[4,21,5,21],[4,22,5,22],[4,23,5,23],[4,24,5,24],[3,2,3,8],[4,9,5,9],[4,8,5,8],[4,2,5,2],[4,7,5,7],[4,6,5,6],[4,5,5,5],[4,4,5,4],[4,3,5,3]]}
[]
[null,"Sum Last 5 Values Using VBA Code",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Practice Section"]
[]
[null,null,"Sales Per Item in Year",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Output",null,null,null,null,null,null,"Items",2015,2016,2017,2018,2019,2020,2021,"Output"]
[]
[null,"AC",300,350,400,425,450,450,500,{"$f":"sum_last_5(C7:I7)","v":2225},null,null,null,null,null,null,"AC",300,350,400,425,450,450,500]
[null,"TV",200,250,200,500,300,200,200,{"$f":"sum_last_5(C8:I8)","v":1400},null,null,null,null,null,null,"TV",200,250,200,500,300,200,200]
[null,"Monitor",150,180,200,220,220,250,250,{"$f":"sum_last_5(C9:I9)","v":1140},null,null,null,null,null,null,"Monitor",150,180,200,220,220,250,250]
[null,"Fan",70,80,100,120,120,100,110,{"$f":"sum_last_5(C10:I10)","v":550},null,null,null,null,null,null,"Fan",70,80,100,120,120,100,110]
[null,"Speaker",50,50,55,60,70,70,80,{"$f":"sum_last_5(C11:I11)","v":335},null,null,null,null,null,null,"Speaker",50,50,55,60,70,70,80]

{"sheet":"Last n Values","index":6,"n_rows":13,"n_cols":25,"rows_kept":13,"cut":false,"hidden":false,"merged":[[1,16,1,24],[3,17,3,23],[4,16,5,16],[4,17,5,17],[4,18,5,18],[4,19,5,19],[4,20,5,20],[4,21,5,21],[4,22,5,22],[4,23,5,23],[4,24,5,24],[3,2,3,8],[4,2,5,2],[4,3,5,3],[4,4,5,4],[1,1,1,9],[4,1,5,1],[4,9,5,9],[4,8,5,8],[4,7,5,7],[4,6,5,6],[4,5,5,5]]}
[]
[null,"Average of Last n Values","Using COUNTIF Function",null,null,null,null,null,null,null,null,null,null,null,null,null,"Practice Section"]
[]
[null,null,"Sales Per Item in Year",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Sales Per Item in Year"]
[null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last n Values",null,null,null,null,null,null,"Items",2015,2016,2017,2018,2019,2020,2021,"Sum of Last 5 Values"]
[]
[null,"AC",300,350,400,425,450,450,500,{"$f":"AVERAGE(OFFSET(C7,0,COUNT(C7:I7)-1,,-$C$13))","v":456.25},null,null,null,null,null,null,"AC",300,350,400,425,450,450,500]
[null,"TV",200,250,200,500,300,200,200,{"$f":"AVERAGE(OFFSET(C8,0,COUNT(C8:I8)-1,,-$C$13))","v":300},null,null,null,null,null,null,"TV",200,250,200,500,300,200,200]
[null,"Monitor",150,180,200,220,220,250,250,235,null,null,null,null,null,null,"Monitor",150,180,200,220,220,250,250]
[null,"Fan",70,80,100,120,120,100,110,112.5,null,null,null,null,null,null,"Fan",70,80,100,120,120,100,110]
[null,"Speaker",50,50,55,60,70,70,80,70,null,null,null,null,null,null,"Speaker",50,50,55,60,70,70,80]
[]
[null,"n Value",4]

{"sheet":"Dataset 2","index":7,"n_rows":23,"n_cols":8,"rows_kept":23,"cut":false,"hidden":false,"merged":[[1,1,1,7],[3,2,3,7]]}
[]
[null,"Calculating Sum in Every 3 Cells"]
[]
[null,null,"Items"]
[null,"Months","AC","TV","Monitor","Fan","Speaker","Monthly Sales"]
[null,"January",1140,1155,1034,980,609,{"$f":"SUM(C6:G6)","v":4918}]
[null,"February",753,1191,909,1167,610,{"$f":"SUM(C7:G7)","v":4630}]
[null,"March",630,949,690,803,862,3934]
[null,"April",548,481,1087,1119,575,3810]
[null,"May",476,899,402,451,533,2761]
[null,"June",745,1002,705,881,1094,4427]
[null,"July",524,909,673,536,507,3149]
[null,"August",952,456,1132,633,1155,4328]
[null,"September",709,401,909,952,808,3779]
[null,"October",903,963,467,487,1046,3866]
[null,"November",437,935,995,1152,1174,4693]
[null,"December",671,555,1027,451,716,3420]
[]
[null,"Quarter","Sales"]
[null,"Q1"]
[null,"Q2"]
[null,"Q3"]
[null,"Q4"]

{"sheet":"3 Cells","index":8,"n_rows":23,"n_cols":21,"rows_kept":23,"cut":false,"hidden":false,"merged":[[3,2,3,7],[1,1,1,7],[1,14,1,20],[3,15,3,20]]}
[]
[null,"Calculating Sum in Every 3 Cells",null,null,null,null,null,null,null,null,null,null,null,null,"Practice Section"]
[]
[null,null,"Items",null,null,null,null,null,null,null,null,null,null,null,null,"Items"]
[null,"Months","AC","TV","Monitor","Fan","Speaker","Monthly Sales",null,null,null,null,null,null,"Months","AC","TV","Monitor","Fan","Speaker","Monthly Sales"]
[null,"January",1140,1155,1034,980,609,{"$f":"SUM(C6:G6)","v":4918},null,null,null,null,null,null,"January",1140,1155,1034,980,609,{"$f":"SUM(P6:T6)","v":4918}]
[null,"February",753,1191,909,1167,610,{"$f":"SUM(C7:G7)","v":4630},null,null,null,null,null,null,"February",753,1191,909,1167,610,{"$f":"SUM(P7:T7)","v":4630}]
[null,"March",630,949,690,803,862,3934,null,null,null,null,null,null,"March",630,949,690,803,862,3934]
[null,"April",548,481,1087,1119,575,3810,null,null,null,null,null,null,"April",548,481,1087,1119,575,3810]
[null,"May",476,899,402,451,533,2761,null,null,null,null,null,null,"May",476,899,402,451,533,2761]
[null,"June",745,1002,705,881,1094,4427,null,null,null,null,null,null,"June",745,1002,705,881,1094,4427]
[null,"July",524,909,673,536,507,3149,null,null,null,null,null,null,"July",524,909,673,536,507,3149]
[null,"August",952,456,1132,633,1155,4328,null,null,null,null,null,null,"August",952,456,1132,633,1155,4328]
[null,"September",709,401,909,952,808,3779,null,null,null,null,null,null,"September",709,401,909,952,808,3779]
[null,"October",903,963,467,487,1046,3866,null,null,null,null,null,null,"October",903,963,467,487,1046,3866]
[null,"November",437,935,995,1152,1174,4693,null,null,null,null,null,null,"November",437,935,995,1152,1174,4693]
[null,"December",671,555,1027,451,716,3420,null,null,null,null,null,null,"December",671,555,1027,451,716,3420]
[]
[null,"Quarter","Sales",null,null,null,null,null,null,null,null,null,null,null,"Quarter","Sales"]
[null,"Q1",{"$f":"SUM(OFFSET($H$6,(ROW()-ROW($C$20))*3,0,3,1))","v":13482},null,null,null,null,null,null,null,null,null,null,null,"Q1"]
[null,"Q2",{"$f":"SUM(OFFSET($H$6,(ROW()-ROW($C$20))*3,0,3,1))","v":10998},null,null,null,null,null,null,null,null,null,null,null,"Q2"]
[null,"Q3",11256,null,null,null,null,null,null,null,null,null,null,null,"Q3"]
[null,"Q4",11979,null,null,null,null,null,null,null,null,null,null,null,"Q4"]