{"text_format":"3","workbook":"xlsx","n_sheets":17,"sheets":["Start","Car sales","2D constants","Horizontal & vertical constants","Constant in a formula","Named array constant","Quick sample dataset","Mathematical operators","Square and square root","Transpose","Reference existing arrays","Count characters","Nth values","Errors","Conditions","Differences between datasets","Learn More"],"not_rendered":{"images":21,"defined_names":14},"read":"whole","date_system":"1900"}

{"sheet":"Start","index":0,"n_rows":5,"n_cols":1,"rows_kept":5}
["Welcome\nInstructions for screen readers: After you go through the tip sheets in this tutorial, you'll learn how to quickly master dynamic array formulas. The instructions for each sheet start in cell A1, and each subsequent step is in cell A2, A3, and so on. \nThe instructions will indicate which cells to navigate to for use of a feature, or further reading.\nTo start, press CTRL+PAGE DOWN."]
["Dynamic array formulas"]
["For Office 365 subscribers only."]
["This tutorial will work best if you follow along with the Guidelines and examples of dynamic array formulas article."]
["Go back to top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN."]

{"sheet":"Car sales","index":1,"n_rows":20,"n_cols":8,"rows_kept":20}
["Single & multi-cell array formulas"]
["Use a combination of array formulas to calculate sub-totals and a grand total"]
["There are headers in cells D9:H9, data beginning in cell D10 going down to cell H19, with totals in row 20."]
["The array formula in cell H10 is =F10:F19*G10:G19, and it multiplies the number of cars sold by their unit price."]
["The array formula in cell H20 is =SUM(F10:F19*G10:G19), and gives a grand total of all cars sold."]
["IMPORTANT DETAIL\nIf you don't have Office 365, you'll need to first select the Total Sales range (cells H10:H19), then enter the formula as a legacy array with Ctrl+Shift+Enter. The same goes for the Total formula in cell H20."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[{"$nulls":3},"Sales Person","Car  Type","Number Sold","Unit Price","Total Sales"]
[{"$nulls":3},"Barnhill","Sedan",5,{"v":33000,"d":"33,000"},{"$f":"F10:F19*G10:G19","v":{"v":165000,"d":"165,000"}}]
[{"$nulls":4},"Coupe",4,{"v":37000,"d":"37,000"},{"v":148000,"d":"148,000"}]
[{"$nulls":3},"Ingle","Sedan",6,{"v":24000,"d":"24,000"},{"v":144000,"d":"144,000"}]
[{"$nulls":4},"Coupe",8,{"v":21000,"d":"21,000"},{"v":168000,"d":"168,000"}]
[{"$nulls":3},"Jordan","Sedan",3,{"v":29000,"d":"29,000"},{"v":87000,"d":"87,000"}]
[{"$nulls":4},"Coupe",1,{"v":31000,"d":"31,000"},{"v":31000,"d":"31,000"}]
[{"$nulls":3},"Pica","Sedan",9,{"v":24000,"d":"24,000"},{"v":216000,"d":"216,000"}]
[{"$nulls":4},"Coupe",5,{"v":37000,"d":"37,000"},{"v":185000,"d":"185,000"}]
[{"$nulls":3},"Sanchez","Sedan",6,{"v":33000,"d":"33,000"},{"v":198000,"d":"198,000"}]
[{"$nulls":4},"Coupe",8,{"v":31000,"d":"31,000"},{"v":248000,"d":"248,000"}]
[{"$nulls":3},"Grand Total",null,{"$f":"SUM(F10:F19)","v":55},null,{"$f":"SUM(F10:F19*G10:G19)","v":{"v":1590000,"d":"1,590,000"}}]

{"sheet":"2D constants","index":2,"n_rows":23,"n_cols":7,"rows_kept":23}
["2D constants"]
["2D constants let you create multiple row/column combinations"]
["There are three separate data sets in cells D9:G11, D15:G18, and D21:G23. The first two are dynamic arrays, and the last has been entered as a legacy array formula with Ctrl+Shift+Enter."]
["IMPORTANT DETAIL\nDynamic array functions like SEQUENCE are so much easier than manually writing constant strings!\n\nUse a comma (,) to create row separators, and a semi-colon (;) for column separators."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[{"$nulls":3},"Dynamic array >",null,{"$f":"_xlfn.FORMULATEXT(D9)","v":"=SEQUENCE(3,4)"}]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,4),1),\"mmmm\")","v":"January"},"February","March","April"]
[{"$nulls":3},{"$f":"_xlfn.SEQUENCE(3,4)","v":1},2,3,4]
[{"$nulls":3},5,6,7,8]
[{"$nulls":3},9,10,11,12]
[]
[{"$nulls":3},"Dynamic array >",null,{"$f":"_xlfn.FORMULATEXT(D15)","v":"={1,2,3,4;5,6,7,8;9,10,11,12}"}]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,4),1),\"mmmm\")","v":"January"},"February","March","April"]
[{"$nulls":3},{"$f":"{1,2,3,4;5,6,7,8;9,10,11,12}","v":1},2,3,4]
[{"$nulls":3},5,6,7,8]
[{"$nulls":3},9,10,11,12]
[]
[{"$nulls":3},"Legacy array >",null,{"$f":"_xlfn.FORMULATEXT(D21)","v":"{={1,2,3,4;5,6,7,8;9,10,11,12}}"}]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,4),1),\"mmmm\")","v":"January"},"February","March","April"]
[{"$nulls":3},{"$f":"{1,2,3,4;5,6,7,8;9,10,11,12}","v":1},2,3,4]
[{"$nulls":3},5,6,7,8]
[{"$nulls":3},9,10,11,12]

