{"id":3437,"date":"2018-01-18T16:02:27","date_gmt":"2018-01-18T22:02:27","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3437"},"modified":"2025-02-10T11:32:54","modified_gmt":"2025-02-10T17:32:54","slug":"array-formula-examples","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-formulas\/array-formula-examples.html","title":{"rendered":"Array Formula Examples &#8211; Simple to Advanced"},"content":{"rendered":"<div class=\"wp-post-image\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\">\n<img decoding=\"async\" width=\"300\" height=\"240\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/excel-array-formula-examples.png\" alt=\"Excel Array Formula Examples\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/excel-array-formula-examples.png\"><meta itemprop=\"width\" content=\"300\"><meta itemprop=\"height\" content=\"240\"><\/div>\n<p>In Excel, an <b>Array Formula<\/b> allows you to do powerful calculations on one or more value sets. The result may fit in a single cell or it may be an array. An <i>array<\/i> is just a list or range of values, but an <i>Array Formula<\/i> is a special type of formula that must be entered by pressing <span class=\"key\">Ctrl<\/span>+<span class=\"key\">Shift<\/span>+<span class=\"key\">Enter<\/span>. The formula bar will show the formula surrounded by curly brackets <b>{=...}<\/b>.<\/p>\n<p>Array formulas are frequently used for data analysis, conditional sums and lookups, linear algebra, matrix math and manipulation, and much more. A new Excel user might come across array formulas in other people's spreadsheets, but creating array formulas is typically an intermediate-to-advanced topic.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/ArrayFormulas.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'ArrayFormulas.xlsx');\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (ArrayFormulas.xlsx)<\/p>\n<h3>Topics and Examples in This Article:<\/h3>\n<ul>\n<li><a href=\"#cse\" class=\"bm\">Entering an Array Formula<\/a><\/li>\n<li><a href=\"#array-constants\" class=\"bm\">Using Array Constants<\/a><\/li>\n<li><a href=\"#simple-example\" class=\"bm\">A Simple Array Formula Example<\/a><\/li>\n<li><a href=\"#multi-cell\" class=\"bm\">Entering a Multi-Cell Array Formula<\/a><\/li>\n<li><a href=\"#office365\" class=\"bm\">Dynamic \"Spillable\" Arrays in Office365<\/a><\/li>\n<li><a href=\"#if\" class=\"bm\">Nested IF Array Formulas<\/a><\/li>\n<li><a href=\"#countif\" class=\"bm\">COUNTIF Alternative: SUM-Boolean Array Formulas<\/a><\/li>\n<li><a href=\"#multi-criteria\" class=\"bm\">Multi-Criteria Boolean Array Formulas<\/a><\/li>\n<li><a href=\"#sequential\" class=\"bm\">Sequential Number Arrays (1,2,3,...)<\/a><\/li>\n<li><a href=\"#matrices\" class=\"bm\">Formulas for Matrices: MUNIT, MMULT, TRANSPOSE, etc.<\/a><\/li>\n<li><a href=\"#other\" class=\"bm\">Other Array Formula Examples<\/a><\/li>\n<\/ul>\n<h2 id=\"video\">Watch the Intro Video<\/h2>\n<div itemprop=\"video\" itemscope itemtype=\"http:\/\/schema.org\/VideoObject\">\n<iframe width=\"730\" height=\"411\" src=\"https:\/\/www.youtube.com\/embed\/7nEyAXiJ304?rel=0\" frameborder=\"0\" allowfullscreen><\/iframe><meta itemprop=\"name\" content=\"How to Create Array Formulas in Excel\"><meta itemprop=\"description\" content=\"Learn how to create single and multi-cell array formulas in Excel.\"><meta itemprop=\"thumbnailURL\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/video-array-formulas-in-excel.png\"><meta itemprop=\"embedURL\" content=\"https:\/\/www.youtube.com\/embed\/7nEyAXiJ304?rel=0\"><meta itemprop=\"uploadDate\" content=\"2018-02-28\">\n<\/div>\n<h2 id=\"cse\">Entering and Identifying an Array Formula<\/h2>\n<ol class=\"bulletlist\">\n<li>When using an Array Formula, you press <span class=\"key\">Ctrl<\/span>+<span class=\"key\">Shift<\/span>+<span class=\"key\">Enter<\/span> instead of just <span class=\"key\">Enter<\/span> after entering or editing the formula. This is why array formulas are often called <b>CSE<\/b> formulas.<\/li>\n<li>An Array Formula will show <b>curly brackets<\/b> or <b>braces<\/b> around the formula in the <b>Formula Bar<\/b> like this: <b>{<\/b>=SUM(A1:A5*B1:B5)<b>}<\/b><\/li>\n<li><b>Array Constants<\/b> (arrays &quot;hard-coded&quot; into formulas) are enclosed in braces <b>{ }<\/b> and use commas to separate columns, and semi-colons to separate rows, like this 2x3 array: <b>{1, 1, 1; 2, 2, 2}<\/b><\/li>\n<li>If an Array Formula returns more than one value (a <i>multi-cell array formula<\/i>), first select a range of cells equal to size of the returned array, then enter your formula.<\/li>\n<li>To select all the cells within a multi-cell array: <b>Press <span class=\"key\">F5<\/span> &gt; Special &gt; Current Array<\/b>.<\/li>\n<\/ol>\n<div class=\"caution-box\">\n<p><span class=\"caution-label\">!<\/span> <strong>Every time you edit an Array Formula, you must remember to press <span class=\"key\">Ctrl<\/span>+<span class=\"key\">Shift<\/span>+<span class=\"key\">Enter<\/span> afterward. If you forget to, the formula may return an error without you realizing it.<\/strong><\/p>\n<\/div>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> Google Sheets uses the <a href=\"https:\/\/support.google.com\/docs\/answer\/3093275\" target=\"_blank\" rel=\"noopener noreferrer\">ARRAYFORMULA<\/a> function instead of showing the formula surrounded by braces. It is not necessary to press Ctrl+Shift+Enter in Google Sheets, but if you do, ARRAYFORMULA( is added to the beginning of the formula.<\/p>\n<\/div>\n<h2 id=\"array-constants\">Using Array Constants in Formulas<\/h2>\n<p>Many functions allow you use array constants like {1,2,6,12} as arguments within formulas. An example that I often use in my <a href=\"\/ExcelTemplates\/yearly-calendar.html\">yearly calendar templates<\/a> returns the weekday abbreviation for a given date. The nice thing about this formula is that you can choose whether to display a single character or two characters.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>({\"Su\";\"M\";\"Tu\";\"W\";\"Th\";\"F\";\"Sa\"},<span class=\"function\">WEEKDAY<\/span>(<span class=\"arg\">theDate<\/span>,1))<\/pre>\n<p>This formula is <i>not<\/i> technically an Array Formula because you don't enter it using Ctrl+Shift+Enter. Using a hard-coded array within a formula does not necessarily require using Ctrl+Shift+Enter.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">TIP<\/span> If you are going to use the array constant in multiple formulas, you may want to first create a Named Constant. Go to Formulas &gt; Name Manager &gt; New Name, enter a descriptive name like <i>payment_frequency<\/i> and enter ={1,2,6,12} into the Refers To field. You can use the name within your formulas. If you ever want to change the values within that array constant, you only need to change it one place (within the Name Manager).<\/p>\n<\/div>\n<h2 id=\"simple-example\">A Simple Array Formula Example<\/h2>\n<p>To start out, I will show how an array formula works using a very basic example. Let's say that I have a list of tasks, the number of days each of those tasks will take, and a column for the percent complete. I want to know the total number of days that have been completed.<\/p>\n<p>Without an array formula, you would create another column called \"Completed\" and multiply the number of days by the % complete, and copy the formula down. Then I would use SUM to total the number of days completed, like the image below:<\/p>\n<p><img decoding=\"async\" src=\"\/blog\/images\/excel-formulas\/array-formula-example-how-it-works.png\" class=\"imgshadow aligncenter\" alt=\"Array Formula Example - How it Works\"><\/p>\n<p>With an <i>array formula<\/i>, you can do essentially the same thing without having to create the extra column. Within a single cell, you can calculate the total days completed as =SUM(D18:D22*E18:E22), remembering to press Ctrl+Shift+Enter because it is an array formula.<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"arg\">D18:D22<\/span>*<span class=\"arg\">E18:E22<\/span>) <span class=\"green\">}<\/span>\r\n\r\nEvaluation Steps\r\nStep 1: =SUM( <span class=\"arg\">{10;5;8;3;12}<\/span> * <span class=\"arg\">{0.5;0.2;0.07;0.5;0.09}<\/span> )\r\nStep 2: =SUM( <span class=\"arg\">{10*0.5;5*0.2;8*0.07;3*0.5;12*0.09}<\/span> )\r\nStep 3: =SUM( <span class=\"arg\">{5;1;0.56;1.5;1.08}<\/span> )\r\nStep 4: =<span class=\"green\">9.14<\/span><\/pre>\n<p>In this and other examples, I've shown the evaluation steps below the formula so that you can see how the formula works. You don't actually type the curly brackets { }, but in this article I will surround all array formulas with brackets to indicate that they are entered as CSE formulas.<\/p>\n<p>In the evaluation steps shown in the above example, you'll see that Excel is multiplying each element of the first array by the corresponding element in the second array, and then SUM adds the results.<\/p>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> It turns out that this particular example can be used to show how the SUMPRODUCT function works, but the SUMPRODUCT function deserves its own article.<\/p>\n<\/div>\n<p>To take this example just a bit further, if all we wanted to know was the Total Percent Complete for the entire project, we can divide the total days completed (9.14) by the total days (38) all within a single array formula, and we don't need column F at all (as shown in the image below).<\/p>\n<p><img decoding=\"async\" src=\"\/blog\/images\/excel-formulas\/array-formula-example-sum-product.png\" class=\"imgshadow aligncenter\" alt=\"Array Formula Example - Sum Product\"><\/p>\n<p>This example is an example of a <i><b>single-cell array formula<\/b><\/i>, meaning that the formula is entered into a single cell.<\/p>\n<h2 id=\"multi-cell\">Entering a Multi-Cell Array Formula<\/h2>\n<p>Whenever your array formula returns more than one value, if you want to display more than just the first value, you need to select the range of cells that will contain the resulting array before entering your formula. Doing this will result in a <b><i>multi-cell array formula<\/i><\/b>, meaning that the <i>result<\/i> of the formula is a multi-cell array.<\/p>\n<p>Using the same example as above, we could use an array formula in the Completed column to calculate Days * Percent Complete. First, select cells F18:F22, then press = and enter the formula, followed by Ctrl+Shift+Enter (CSE). The image below is what it will look like just before you press CSE.<\/p>\n<p><img decoding=\"async\" src=\"\/blog\/images\/excel-formulas\/multi-cell-array-formula-example.png\" class=\"imgshadow aligncenter\" alt=\"Array Multi-Cell Array Formula Example\"><\/p>\n<p><b>You can edit a multi-cell array formula<\/b> by selecting any of the cells in the array and then updating the formula and pressing Ctrl+Shift+Enter when you are done. However, you can't use this technique to modify the size of the array.<\/p>\n<p>\"<b>You can't change part of an array<\/b>\" - This is the warning or error you will get if you try to insert rows or columns or change individual cells within a multi-cell array.<\/p>\n<p>Using multi-cell array formulas can make it more difficult to customize a spreadsheet because to change the size of the array requires that you (1) delete the formula (after selecting all the cells of the array), (2) select the new range of cells, and (3) re-enter the array formula. <b>TIP:<\/b> Make sure to copy your original formula before deleting it. Then, when you re-enter the formula, you can paste it and modify the ranges.<\/p>\n<h2 id=\"office365\">Dynamic Array Formulas<\/h2>\n<p><b>Office 365!<\/b> This article was originally written before the new dynamic array formulas or \"spillable\" arrays were available. In Office 365, many of the formulas and functions no longer require the use of Ctrl+Shift+Enter. Some of the new functions, such as <b>SEQUENCE<\/b>, make the formulas in this article much easier. I have included some of these new examples, highlighted in purple.<\/p>\n<h2 id=\"if\">Nested IF Array Formulas<\/h2>\n<p>A nested IF array formula can be very powerful and is probably one of the more common uses for array formulas in Excel. Although Excel provides the SUMIF and COUNTIF and AVERAGEIF functions, they don't allow as much freedom as a nested IF array formula.<\/p>\n<h3>MAX-IF Array Formula<\/h3>\n<p>Older versions of Excel do not have the MAXIFS or MINIFS functions, so let's create our own MAX-IF formula. When we use hyphens to name a formula, it usually means that we're nesting the functions (IF within MAX in this case).<\/p>\n<p>Let's say that I have the following contact and sales log and I want a formula that will tell me when I last contacted Bob (cell H51).<\/p>\n<p><img decoding=\"async\" src=\"\/blog\/images\/excel-formulas\/max-if-function-example-array-formula.png\" class=\"imgshadow aligncenter\" alt=\"MAX-IF Array Formula Example\"><\/p>\n<p>Using MAX on the date range will give me that latest date (9\/10\/2017), but I only want to include the rows where the contact is Bob. So, I'll use the MAX-IF array formula:<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">MAX<\/span>(<span class=\"function\">IF<\/span>(<span class=\"arg\">contact_range<\/span>=\"Bob\",<span class=\"arg\">date_range<\/span>)) <span class=\"green\">}<\/span>\r\n\r\nEvaluation Steps\r\nStep 1: =MAX(IF(<span class=\"green\">{\"Jim\";\"Bob\";\"Jim\";\"Bob\";\"Jim\"}<\/span>=\"Bob\", date_range ))\r\nStep 2: =MAX(IF(<span class=\"green\">{FALSE;TRUE;FALSE;TRUE;FALSE}<\/span>, date_range ))\r\nStep 3: =MAX( <span class=\"green\">{FALSE,2\/25\/2017,FALSE,8\/5\/2017,FALSE}<\/span> )\r\nStep 4: =<span class=\"green\">8\/5\/2017<\/span><\/pre>\n<h3>LARGE-IF Array Formula<\/h3>\n<p>The LARGE and SMALL functions come in handy when you want to find the value that is perhaps the 2nd largest or 2nd smallest.<\/p>\n<p>The following function will return the second largest sale where the contact is Jim.<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">LARGE<\/span>(<span class=\"function\">IF<\/span>(<span class=\"arg\">contact_range<\/span>=\"Jim\",<span class=\"arg\">sale_range<\/span>),2) <span class=\"green\">}<\/span><\/pre>\n<h3>SMALL-IF Array Formula<\/h3>\n<p>This function returns the second smallest sale where the contact is Jim.<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">SMALL<\/span>(<span class=\"function\">IF<\/span>(<span class=\"arg\">contact_range<\/span>=\"Jim\",<span class=\"arg\">date_range<\/span>),2) <span class=\"green\">}<\/span><\/pre>\n<p>The LARGE and SMALL functions can be used for sorting arrays. More on that later. Hopefully, Excel will introduce a SORT function soon (Google Sheets has already done that).<\/p>\n<p>The SMALL-IF formula can be used in combination with INDEX to do a <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/vlookup-and-index-match-examples.html#lookup-nth-match\">lookup a value based on the Nth Match<\/a>.<\/p>\n<h3>SUM-IF Array Formula<\/h3>\n<p>Yes, there is already a <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/sumif-and-countif-in-excel.html\">SUMIF function<\/a> that is generally better than using an array formula, but we'll be getting into more advanced SUM-IF array formulas, so it's useful to see the simple example:<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"function\">IF<\/span>(<span class=\"arg\">contact_range<\/span>=\"Jim\",<span class=\"arg\">sales_range<\/span>)) <span class=\"green\">}<\/span><\/pre>\n<p><i>More Reading:<\/i> Chip Pearson provides some great examples of ways to use nested IF functions within the SUM and AVERAGE functions to ignore errors and zero values. <a href=\"http:\/\/www.cpearson.com\/excel\/arrayformulas.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">See Chip Pearson's article<\/a>.<\/p>\n<h2 id=\"countif\">COUNTIF Alternative: SUM-Boolean Array Formulas<\/h2>\n<p>Although there is already a <a href=\"\/blog\/excel-formulas\/sumif-and-countif-in-excel.html\">COUNTIF function<\/a>, the criteria available in the COUNTIF family of functions is limited. An alternative method is to do a SUM of boolean (TRUE\/FALSE) results that have been converted to 0s and 1s  (FALSE=0, TRUE=1). Boolean results can be converted to 0s and 1s by adding +0, multiplying by *1 and by using double negation.<\/p>\n<h4>SUM-ISERROR: Count the number of Error values in a range<\/h4>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"green\">1*<\/span><span class=\"function\">ISERROR<\/span>(<span class=\"arg\">range<\/span>)) <span class=\"green\">}<\/span>\r\n<span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"green\">0+<\/span><span class=\"function\">ISERROR<\/span>(<span class=\"arg\">range<\/span>)) <span class=\"green\">}<\/span>\r\n<span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"green\">--<\/span><span class=\"function\">ISERROR<\/span>(<span class=\"arg\">range<\/span>)) <span class=\"green\">}<\/span>\r\n\r\nEvaluation Steps\r\nStep 1: =SUM( 1*<span class=\"green\">{FALSE,TRUE,TRUE,FALSE,TRUE}<\/span> )\r\nStep 2: =SUM( <span class=\"green\">{0,1,1,0,1}<\/span> )\r\nStep 3: =<span class=\"green\">3<\/span>\r\n<\/pre>\n<h4>SUM-ISBLANK: Count the number of Blank values in a range<\/h4>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"green\">--<\/span><span class=\"function\">ISBLANK<\/span>(<span class=\"arg\">range<\/span>)) <span class=\"green\">}<\/span><\/pre>\n<p>Remember: A formula that returns an empty \"\" string is considered NOT blank.<\/p>\n<h4>SUM-NOT-ISBLANK: Count the number of Non-Blank values in a range<\/h4>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"green\">--<\/span><span class=\"function\">NOT<\/span>(<span class=\"function\">ISBLANK<\/span>(<span class=\"arg\">range<\/span>)) <span class=\"green\">}<\/span><\/pre>\n<h2 id=\"multi-criteria\">Multi-Criteria Boolean Array Formulas<\/h2>\n<p>The AND and OR functions return only a single value, even when they contain multiple arrays, so we don't generally use them within array formulas.<\/p>\n<p>For multiple-criteria logical array formulas, such as SUM-IF between two dates, you need to do the boolean logic by <b>adding boolean values for \"or\"<\/b> conditions and by <b>multiplying boolean values for \"and\"<\/b> conditions.<\/p>\n<h3>SUM-IF Between Two Dates<\/h3>\n<p>Yes, <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/sumif-and-countif-in-excel.html\">SUMIFS<\/a> would be easier, but let's assume we are using an older version of Excel. Referring back to the Contact and Sales log, we'll sum all of the Sales between 2\/1\/2017 and 9\/1\/2017, meaning that Date &gt;= 2\/1\/2017 AND Date &lt;= 9\/1\/2017.<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"function\">IF<\/span>((<span class=\"arg\">date_range<\/span>&gt;=<span class=\"arg\">start<\/span>)*(<span class=\"arg\">date_range<\/span>&lt;=<span class=\"arg\">end<\/span>), <span class=\"arg\">sum_range<\/span>) ) <span class=\"green\">}<\/span>\r\n\r\nEvaluation Steps\r\nStep 1: SUM(IF(<span class=\"green\">{FALSE,TRUE,TRUE,TRUE,TRUE}<\/span>*<span class=\"green\">{TRUE,TRUE,TRUE,TRUE,FALSE}<\/span>,sum_range))\r\nStep 2: SUM(IF(<span class=\"green\">{0,1,1,1,0}<\/span>,sum_range))\r\nStep 3: SUM(<span class=\"green\">{FALSE,300,500,0,FALSE}<\/span>)\r\nStep 4: <span class=\"green\">800<\/span>\r\n<\/pre>\n<p>In this case we don't need to use 1*(...) to convert the boolean values, because the boolean values are converted to 0s and 1s automatially when we multiply the two arrays together. The IF function in Excel treats the value 0 as FALSE and all other values as TRUE.<\/p>\n<h3>Overlapping OR Conditions<\/h3>\n<p>To demonstrate a logical OR condition, we'll sum the sales where Name = \"Bob\" OR Date &gt; 7\/1\/2017. An \"or\" condition is true when one or more of the conditions is true, so we check whether the sum of the expressions is greater than 0.<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">SUM<\/span>(<span class=\"function\">IF<\/span>( ((<span class=\"arg\">contact_range<\/span>=<span class=\"arg\">\"Bob\"<\/span>)<span class=\"green\">+<\/span>(<span class=\"arg\">date_range<\/span>&gt;=<span class=\"arg\">date<\/span>))<span class=\"green\">&gt;0<\/span>, <span class=\"arg\">sum_range<\/span>) ) <span class=\"green\">}<\/span>\r\n\r\nEvaluation Steps\r\nStep 1: SUM(IF((<span class=\"green\">{FALSE,TRUE,FALSE,TRUE,FALSE}<\/span>+<span class=\"green\">{FALSE,FALSE,TRUE,TRUE,TRUE}<\/span>)&gt;0,sum_range))\r\nStep 2: SUM(IF(<span class=\"green\">{0,1,1,2,1}>0<\/span>,sum_range))\r\nStep 3: SUM(IF(<span class=\"green\">{FALSE,TRUE,TRUE,TRUE,TRUE}<\/span>,sum_range))\r\nStep 4: SUM(<span class=\"green\">{FALSE,300,500,0,200}<\/span>)\r\nStep 5: <span class=\"green\">1000<\/span>\r\n<\/pre>\n<p>Using this approach, you can create multiple-criteria equivalents for MAX-IF, LARGE-IF, and other array formulas.<\/p>\n<h2 id=\"sequential\">Sequential Number Arrays<\/h2>\n<p>For many array formulas, you will need to use an array of sequential numbers like {1; 2; 3; ... n}. You can return a sequential number array from 1 to <i>n<\/i> using this formula:<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">ROW<\/span>(1:<span class=\"arg\">n<\/span>) <span class=\"green\">}<\/span>\r\n-or-\r\n<span class=\"function\"><span class=\"green\">{<\/span> =ROW<\/span>(<span class=\"function\">OFFSET<\/span>($A$1,0,0,<span class=\"arg\">n<\/span>,1)) <span class=\"green\">}<\/span><\/pre>\n<p><b>Important<\/b>: Although it doesn't matter what is contained in cell A1, if you delete the cell (by removing row 1 or column A for example), insert a row above or a column to the left of cell A1, or cut and paste cell A1 to a different location, your array formula will be messed up. To avoid this problem, use the INDIRECT function:<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(\"1:\"&amp;<span class=\"arg\">n<\/span>)) <span class=\"green\">}<\/span>\r\n-or-\r\n<span class=\"green\">{<\/span> <span class=\"function\">ROW<\/span>(<span class=\"function\">OFFSET<\/span>(<span class=\"function\">INDIRECT<\/span>(\"A1\"),0,0,<span class=\"arg\">n<\/span>,1)) <span class=\"green\">}<\/span><\/pre>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> The OFFSET and INDIRECT function are <a href=\"\/blog\/excel-formulas\/volatile-functions.html\">volatile functions<\/a>. If calculation speed becomes a problem due to these formulas, you could either use ROW(1:n) and risk having row 1 removed, or you could reference a hidden or protected worksheet using <span class=\"mono\">=ROW(Sheet4!1:n)<\/span><\/p>\n<\/div>\n<p><strong>Office 365!<\/strong> The new <strong>SEQUENCE<\/strong> function available in Office 365 makes creating sequential number arrays a lot easier. The above sequence starting with the default value of 1 is simply <strong>=SEQUENCE(<em>n<\/em>)<\/strong>.<\/p>\n<h3>Variant #1: Create a Sequence of Whole Numbers from <i>i<\/i> to <i>j<\/i><\/h3>\n<p>If you want to hard-code the values for <i>i<\/i> and <i>j<\/i> into the formula, an array formula such as ROW(4:8) may work fine to create the array {4;5;6;7;8}. If you want the formula to use cell references for <i>i<\/i> and <i>j<\/i>, you can use INDIRECT like this:<\/p>\n<pre class=\"XLformula light\"><span class=\"arg\">A1<\/span> = 4\r\n<span class=\"arg\">B1<\/span> = 8\r\n<span class=\"green\">{<\/span> <span class=\"function\">=ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(<span class=\"arg\">A1<\/span>&amp;\":\"&amp;<span class=\"arg\">B1<\/span>)) <span class=\"green\">}<\/span>\r\n\r\nResult: {4;5;6;7;8}<\/pre>\n<pre class=\"XLformula light\" style=\"background-color:#F0ECF9;border:solid 3px #C5B6E7;\">\r\nIn Office 365!\r\n<span class=\"function\">=<span style=\"color:#704AC4;\">SEQUENCE<\/span><\/span>(<span class=\"arg\">j<\/span>-<span class=\"arg\">i<\/span>+1,,<span class=\"arg\">i<\/span>)<\/pre>\n<h3>Variant #2: Create an <i>n<\/i> x 1 Vector of Whole Numbers Starting From <i>s<\/i><\/h3>\n<p>You can use this technique when you want to specify the length of the number array instead of the end value. To create the array {s; s+1; s+2; ... s+n-1} use<\/p>\n<pre class=\"XLformula light\"><span class=\"arg\">s<\/span> = 4\r\n<span class=\"arg\">n<\/span> = 7\r\n<span class=\"green\">{<\/span> =<span class=\"arg\">s<\/span>+<span class=\"function\">ROW<\/span>(<span class=\"function\">OFFSET<\/span>(<span class=\"function\">INDIRECT<\/span>(\"A1\"),0,0,<span class=\"arg\">n<\/span>,1))-1 <span class=\"green\">}<\/span>\r\n   -or-\r\n<span class=\"green\">{<\/span> =<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(<span class=\"arg\">s<\/span>&amp;\":\"&amp;<span class=\"arg\">s<\/span>+<span class=\"arg\">n<\/span>-1)) <span class=\"green\">}<\/span>\r\n\r\nResult: {4;5;6;7;8;9;10}<\/pre>\n<pre class=\"XLformula light\" style=\"background-color:#F0ECF9;border:solid 3px #C5B6E7;\">\r\nIn Office 365!\r\n<span class=\"function\">=<span style=\"color:#704AC4;\">SEQUENCE<\/span><\/span>(<span class=\"arg\">n<\/span>,,<span class=\"arg\">s<\/span>)<\/pre>\n<h3>Variant #3: Sequence of Dates Between START and END (inclusive)<\/h3>\n<p>To create an array of dates from <i>start<\/i> through <i>end<\/i> (assuming <i>start<\/i> and <i>end<\/i> are cells containing date values), remember that date values are stored as whole numbers. If they are indeed date values and not date-<i>time<\/i> values, you can use:<\/p>\n<pre class=\"XLformula light\"><span class=\"arg\">start_date<\/span> = 1\/1\/2018\r\n<span class=\"arg\">end_date<\/span> = 1\/5\/2018\r\n<span class=\"green\">{<\/span> =<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(<span class=\"arg\">start_date<\/span>&amp;\":\"&amp;<span class=\"arg\">end_date<\/span>)) <span class=\"green\">}<\/span>\r\n\r\nResult: {43101;43102;43103;43104;43105}<\/pre>\n<p>The result shows the numeric values for 1\/1\/2018, 1\/2\/2018, etc. You can format the results using whatever date format you want. If your start and end dates might be date-time values, then strip the time portion off of the number like this:<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(<span class=\"function\">INT<\/span>(<span class=\"arg\">start_date<\/span>)&amp;\":\"&amp;<span class=\"function\">INT<\/span>(<span class=\"arg\">end_date<\/span>)) <span class=\"green\">}<\/span><\/pre>\n<pre class=\"XLformula light\" style=\"background-color:#F0ECF9;border:solid 3px #C5B6E7;\">\r\nIn Office 365!\r\n<span class=\"function\">=<span style=\"color:#704AC4;\">SEQUENCE<\/span><\/span>(<span class=\"arg\">end_date<\/span>-<span class=\"arg\">starte_date<\/span>+1,,<span class=\"arg\">start_date<\/span>)<\/pre>\n<h3>Variant #4: Create an <i>n<\/i> x 1 Vector of Sequential Powers of 10<\/h3>\n<p>To create the array {1; 10; 100; 1000; ... 10^(n-1)} use<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =10^(<span class=\"function\">ROW<\/span>(<span class=\"function\">OFFSET<\/span>(<span class=\"function\">INDIRECT<\/span>(\"A1\"),0,0,<span class=\"arg\">n<\/span>,1))-1) <span class=\"green\">}<\/span>\r\n-or-\r\n<span class=\"green\">{<\/span> =10^(<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(\"1:\"&amp;<span class=\"arg\">n<\/span>))-1) <span class=\"green\">}<\/span><\/pre>\n<pre class=\"XLformula light\" style=\"background-color:#F0ECF9;border:solid 3px #C5B6E7;\">\r\nIn Office 365!\r\n=10^<span class=\"function\" style=\"color:#704AC4;\">SEQUENCE<\/span>(<span class=\"arg\">n<\/span>,,<span class=\"arg\">0<\/span>)<\/pre>\n<h2 id=\"matrices\">Formulas for Matrices<\/h2>\n<p>Excel contains some key functions for working with matrices:<\/p>\n<ul>\n<li><b>MUNIT<\/b>(m): Creates an Identity matrix of size m x m<\/li>\n<li><b>MMULT<\/b>(A,B): Uses matrix multiplication to multiply an n x k matrix A by a k x m matrix B resulting in an array of size n x m.<\/li>\n<li><b>TRANSPOSE<\/b>(A): Switches rows to columns or vice versa, and can be used for more than just numbers.<\/li>\n<li><b>MDETERM<\/b>(A): Calculates the determinant of a matrix A.<\/li>\n<li><b>MINVERSE<\/b>(A): Calculates the inverse of the matrix A (if possible).<\/li>\n<li><b>INDEX(A,n,0) or INDEX(A,0,m)<\/b>: Returns either row n or column m of matrix A.<\/li>\n<\/ul>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> Excel does a great job of displaying data, but if you need to do a lot of statistical analysis and linear algebra, other tools such as Python, R, and Matlab may be better.<\/p>\n<\/div>\n<h3 id=\"element-wise\">Element-Wise Multiplication of 2 Matrices<\/h3>\n<p>You can perform <b>element-wise<\/b> multiplication of 2 matrices by simply multiplying two ranges and entering the function as an Array Formula. For example, the formula ={1,2;3,4}*{a,b;c,d} would return the array {1*a,2*b;3*c,4*d}. If one matrix has more columns or rows than the other, those values will be truncated from the result.<\/p>\n<h3 id=\"ones\">Creating the ONES Vector and ONES Matrix<\/h3>\n<p>The ones vector <b>j<\/b>={1;1;1...} and the ones matrix <b>J<\/b>={1,1;1,1} are very useful in linear algebra and array formulas. The image below shows an example using the <b>MUNIT<\/b> function to create the Identity matrix <b>I<\/b>, the ones vector <b>j<\/b>, and the ones matrix <b>J<\/b>.<\/p>\n<p><img decoding=\"async\" src=\"\/blog\/images\/excel-formulas\/excel-identity-and-ones-matrices.png\" width=\"618\" height=\"180\" class=\"imgshadow aligncenter\" alt=\"Identity and Ones Matrices in Excel\"><\/p>\n<p>A simple way to create an <i>n x n<\/i> ones matrix (<b>J<\/b>) is to multiply the identity matrix by 0 and add 1, like this:<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =1+0*<span class=\"function\">MUNIT<\/span>(<span class=\"arg\">n<\/span>) <span class=\"green\">}<\/span><\/pre>\n<p>The ones vector (<b>j<\/b>) of size <i>n x 1<\/i> can be created by using INDEX to return the first column of the ones matrix, like this:<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">INDEX<\/span>(1+0*<span class=\"function\">MUNIT<\/span>(<span class=\"arg\">n<\/span>),0,1) <span class=\"green\">}<\/span><\/pre>\n<p>In older versions of Excel that don't support the MUNIT function, you can create the ones vector, ones matrix and identity matrix using these formulas:<\/p>\n<pre class=\"XLformula light\"><span class=\"arg\">j<\/span> =(1+0*<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(\"1:\"&amp;<span class=\"arg\">n<\/span>)))\r\n<span class=\"arg\">J<\/span> =<span class=\"function\">IF<\/span>(<span class=\"function\">ISERROR<\/span>(<span class=\"function\">OFFSET<\/span>(<span class=\"function\">INDIRECT<\/span>(\"A1\"),0,0,<span class=\"arg\">n<\/span>,<span class=\"arg\">n<\/span>)),1,1)\r\n<span class=\"arg\">I<\/span> =<span class=\"function\">IF<\/span>( <span class=\"function\">ROW<\/span>(<span class=\"function\">OFFSET<\/span>(<span class=\"function\">INDIRECT<\/span>(\"A1\"),0,0,<span class=\"arg\">n<\/span>,<span class=\"arg\">n<\/span>)) = <span class=\"function\">COLUMN<\/span>(<span class=\"function\">OFFSET<\/span>(<span class=\"function\">INDIRECT<\/span>(\"A1\"),0,0,<span class=\"arg\">n<\/span>,<span class=\"arg\">n<\/span>)), <span class=\"cgreen\">1<\/span>, 0)<\/pre>\n<pre class=\"XLformula light\" style=\"background-color:#F0ECF9;border:solid 3px #C5B6E7;\">\r\nIn Office 365!\r\nj =<span class=\"function\" style=\"color:#704AC4;\">SEQUENCE<\/span>(<span class=\"arg\">n<\/span>,,1,0)\r\nJ =<span class=\"function\" style=\"color:#704AC4;\">SEQUENCE<\/span>(<span class=\"arg\">n<\/span>,<span class=\"arg\">n<\/span>,1,0)<\/pre>\n<h3 id=\"repeating\">Repeating Rows or Columns to Create a Matrix<\/h3>\n<p>Sometimes you may need to form a matrix by repeating a row or column. This can be done using <b>MMULT<\/b> and the ones vector.<\/p>\n<p><img decoding=\"async\" src=\"\/blog\/images\/excel-formulas\/repeating-a-row-to-create-a-matrix.png\" width=\"561\" height=\"229\" class=\"imgshadow aligncenter\" alt=\"Repeating a Row to Create a Matrix\"><\/p>\n<p>If you want to create a matrix with <i>n<\/i> rows by repeating <b>row<\/b>={1, 2, 3}, use the array formula =MMULT(<b>j<\/b>,<b>row<\/b>) where <b>j<\/b> is size <i>n x 1<\/i>.<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">MMULT<\/span>( <span class=\"function\">INDEX<\/span>(1+0*<span class=\"function\">MUNIT<\/span>(<span class=\"arg\">n<\/span>),0,1), <span class=\"arg\">row<\/span>) <span class=\"green\">}<\/span><\/pre>\n<p>If you want to create a matrix with <i>k<\/i> columns by repeating <b>col<\/b>={1;2;3}, use the array formula =MMULT(<b>col<\/b>,TRANSPOSE(<b>j<\/b>)) where <b>j<\/b> is size <i>k x 1<\/i>.<\/p>\n<pre class=\"XLformula light\"><span class=\"green\">{<\/span> =<span class=\"function\">MMULT<\/span>(<span class=\"arg\">col<\/span>, <span class=\"function\">TRANSPOSE<\/span>(<span class=\"function\">INDEX<\/span>(1+0*<span class=\"function\">MUNIT<\/span>(<span class=\"arg\">k<\/span>),0,1)) ) <span class=\"green\">}<\/span><\/pre>\n<h3>ROW or COLUMN Sums using the ONES Vector<\/h3>\n<p>It turns out that the ONES vector is very important in statistics for performing a very simple matrix operation: summing the rows or columns. Let's say you have a range of size <i>n<\/i> (rows) x <i>k<\/i> (columns). You could either use the SUM function separately for each row or column, or you could use array formulas.<\/p>\n<p><img decoding=\"async\" src=\"\/blog\/images\/excel-formulas\/row-and-column-sums-using-array-formulas.png\" width=\"562\" height=\"260\" class=\"imgshadow aligncenter\" alt=\"Row and Column Sums Using Array Formulas\"><\/p>\n<p><b>Column-Sum<\/b>: To the sum the values within each COLUMN of the matrix and return the sums as a 1 x <i>n<\/i> array (or row vector), use <\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">ones_vector<\/span> =<span class=\"function\">INDEX<\/span>(1+0*<span class=\"function\">MUNIT<\/span>(<span class=\"function\">ROWS<\/span>(<span class=\"arg\">range<\/span>)),0,1)\r\n<span class=\"arg\">column_sum<\/span> =<span class=\"function\">MMULT<\/span>(<span class=\"function\">TRANSPOSE<\/span>(<span class=\"arg\">ones_vector<\/span>),<span class=\"arg\">range<\/span>)<\/pre>\n<p><b>Row-Sum<\/b>: To sum the values within each ROW of the matrix and return the sums as a <i>k<\/i> x 1 array (or column vector), use<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">ones_vector<\/span> =<span class=\"function\">INDEX<\/span>(1+0*<span class=\"function\">MUNIT<\/span>(<span class=\"function\">COLUMNS<\/span>(<span class=\"arg\">range<\/span>)),0,1)\r\n<span class=\"arg\">row_sum<\/span> =<span class=\"function\">MMULT<\/span>(<span class=\"arg\">range<\/span>,<span class=\"arg\">ones_vector<\/span>)<\/pre>\n<h3 id=\"diagonal\">Creating a DIAGONAL Matrix<\/h3>\n<p>Element-wise multiplication of matrices can be used to create a <b>Diagonal matrix<\/b>. A Diagonal matrix is a special matrix where all of the off-diagonal terms are zeros. To create the Diagonal matrix, you multiply the matrix by the Identity matrix of the same size:<\/p>\n<pre class=\"XLformula light\">Diagonal =<span class=\"arg\">A<\/span>*<span class=\"function\">MUNIT<\/span>(<span class=\"function\">ROWS<\/span>(<span class=\"arg\">A<\/span>))<\/pre>\n<p>Many programs (but not Excel) include a function like <i>diag<\/i>(<i>matrix<\/i>) which returns an <i>n<\/i> x 1 vector containing the diagonal terms of an <i>n<\/i> x <i>n<\/i> matrix. To return the diagonal as a vector, you can use the row-sum operation on the Diagonal like this:<\/p>\n<pre class=\"XLformula light\">diag(A) =<span class=\"function\">MMULT<\/span>(<span class=\"arg\">A<\/span>*<span class=\"function\">MUNIT<\/span>(<span class=\"function\">ROWS<\/span>(<span class=\"arg\">A<\/span>)),(1+0*<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(\"1:\"&amp;<span class=\"function\">ROWS<\/span>(<span class=\"arg\">A<\/span>)))))<\/pre>\n<pre class=\"XLformula light\" style=\"background-color:#F0ECF9;border:solid 3px #C5B6E7;\">\r\nIn Office 365!\r\ndiag(A) =<span class=\"function purple\">LET<\/span>(m,<span class=\"arg\">A<\/span>,d,<span class=\"function purple\">SEQUENCE<\/span>(<span class=\"function\">ROWS<\/span>(m),,1),<span class=\"function\">INDEX<\/span>(m,d,d))\r\n<\/pre>\n<h3 id=\"trace\">Find the TRACE of a Square Matrix<\/h3>\n<p>The <b>trace<\/b> of a square matrix is just the sum of the diagonal elements. Therefore, the formula for calculating the trace is just:<\/p>\n<pre class=\"XLformula light\">trace(A) =<span class=\"function\">SUM<\/span>( <span class=\"arg\">A<\/span>*<span class=\"function\">MUNIT<\/span>(<span class=\"function\">ROWS<\/span>(<span class=\"arg\">A<\/span>)) )<\/pre>\n<h2 id=\"other\">Other Array Formula Examples<\/h2>\n<h3>Linear Regression<\/h3>\n<p>The trend lines in an Excel chart allow you to do simple linear regression, but you can also do linear regression in Excel using matrix and array functions. It's much easier to just use the LINEST function, but for fun I give the general formula for calculating the <b>b<\/b> matrix (the <i>least squares estimators<\/i>) when you have the <b>y<\/b> and <b>X<\/b> matrix. Or in other words, if you want to solve for <b>b<\/b> starting from <b>y<\/b>=<b>X<\/b><b>b<\/b>, you can do that using the formula <b>b<\/b>=(<b>X<\/b>'<b>X<\/b>)<sup>-1<\/sup><b>X<\/b>'<b>y<\/b> which in Excel is:<\/p>\n<pre class=\"XLformula light\"><b>{<\/b> =<span class=\"function\">MMULT<\/span>(<span class=\"function\">MMULT<\/span>(<span class=\"function\">MINVERSE<\/span>(<span class=\"function\">MMULT<\/span>(<span class=\"function\">TRANSPOSE<\/span>(<span class=\"arg\">x<\/span>),<span class=\"arg\">x<\/span>)),<span class=\"function\">TRANSPOSE<\/span>(<span class=\"arg\">x<\/span>)),<span class=\"arg\">y<\/span>) <b>}<\/b><\/pre>\n<h3>Alternate XNPV Function<\/h3>\n<p>If for some reason you don't like Excel's XNPV function or for some reason you need to use 360 days in a year instead of 365, you can use the following array formula in place of XNPV, where <i>r<\/i> is the discount rate.<\/p>\n<pre class=\"XLformula light\"><b>{<\/b> =<span class=\"function\">SUM<\/span>(<span class=\"arg\">values_range<\/span>\/((1+<span class=\"arg\">r<\/span>)^((<span class=\"arg\">date_range<\/span>-<span class=\"function\">INDEX<\/span>(<span class=\"arg\">date_range<\/span>,1))\/365))) <b>}<\/b><\/pre>\n<h3>Running XIRR Formula<\/h3>\n<p>My <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/investment-tracker.html\">Investment Tracker<\/a> calculates an annualized compounded rate of return using a running XIRR array formula.<\/p>\n<p><!--        REFERENCES          --><\/p>\n<h3>Some References for Array Formulas in Excel<\/h3>\n<p><\/p>\n<ul class=\"bulletlist\">\n<li><a href=\"https:\/\/support.office.com\/en-us\/article\/Guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7\" target=\"_blank\" rel=\"noopener noreferrer\">Guidelines and Examples of Array Formulas<\/a> at <i>support.office.com<\/i><\/li>\n<li><a href=\"http:\/\/www.cpearson.com\/excel\/ArrayFormulas.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">Array Formulas<\/a> at <i>cpearson.com<\/i> - Some detailed information about using array formulas in Excel, along with some example array functions.<\/li>\n<li><a href=\"https:\/\/bettersolutions.com\/excel\/functions\/matrix-functions.htm\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Matrix Functions in Excel<\/a> at <i>bettersolutions.com<\/i> - Examples showing the use of MDETERM, MINVERSE, MMULT, and TRANSPOSE.<\/li>\n<li><a href=\"http:\/\/people.revoledu.com\/kardi\/tutorial\/Excel\/EigenValue.html\" target=\"_blank\" rel=\"noopener noreferrer\">Using Excel to find Eigenvalues and Eigenvectors<\/a><\/li>\n<li>A.C.Rencher, <i>Methods of Multivariate Analysis<\/i>, John Wiley &amp; Sons, Inc.: New York, 1995.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>In Excel, an array formula allows you to do some powerful calculations that you might not be able to do any other way. Learn how to use both simple and advanced array formulas, including nested IF formulas, sequential number arrays, and matrices.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[79],"tags":[],"class_list":{"0":"post-3437","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-formulas"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3437","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3437"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3437\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3437"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3437"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3437"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}