Here’s an important thing to keep in mind with this formula: In any argument in any formula, if the argument expects a single value but we provide an array instead, the function will calculate a value for each item in the array and then return an array with every calculated result. IF( SeqCol <= RowsA, INDEX( DataA, SeqCol, SeqRow ), SeqRow: Here, we capture one row of sequence values, where SEQUENCE counts from 1 to the total number of columns in either array.Īnd then we get to the Result. But in this case, all we needed was a column of numbers from 1 through the number of rows in the final array. …allows us to specify important characteristics about the sequence that the function returns. Its height determines the number of rows in the final array. SeqCol: The Sequence Column is the key array. RowsA & RowsB: These names capture the number of rows in each array, of course. NumCols: Because both arrays must have the same number of columns, NumCols contains the number of columns in one of the arrays. INDEX( DataB, SeqCol – RowsA, SeqRow ) ), Result ) Result, IF( SeqCol <= RowsA, INDEX( DataA, SeqCol, SeqRow ), Let’s look at those arguments one at a time. But when you want to debug the formula, you can return any other name you’ve defined in the formula.Īs you can see in the LET function’s formula below, the formula named Result relies on the values defined in earlier arguments. Usually, the named_result will be the name you defined last. That is, you set up any number of pairs of names and their values (usually returned by a formula), and then in the last argument, you specify which named value you want to return. =LET(name1, name_value1,, , named_result) By doing so, I then could wrap a LAMBDA function around it to define a simple function that I could use whenever I needed to stack two arrays in the future.īest practice for the LET function uses this syntax: I decided to use the LET function to manage the task. Here, our goal is to stack the data from DataA on top of the data from DataB and return the Final Array shown here: So let’s look at the solutions… Stacked Arrays
You can download the workbook with both solutions here.
While playing around with the arrays, I also discovered how to create one array from two arrays placed side-by-side…just as books are placed on a shelf. I searched online, but found no solution. I thought about it for a minute or two, and saw no obvious way to do it. Or, in relational database terms, I wanted to append two arrays.
To fix this problem, I would need to update the formulas in the helper table.While using Excel 365 recently, I needed to create one dynamic array that would consist of two arrays, with one stacked on top of the other. If I use it here to group sales by region instead of quarter, the chart looks fine, but the percentages are no longer correct. Whenever you create these kind of helper calculations for a chart, take care with the Switch Column/Row button. Now we have a 100% stacked chart that shows the percentage breakdown in each column.
To add these to the chart, I need select the data labels for each series one at a time, then switch to "value from cells" under label options. Now when I copy the formula throughout the table, we get the percentages we need. I need to lock this reference carefully.I want the rows to change, but columns need to stay fixed as the formula is being copied across the table. In other words, I need to divide C5 by the sum of C5 to F5.
Now to get the right percentage, I need to divide each value in the table above by the total value in the same row, which represents one quarter. To start off, I'll copy and paste the whole table and remove the values. What this means is that we need to build our own formulas to calculate percentages, then pull these results into the data labels.
Unlike a pie chart, which has a specific option to show percentages, a 100% stacked chart does not have this option.īut there is an option to pull values from other cells. This isn't hard to do, but it does take a little prep work. Looking at the chart, you might wonder how to show the actual percentages in each bar? The result is a chart that shows a proportional breakdown of each quarter by region. In a previous video, we built a 100% stacked column chart, and added data labels to show actual amounts in an abbreviated custom number format.