{"sheet":"Horizontal & vertical constants","index":3,"n_rows":22,"n_cols":10,"rows_kept":22}
["Horizontal & vertical constants"]
["There are several ways to create horizontal and vertical constants with arrays"]
["There is a range of horizontal constant in cells D10:H12, and a range of vertical constant in cells D16:F20."]
["GOOD TO KNOW\nWe're using the FORMULATEXT function to display the formulas we used."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[{"$nulls":3},"Horizontal constant from 1-5"]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,5),1),\"mmmm\")","v":"January"},"February","March","April","May",null,"Formulas"]
[{"$nulls":3},{"$f":"_xlfn.SEQUENCE(1,5)","v":1},2,3,4,5,null,{"$f":"_xlfn.FORMULATEXT(D10)","v":"=SEQUENCE(1,5)"}]
[{"$nulls":3},{"$f":"{1,2,3,4,5}","v":1},2,3,4,5,null,{"$f":"_xlfn.FORMULATEXT(D11)","v":"={1,2,3,4,5}"}]
[{"$nulls":3},{"$f":"{1,2,3,4,5}","v":1},2,3,4,5,null,{"$f":"_xlfn.FORMULATEXT(D12)","v":"{={1,2,3,4,5}}"}]
[]
[{"$nulls":3},"Vertical constant from 1-5"]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,3),1),\"mmmm\")","v":"January"},"February","March"]
[{"$nulls":3},{"$f":"_xlfn.SEQUENCE(5)","v":1},{"$f":"{1;2;3;4;5}","v":1},{"$f":"{1;2;3;4;5}","v":1}]
[{"$nulls":3},2,2,2]
[{"$nulls":3},3,3,3]
[{"$nulls":3},4,4,4]
[{"$nulls":3},5,5,5]
[]
[null,"Formulas (D16:F16)",null,{"$f":"_xlfn.FORMULATEXT(D16)","v":"=SEQUENCE(5)"},{"$f":"_xlfn.FORMULATEXT(E16)","v":"={1;2;3;4;5}"},{"$f":"_xlfn.FORMULATEXT(F16)","v":"{={1;2;3;4;5}}"}]

{"sheet":"Constant in a formula","index":4,"n_rows":21,"n_cols":8,"rows_kept":21}
["Array constant in a formula"]
["You can use array constants in your formulas, and mix them with ranges, or combine them"]
["There's a header row with January - May in cells D8:H8, then a dynamic array formula in cell D9 that has spilled to H9. The formula is: =SEQUENCE(1,5,3,1). There are several other array formula examples in cells E13, E15, E19 and E21."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,5),1),\"mmmm\")","v":"January"},"February","March","April","May"]
[{"$nulls":3},{"$f":"_xlfn.SEQUENCE(1,5,3,1)","v":3},4,5,6,7]
[]
[{"$nulls":3},"Combined range and array constants"]
[]
[{"$nulls":3},"Total",{"$f":"SUM(D9:H9*_xlfn.SEQUENCE(1,5))","v":85},{"$f":"_xlfn.FORMULATEXT(E13)","v":"=SUM(D9:H9*SEQUENCE(1,5))"}]
[]
[{"$nulls":3},"Total",{"$f":"SUM(D9:H9*{1,2,3,4,5})","v":85},{"$f":"_xlfn.FORMULATEXT(E15)","v":"=SUM(D9:H9*{1,2,3,4,5})"}]
[]
[{"$nulls":3},"Combined array constants"]
[]
[{"$nulls":3},"Total",{"$f":"SUM(_xlfn.SEQUENCE(1,5,3,1)*_xlfn.SEQUENCE(1,5))","v":85},{"$f":"_xlfn.FORMULATEXT(E19)","v":"=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5))"}]
[]
[{"$nulls":3},"Total",{"$f":"SUM({3,4,5,6,7}*{1,2,3,4,5})","v":85},{"$f":"_xlfn.FORMULATEXT(E21)","v":"=SUM({3,4,5,6,7}*{1,2,3,4,5})"}]

