Categories
Cloud Apps

How to Flip ARRAY_CONSTRAIN Formula in Google Sheets

Previously I posted how you can use the INDEX() function to obtain the fields needed for a simple SUM() function.

Then I came across another handy Google Sheet function ARRAY_CONSTRAIN().

What ARRAY_CONSTRAIN Formula Does

There are three parameters with this formula:

  1. range – insert the range for the formula to operate on.
  2. num_rows – set the number of rows to compress.
  3. num_cols – set the number of columns to compress.

All are required fields.

Example

Let’s look at a current depreciation problem I’m trying to solve.

I’m trying to compress the different types of depreciation on an array of different purchases projected over the next few years and to calculate what the new depreciation would be once these items have been purchased.

The data looks like this:

{{< figure src=”/images/purchase-schedule-with-depreciation-life.png” alt=”depreciation schedule” caption=”Simple depreciation schedule” >}}

What we’re trying to achieve is to determine what the depreciation would be each year according to the year when items have been purchased, and their corresponding depreciation life in years.

Limitations of ARRAY_CONSTRAIN

Unfortunately the big limitation of ARRAY_CONSTRAIN is it works from the top-leftmost corner to cut the excess cells.

For example, if we apply the following formula on our data set:

=ARRAY_CONSTRAIN(C1:G6, 3, 2)

It will return for us the following highlighted cells:

Array_Constrain Top Left Cells
ARRAY_CONSTRAIN() function only returns data from top-left cell

This causes a problem for our depreciation schedule as we’d like to fetch the last two years when we’re in the 2024 column for row 3, so using the following formula:

=ARRAY_CONSTRAIN($C3:G$3, 1, B$3)

For the Network Upgrade row would only return [20000,<blank>], as these are the nearest to the top-left cells.

Workaround to ARRAY_CONSTRAIN

Is there a way to flip the input row?

There is!

Is there a REVERSE() function in Google Sheets we can apply to the range?

Unfortunately not yet.

So what we need to do as a workaround is to create our own REVERSE() function by sorting our data and reversing it backwards.

Thankfully my data already has a year row (Row 1), all I need to be able to do is sort the data set by that row, apply descending sort and then insert the new range back into the ARRAY_CONSTRAIN() formula.

Here’s how I flipped my data range:

=ARRAY_CONSTRAIN(QUERY(TRANSPOSE($C$1:G3),"SELECT Col" & ROWS($C$1:G3) & " ORDER BY Col1 DESC", 0),$B3,1)

Let’s break apart this function and understand what’s happening, as usual we’ll start with the innermost functions and work out way out:

  • ROWS($C$1:G3) – I used this to be able to get the column number to return from the QUERY function. As the last Column in the data set is the one I wanted, I just obtained this from the height of the original input range.
  • TRANSPOSE($C$1:G3) – as my data set is in rows, I need to translate this into columns for it to be functional as a data set with the QUERY() function. The data set needs to include the row containing the years.
  • QUERY(..., "SELECT Col" & ... & " ORDER BY Col1 DESC", 0) – the second SELECT parameter to our QUERY() function fetches just the one column, but orders by the first column being the column containing all the year values. The third parameter 0 just makes sure we don’t return any header rows.
  • ARRAY_CONSTRAIN(..., $B3, 1) – we finally wrap everything back into our original ARRAY_CONSTRAIN function. This time we change the parameters a little as they are in a column format, but the purposes are still the same – we want the nearest 2 years (the value of cell $B3) – and as there’s only 1 column our third parameter is 1.

To calculate depreciation on this row our final touch is to sum everything returned from the range and divide by the number of years:

=SUM(ARRAY_CONSTRAIN(QUERY(TRANSPOSE($C$1:G3, "SELECT Col" & ROWS($C$1:G3) & " ORDER BY Col1 DESC", 0), $B3, 1))/$B3

We further wrap the formula in a check to see if there are any depreciation values in the Dep’n (Years) column:

=IF(LEN($B3),SUM(...),"")

Result

Here’s the final result showing the function as well as our accumulated depreciation for the items purchased:

Calculate Depreciation Using Query & Array_Constrain
Calculating different depreciation schedules using QUERY and ARRAY_CONSTRAINT in Google Sheets

Conclusion

In this post you’ve been able to discover and learn about the ARRAY_CONSTRAINT. You’ve seen its limitations in fetching from the upper-leftmost corner, and perhaps in time Google Sheets will append an additional parameter to set where the corner should be fetched from.

In the meantime we’ve discovered how we can reverse or flip a range to make use of the ARRAY_CONSTRAINT function by using the TRANSPOSE and QUERY functions together.

Categories
Cloud Apps

How to Easily Sum Columns with Dynamic Range

I had a case in a spreadsheet where I needed to test the cash flow of payments made against future loans.

However, when it came to scenario testing the cash flow we had the ask and answer the question on the viability of the organisations cash flow depending on the loan repayment schedule.

Would we be able to repay our loans back quicker? What would our cash flow look like if repayments were 10 years, or 15 years, or 20 years’ terms?

To enable the spreadsheet to calculate this on the fly, I had to find a way whereby I could set a field value to represent the number of years the loan was to be repaid and for the single row in the report reflecting the repayment schedule to show those repayments.

For those familiar with a cash flow statement you will know there’s generally a section within the Financial Activities to detail any financial repayments made, ours was labelled Principal Repayments as shown in the basic table below of the first few columns (as we had to perform a 20-year projection there were more columns than this):

RowsABC
1Cash Flow ReportYear 1Year 2
2-8Assumptions area
9Operating Activities  
10Adjustments to Income Statement$B$10$C$10
11-19etc …
20Total Operating Activities=sum($B$10:$B$19)=sum($C$10:$C$19)
21   
22Investing Activities  
23CapEx Purchases$B$23$C$23
24-31etc …
32Total Investing Activities=sum($B$23:$B$31)=sum($C$23:$C$31)
33   
34Financing Activities  
35New Loans$B$35$C$35
36Capital Grant Receipts$B$36$C$36
37Principal Repayments$B$37$C$37
38Total Financing Activities=sum($B$35:$B$37)=sum($C$35:$C$37)
39   
40Total Net Cash Flow=sum($B$20, $B$33, $B$38)=sum($C$20, $C$33, $C$38)

Now the issue at hand was that the New Loans row was an input row. Management wanted to insert how much they were willing to borrow for each project, but they wanted to see the impact on the Total Net Cash Flow which was the sum of the Total Operating Activities plus the Total Investing Activities plus the Total Financing Activities.

So with an input field on the New Loans row, and the output field on another row Principal Repayments all I needed to do was one more input cell representing the Loan Terms (in Years).

The cell I used as the input for the loan terms is defined in cell $B$50.

So here’s how the Principal Repayments row ended up:

$B$37 = -sum(index($B$35:B$35,0,MAX(COLUMN(B$35)-COLUMN($B$35)-$B$50+1,1)):B$35)/$B$50

So let’s break this formula down, starting in the heart:

  1. MAX(COLUMN(B$35)-COLUMN($B$35)-$B$50+1, 1) this formula helps to determine the column to start our range capture.

Here’s what we would get if the formula was copied across different cells:

$E$35 = MAX(COLUMN(E$35)-COLUMN($B$35)-$B$50+1, 1)

Which for each reference and function call results in:

  • COLUMN(E$35) = 5
  • COLUMN($B$35) = 2
  • $B$35 = 10
  • MAX(5 - 2 - 10 + 1, 1) = MAX(-6, 1) = 1

For the cell $Z$35 which would contain this portion of the formula as MAX(COLUMN(Z$35)-COLUMN($B$35)-$B$50+1, 1) has the following results:

  • COLUMN(Z$35) = 26
  • COLUMN($B$35) = 2
  • $B$50 = 10
  • MAX(26 - 2 - 10 + 1, 1) = MAX(15, 1) = 15
  1. index($B$35:B$35, 0, ...) the nifty thing about the index formula is that it returns a value or the reference to a value. The parameters of the index function are: INDEX(reference, [row_offset], [column_offset]) and as we don’t want to change the row_offset this is represented as 0, but we do want to change the column_offset and this is where the MAX formula helped us. From the value achieved from the MAX formula we then receive a reference.
  2. sum(index(...):B$35) as we’ve captured the cell reference from the index function we can add the range notation : and have the sum value add all values from that cell to the current cell we are in.
  3. =-sum(...)/$B$50 lastly as we have added up all the new loans in the sum formula we then divide the total New Loans amount by the loan term.

So if we had this cash flow sheet spread out of 25 time periods out, by the time we got to the formula in $Z$37 it would look like this:

$Z$37 = -sum(index($B$35:Z$35,0,MAX(COLUMN(Z$35)-COLUMN($B$35)-$B$50+1,1)):Z$35)/$B$50

Thereby only getting us the last $B$50 (loan term) periods and dividing that amount by the loan term.

INDEX formula is a nifty little formula to learn!