{"sheet":"Named array constant","index":5,"n_rows":21,"n_cols":8,"rows_kept":21}
["Array constant in a formula"]
["You can use array constants in your formulas, and mix them with ranges, or combine them"]
["We've gone to Formulas > Name Manager > New Name, and defined Quarter1 as ={\"January\",\"February\",\"March\"}, and Quarter2 as ={\"April\",\"May\",\"June\"} respectively. You can then use them as a formula by entering =Quarter1. We've entered them in cells C9 & F9 as dynamic arrays, and C12 & F12 as legacy arrays."]
["In cell C16 we used =TRANSPOSE(C9:H9) to create a vertical list of months."]
["2GOOD TO KNOW\nGo to Formulas > Defined Names > Name Manager > New to create a named array constant."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[null,null,{"$f":"_xlfn.FORMULATEXT(C9)","v":"=Quarter1"},null,null,{"$f":"_xlfn.FORMULATEXT(F9)","v":"=Quarter2"}]
[null,null,{"$f":"Quarter1","v":"January"},"February","March",{"$f":"Quarter2","v":"April"},"May","June"]
[]
[null,null,{"$f":"_xlfn.FORMULATEXT(C12)","v":"{=Quarter1}"},null,null,{"$f":"_xlfn.FORMULATEXT(F12)","v":"{=Quarter2}"}]
[null,null,{"$f":"Quarter1","v":"January"},"February","March",{"$f":"Quarter2","v":"April"},"May","June"]
[]
[null,null,{"$f":"_xlfn.FORMULATEXT(C16)","v":"=TRANSPOSE(C9:H9)"}]
[null,null,"Month"]
[null,null,{"$f":"TRANSPOSE(C9:H9)","v":"January"}]
[null,null,"February"]
[null,null,"March"]
[null,null,"April"]
[null,null,"May"]
[null,null,"June"]

{"sheet":"Quick sample dataset","index":6,"n_rows":28,"n_cols":13,"rows_kept":28}
["Sample datasets"]
["Create a quick sample dataset like this with only 2 formulas"]
["In cell B9 we have =TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),\"MMM\"), which spills the months from Jan - Dec across to M9. The DATE function uses YEAR(TODAY()) as the YEAR argument, then SEQUENCE(1,12), creates an array from 1 to 12 for the months, then 1 for the DAY argument. The whole thing is wrapped in the TEXT function, which converts the date to MMM text format."]
["In cell B10 we have =INT(RANDARRAY(6,12)*1000) to create a random array of numbers that's 6 rows tall by 12 columns wide. The numbers will change each time Excel recalculates."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[]
[null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,12),1),\"mmm\")","v":"Jan"},"Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"]
[null,{"$f":"INT(_xlfn.RANDARRAY(6,12)*1000)","v":192},732,710,615,19,85,786,967,704,916,739,651]
[null,677,879,484,22,508,148,480,210,249,249,356,242]
[null,610,321,703,170,674,885,470,121,122,931,83,553]
[null,178,61,704,780,732,371,380,338,988,271,511,384]
[null,199,526,888,951,6,490,588,112,105,77,524,615]
[null,425,384,709,667,283,19,848,792,2,171,325,460]
{"$rows_empty":12}
[null,"* If you don't have Office 365, you can use this formula: =TEXT(DATE(YEAR(TODAY()),{1,2,3,4,5,6,7,8,9,10,11,12},1),\"mmm\"), entered with Ctrl+Shift+Enter."]

{"sheet":"Mathematical operators","index":7,"n_rows":24,"n_cols":9,"rows_kept":24}
["Mathematical operators in a formula"]
["You can use mathematical operators in your formulas > Multiply (*), Divide (/), Add (+), and Subtract (-). We've got multiply & divide examples here. You can try to add & subtract with these examples, or your own."]
["We have dynamically created month headers in D8:I8, D11:I11, D14:I14, D17:I17, D20:I20, and D23:I23. Beneath each of those, there are various examples of multiplying (*) and dividing (/) array constants. You can try adjusting the examples to add (+) and subtract (-) on your own."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[]
[null,"Multiply",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D9)","v":"=SEQUENCE(1,6)*2"},null,{"$f":"_xlfn.SEQUENCE(1,6)*2","v":2},4,6,8,10,12]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D12)","v":"={1,2,3,4,5,6}*2"},null,{"$f":"{1,2,3,4,5,6}*2","v":2},4,6,8,10,12]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D15)","v":"{={1,2,3,4,5,6}*2}"},null,{"$f":"{1,2,3,4,5,6}*2","v":2},4,6,8,10,12]
[]
[null,"Divide",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D18)","v":"=SEQUENCE(1,6)/2"},null,{"$f":"_xlfn.SEQUENCE(1,6)/2","v":0.5},1,1.5,2,2.5,3]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D21)","v":"={1,2,3,4,5,6}/2"},null,{"$f":"{1,2,3,4,5,6}/2","v":0.5},1,1.5,2,2.5,3]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D24)","v":"{={1,2,3,4,5,6}/2}"},null,{"$f":"{1,2,3,4,5,6}/2","v":0.5},1,1.5,2,2.5,3]

{"sheet":"Square and square root","index":8,"n_rows":24,"n_cols":9,"rows_kept":24}
["Square and square root"]
["You can use the caret (^) sign to calculate squares, and the SQRT function to calculate square roots. Here the arrays generate a sequence from 1 to 6, and return the square/square root of each."]
["We have dynamically created month headers in D8:I8, D11:I11, D14:I14, D17:I17, D20:I20, and D23:I23. Beneath each of those, there are various examples of squaring, and calculting square roots."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[]
[null,"Square",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D9)","v":"=SEQUENCE(1,6)^2"},null,{"$f":"_xlfn.SEQUENCE(1,6)^2","v":1},4,9,16,25,36]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D12)","v":"={1,2,3,4,5,6}^2"},null,{"$f":"{1,2,3,4,5,6}^2","v":1},4,9,16,25,36]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D15)","v":"{={1,2,3,4,5,6}^2}"},null,{"$f":"{1,2,3,4,5,6}^2","v":1},4,9,16,25,36]
[]
[null,"Square root",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D18)","v":"=SQRT(SEQUENCE(1,6)^2)"},null,{"$f":"SQRT(_xlfn.SEQUENCE(1,6)^2)","v":1},2,3,4,5,6]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D21)","v":"=SQRT({1,2,3,4,5,6}^2)"},null,{"$f":"SQRT({1,2,3,4,5,6}^2)","v":1},2,3,4,5,6]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D24)","v":"{=SQRT({1,2,3,4,5,6}^2)}"},null,{"$f":"SQRT({1,2,3,4,5,6}^2)","v":1},2,3,4,5,6]

{"sheet":"Transpose","index":9,"n_rows":24,"n_cols":9,"rows_kept":24,"merged":[[14,1,17,2],[20,1,23,2]]}
["TRANSPOSE function "]
["You can use the TRANSPOSE function to flip your data the other way around"]
["We have header rows with dynamically generated months in cells D8:I8, D14:I14, and D20:I20. There are 3 x 6 arrays beneath them using the TRANSPOSE function to flip the arrays the other way around. "]
["KEEP IN MIND\nSome array constants can get pretty long. It's better to use SEQUENCE to automatically build them for you."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[null,"TRANSPOSE",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D9)","v":"=TRANSPOSE(SEQUENCE(6,4))"},null,{"$f":"TRANSPOSE(_xlfn.SEQUENCE(6,4))","v":1},5,9,13,17,21]
[{"$nulls":3},2,6,10,14,18,22]
[{"$nulls":3},3,7,11,15,19,23]
[{"$nulls":3},4,8,12,16,20,24]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D15)","v":"=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;17,18,19,20;21,22,23,24})"},null,{"$f":"TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;17,18,19,20;21,22,23,24})","v":1},5,9,13,17,21]
[{"$nulls":3},2,6,10,14,18,22]
[{"$nulls":3},3,7,11,15,19,23]
[{"$nulls":3},4,8,12,16,20,24]
[]
[{"$nulls":3},{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D21)","v":"{=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;17,18,19,20;21,22,23,24})}"},null,{"$f":"TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;17,18,19,20;21,22,23,24})","v":1},5,9,13,17,21]
[{"$nulls":3},2,6,10,14,18,22]
[{"$nulls":3},3,7,11,15,19,23]
[{"$nulls":3},4,8,12,16,20,24]

{"sheet":"Reference existing arrays","index":10,"n_rows":26,"n_cols":9,"rows_kept":26,"merged":[[13,1,15,2],[18,1,20,2],[23,1,25,2]]}
["Reference existing arrays"]
["You can use the spilled range operator (#) to reference a dynamic array. If the array range changes, the spilled range operator will automataically adjust."]
["We have dynamically generated header months in cells D8:I8, D13:I13, D18:I18, and D23:I23. Beneath those are 3 x 6 arrays. The first and third examples use array functions to populate those ranges, while the second and fourth examples use the spilled range operator (#) to dynamically reference the arrays above them. For example, =D9# will return the array that's spilled to cells D9:I11."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[]
[null,"Array",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D9)","v":"=SEQUENCE(3,6,10,10)"},null,{"$f":"_xlfn.SEQUENCE(3,6,10,10)","v":10},20,30,40,50,60]
[{"$nulls":3},70,80,90,100,110,120]
[{"$nulls":3},130,140,150,160,170,180]
[]
[null,"Spilled range operator",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D14)","v":"=D9#"},null,{"$f":"_xlfn.ANCHORARRAY(D9)","v":10},20,30,40,50,60]
[{"$nulls":3},70,80,90,100,110,120]
[{"$nulls":3},130,140,150,160,170,180]
[]
[null,"Array",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D19)","v":"={10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170,180}"},null,{"$f":"{10,20,30,40,50,60;70,80,90,100,110,120;130,140,150,160,170,180}","v":10},20,30,40,50,60]
[{"$nulls":3},70,80,90,100,110,120]
[{"$nulls":3},130,140,150,160,170,180]
[]
[null,"Spilled range operator",null,{"$f":"TEXT(DATE(YEAR(TODAY()),_xlfn.SEQUENCE(1,6),1),\"mmmm\")","v":"January"},"February","March","April","May","June"]
[null,{"$f":"_xlfn.FORMULATEXT(D24)","v":"=D19#"},null,{"$f":"_xlfn.ANCHORARRAY(D19)","v":10},20,30,40,50,60]
[{"$nulls":3},70,80,90,100,110,120]
[{"$nulls":3},130,140,150,160,170,180]

{"sheet":"Count characters","index":11,"n_rows":25,"n_cols":7,"rows_kept":25}
["Count or return characters"]
["You can use arrays to count how many characters are in text strings, or even find the shortest and longest text strings. As a bonus, we used the CONCAT function to convert the text strings into a proper sentence."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[]
[]
[null,null,"Data",null,null,"CONCAT()",{"$f":"_xlfn.FORMULATEXT(G9)","v":"=CONCAT(C9,\" \",C10,\" \",C11,\" \",C12,\" \",C13)"}]
[null,null,"This is a",{"$nulls":3},{"$f":"_xlfn.CONCAT(C9,\" \",C10,\" \",C11,\" \",C12,\" \",C13)","v":"This is a bunch of cells that come together to form a single sentence."}]
[null,null,"bunch of cells that"]
[null,null,"come together"]
[null,null,"to form a"]
[null,null,"single sentence."]
[]
[null,null,"Total characters in a range"]
[null,null,{"$f":"SUM(LEN(C9:C13))","v":66},null,null,{"$f":"_xlfn.FORMULATEXT(C16)","v":"=SUM(LEN(C9:C13))"}]
[]
[null,null,"Find the longest text string in a range of cells"]
[null,null,{"$f":"INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)","v":"bunch of cells that"},null,null,{"$f":"_xlfn.FORMULATEXT(C19)","v":"=INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)"}]
[]
[null,null,"Find the shortest text string in a range of cells"]
[null,null,{"$f":"INDEX(C9:C13,MATCH(MIN(LEN(C9:C13)),LEN(C9:C13),0),1)","v":"This is a"},null,null,{"$f":"_xlfn.FORMULATEXT(C22)","v":"=INDEX(C9:C13,MATCH(MIN(LEN(C9:C13)),LEN(C9:C13),0),1)"}]
[]
[null,null,"Average number of characters in each text string"]
[null,null,{"$f":"AVERAGE(LEN(C9:C13))","v":13.2},null,null,{"$f":"_xlfn.FORMULATEXT(C25)","v":"=AVERAGE(LEN(C9:C13))"}]

{"sheet":"Nth values","index":12,"n_rows":18,"n_cols":12,"rows_kept":18}
["Nth values"]
["You can use arrays to return the Nth largest or smallest values from a set with the SMALL & LARGE functions"]
["We have a series of random numbrs in cells B9:B18 populated with =INT(RANDARRAY(10,1)*100). There is a variable value in cell D9 that you can change to adjust the output of the Nth smallest or largest values in the list. It's currently set to 3. The Nth smallest values are listed beginning in cell F9, and the Nth largest values are start in cell H9. Tehre are additional formulas in cells J9, J12, J15, and J18."]
["To get the Nth smallest values, we used =SMALL(B9#,SEQUENCE(D9)), which uses the spilled range operator to look in the array beginning in cell B9, then the sequence function creates an array the same size as the value entered in cell D9."]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[null,"Data",null,"Value",null,"Nth Smallest",null,"Nth Largest",null,"SUM of Nth Smallest values"]
[null,{"$f":"INT(_xlfn.RANDARRAY(10,1)*100)","v":2},null,3,null,{"$f":"SMALL(_xlfn.ANCHORARRAY(B9),_xlfn.SEQUENCE(D9))","v":2},null,{"$f":"LARGE(_xlfn.ANCHORARRAY(B9),_xlfn.SEQUENCE(D9))","v":97},null,{"$f":"SUM(SMALL(_xlfn.ANCHORARRAY(B9),_xlfn.SEQUENCE(D9)))","v":57},null,{"$f":"_xlfn.FORMULATEXT(J9)","v":"=SUM(SMALL(B9#,SEQUENCE(D9)))"}]
[null,39,{"$nulls":3},16,null,94]
[null,94,{"$nulls":3},39,null,89,null,"SUM of Nth largest values"]
[null,89,{"$nulls":7},{"$f":"SUM(LARGE(_xlfn.ANCHORARRAY(B9),_xlfn.SEQUENCE(D9)))","v":280},null,{"$f":"_xlfn.FORMULATEXT(J12)","v":"=SUM(LARGE(B9#,SEQUENCE(D9)))"}]
[null,58]
[null,16,{"$nulls":7},"Average of Nth Smallest values"]
[null,76,{"$nulls":7},{"$f":"AVERAGE(SMALL(_xlfn.ANCHORARRAY(B9),_xlfn.SEQUENCE(D9)))","v":{"v":19,"d":"19.00"}},null,{"$f":"_xlfn.FORMULATEXT(J15)","v":"=AVERAGE(SMALL(B9#,SEQUENCE(D9)))"}]
[null,97]
[null,44,{"$nulls":7},"Average of Nth largest values"]
[null,65,{"$nulls":7},{"$f":"AVERAGE(LARGE(_xlfn.ANCHORARRAY(B9),_xlfn.SEQUENCE(D9)))","v":{"v":93.33333333333333,"d":"93.33"}},null,{"$f":"_xlfn.FORMULATEXT(J18)","v":"=AVERAGE(LARGE(B9#,SEQUENCE(D9)))"}]

{"sheet":"Errors","index":13,"n_rows":18,"n_cols":7,"rows_kept":18}
["Errors"]
["You can use arrays to ignore or count errors in your data where normal functions would fail"]
["We have sample data in cells C9:C18, which has been defined as \"Data\". There is a #VALUE! Error in cell C11, and a #N/A error in cell C14. These error values will prevent most formulas, like SUM or COUNT from functioning properly, and they will instead return errors of their own. In cell E9 we used =SUM(IF(ISERROR(Data),\"\",Data)) to sum the data and ignore the error. In cell E12 we used =SUM(IF(ISERROR(Data),1,0)) to count the number of errors in the data range. We calculate the average in cell E15, and the minumum value in cell E18. You can try to create a few of your own using COUNT or MAX."]
["EXPERIMENT\nCan you find the maximum value in the range?"]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[null,null,"Data",null,"Sum a range that contains error values"]
[null,null,40,null,{"$f":"SUM(IF(ISERROR(Data),\"\",Data))","v":371},null,{"$f":"_xlfn.FORMULATEXT(E9)","v":"=SUM(IF(ISERROR(Data),\"\",Data))"}]
[null,null,38]
[null,null,{"$f":"\"A\"+10","v":{"$error":"#VALUE!"}},null,"Count the number of errors in a range"]
[null,null,76,null,{"$f":"SUM(IF(ISERROR(Data),1,0))","v":2},null,{"$f":"_xlfn.FORMULATEXT(E12)","v":"=SUM(IF(ISERROR(Data),1,0))"}]
[null,null,73]
[null,null,{"$f":"NA()","v":{"$error":"#N/A"}},null,"Average the values in the range"]
[null,null,22,null,{"$f":"AVERAGE(IF(ISERROR(Data),\"\",Data))","v":{"v":46.375,"d":"46.38"}},null,{"$f":"_xlfn.FORMULATEXT(E15)","v":"=AVERAGE(IF(ISERROR(Data),\"\",Data))"}]
[null,null,37]
[null,null,0,null,"Find the minimum value in the range"]
[null,null,85,null,{"$f":"MIN(IF(ISERROR(Data),\"\",Data))","v":0},null,{"$f":"_xlfn.FORMULATEXT(E18)","v":"=MIN(IF(ISERROR(Data),\"\",Data))"}]

{"sheet":"Conditions","index":14,"n_rows":25,"n_cols":10,"rows_kept":25}
["Conditions"]
["You can use arrays calculate based on certain conditions"]
["We have data by sales rep and month in cells C9:E24, where column E has been defined as \"Sales\". There are several examples of formulas to sum based on different conditions in cells G8, G10, G12, and G14. For example, cell G8 has the formula =SUM(IF(Sales>0,Sales)), which sums all cells in the Sales column that are greater than 0."]
["HERE'S THE KEY\nUse the asterisk (*) for the AND operator, and the Plus (+) sign for the OR operator. "]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[]
[null,null,"Sales Rep","Month","Sales",null,"Sum values > 0"]
[null,null,"Tom","January",{"v":9882,"d":"9,882 "},null,{"$f":"SUM(IF(Sales>0,Sales))","v":{"v":67692,"d":"67,692 "}},null,null,{"$f":"_xlfn.FORMULATEXT(G10)","v":"=SUM(IF(Sales>0,Sales))"}]
[null,null,"Fred","January",{"v":9418,"d":"9,418 "}]
[null,null,"Amy","January",{"v":3355,"d":"3,355 "},null,"Sum values > 0 AND < 2500"]
[null,null,"Sal","January",{"v":0,"d":"0 "},null,{"$f":"SUM((Sales>0)*(Sales<=2500)*(Sales))","v":{"v":2839,"d":"2,839 "}},null,null,{"$f":"_xlfn.FORMULATEXT(G13)","v":"=SUM((Sales>0)*(Sales<=2500)*(Sales))"}]
[null,null,"Fritz","January",{"v":2304,"d":"2,304 "}]
[null,null,"Sravan","January",{"v":7134,"d":"7,134 "},null,"Sum values > 0 OR < 2500"]
[null,null,"Xi","January",{"v":5751,"d":"5,751 "},null,{"$f":"SUM(IF((Sales>0)+(Sales<2500),Sales))","v":{"v":61900,"d":"61,900 "}},null,null,{"$f":"_xlfn.FORMULATEXT(G16)","v":"=SUM(IF((Sales>0)+(Sales<2500),Sales))"}]
[null,null,"Hector","January",{"v":6282,"d":"6,282 "}]
[null,null,"Tom","February",{"v":-2210,"d":"(2,210)"},null,"Average excluding 0"]
[null,null,"Fred","February",{"v":9207,"d":"9,207 "},null,{"$f":"AVERAGE(IF(Sales<>0,Sales))","v":{"v":4126.666666666667,"d":"4,127 "}},null,null,{"$f":"_xlfn.FORMULATEXT(G19)","v":"=AVERAGE(IF(Sales<>0,Sales))"}]
[null,null,"Amy","February",{"v":10,"d":"10 "}]
[null,null,"Sal","February",{"v":3214,"d":"3,214 "}]
[null,null,"Fritz","February",{"v":-3582,"d":"(3,582)"}]
[null,null,"Sravan","February",{"v":2553,"d":"2,553 "}]
[null,null,"Xi","February",{"v":525,"d":"525 "}]
[null,null,"Hector","February",{"v":8057,"d":"8,057 "}]

{"sheet":"Differences between datasets","index":15,"n_rows":23,"n_cols":12,"rows_kept":23}
["Count the number of differences between two ranges of cells"]
["You can use arrays to compare data sources for differences"]
["We have two tables of monthly sales in cells B9:D23 and F9:F23 with headers in row 8. Column D, the sales column has been defined as Qtr_1, while column H has been defined as Qtr_2. There are comparison arrays in cells J9, J11, J13, J15, and J17. For instance, in cell J9 we have =SUM(IF(Qtr_1=Qtr_2,0,1)), which counts the number of differences between two ranges. There are descriptions of what each formula does starting in cell L9."]
["GOOD TO KNOW\nQtr_1 is a named range for cells D9:D23\nQtr_2 is a named range for cells H9:H23"]
["Use CTRL+PAGE UP up to return to the previous worksheet, CTRL+PAGE DOWN to advance to the next sheet."]
["Give us feedback on this tutorial"]
[]
[null,"Sales Rep","Month","Sales",null,"Sales Rep","Month","Sales",null,"Count the number of differences between two ranges"]
[null,"Tom","January",{"v":7825,"d":"7,825"},null,"Tom","April",{"v":6707,"d":"6,707"},null,{"$f":"SUM(IF(Qtr_1=Qtr_2,0,1))","v":15},null,{"$f":"_xlfn.FORMULATEXT(J9)","v":"=SUM(IF(Qtr_1=Qtr_2,0,1))"}]
[null,"Fred","January",{"v":5433,"d":"5,433"},null,"Fred","April",{"v":7557,"d":"7,557"}]
[null,"Amy","January",254,null,"Amy","April",{"v":8094,"d":"8,094"},null,"Find the row # of the maximum value in a range"]
[null,"Sal","January",{"v":4971,"d":"4,971"},null,"Sal","April",{"v":4897,"d":"4,897"},null,{"$f":"MIN(IF(Qtr_1=MAX(Qtr_1),ROW(Qtr_1),\"\"))","v":21},null,{"$f":"_xlfn.FORMULATEXT(J12)","v":"=MIN(IF(Qtr_1=MAX(Qtr_1),ROW(Qtr_1),\"\"))"}]
[null,"Fritz","January",{"v":5796,"d":"5,796"},null,"Fritz","April",{"v":6916,"d":"6,916"}]
[null,"Tom","February",{"v":4398,"d":"4,398"},null,"Tom","May",{"v":1338,"d":"1,338"},null,"Find the row # of the minimum value in a range"]
[null,"Fred","February",{"v":2870,"d":"2,870"},null,"Fred","May",{"v":8043,"d":"8,043"},null,{"$f":"MAX(IF(Qtr_1=MIN(Qtr_1),ROW(Qtr_1),\"\"))","v":11},null,{"$f":"_xlfn.FORMULATEXT(J15)","v":"=MAX(IF(Qtr_1=MIN(Qtr_1),ROW(Qtr_1),\"\"))"}]
[null,"Amy","February",{"v":7861,"d":"7,861"},null,"Amy","May",{"v":1623,"d":"1,623"}]
[null,"Sal","February",{"v":4934,"d":"4,934"},null,"Sal","May",810,null,"Find the address of the maximum value in a range"]
[null,"Fritz","February",{"v":8047,"d":"8,047"},null,"Fritz","May",{"v":1066,"d":"1,066"},null,{"$f":"ADDRESS(MIN(IF(Qtr_1=MAX(Qtr_1),ROW(Qtr_1),\"\")),COLUMN(Qtr_1))","v":"$D$21"},null,{"$f":"_xlfn.FORMULATEXT(J18)","v":"=ADDRESS(MIN(IF(Qtr_1=MAX(Qtr_1),ROW(Qtr_1),\"\")),COLUMN(Qtr_1))"}]
[null,"Tom","March",{"v":1427,"d":"1,427"},null,"Tom","June",{"v":9681,"d":"9,681"}]
[null,"Fred","March",{"v":7970,"d":"7,970"},null,"Fred","June",{"v":9451,"d":"9,451"},null,"Find the address of the minimum value in a range"]
[null,"Amy","March",{"v":8379,"d":"8,379"},null,"Amy","June",{"v":6235,"d":"6,235"},null,{"$f":"ADDRESS(MAX(IF(Qtr_1=MIN(Qtr_1),ROW(Qtr_1),\"\")),COLUMN(Qtr_1))","v":"$D$11"},null,{"$f":"_xlfn.FORMULATEXT(J21)","v":"=ADDRESS(MAX(IF(Qtr_1=MIN(Qtr_1),ROW(Qtr_1),\"\")),COLUMN(Qtr_1))"}]
[null,"Sal","March",{"v":1938,"d":"1,938"},null,"Sal","June",867]
[null,"Fritz","March",956,null,"Fritz","June",{"v":2847,"d":"2,847"}]

{"sheet":"Learn More","index":16,"n_rows":19,"n_cols":6,"rows_kept":19}
["Learn more - Scroll down to cells B11, D11, and F11 for links to other information"]
["Use CTRL+PAGE UP  to return to the previous worksheet"]
["Give us feedback on this tutorial"]
[]
[]
[]
[]
[]
[]
[null,"Related articles",null,"Other helpful information",null,"More resources"]
[null,"FILTER function",null,"Excel functions (by category)",null,"Training courses"]
[null,"RANDARRAY function",null,"Excel functions (alphabetical)",null,"Tech community"]
[null,"SEQUENCE function",null,"Overview of formulas in Excel",null,"Excel blog"]
[null,"SINGLE function",null,"How to avoid a broken formula",null,"Make a suggestion at Excel User Voice"]
[null,"SORT function"]
[null,"SORTBY function"]
[null,"UNIQUE function"]
[null,"Spilled array behavior"]
[null,"Dynamic array formulas vs. legacy CSE array formulas"]