INTHEBLACK July 2022 - Magazine - Page 59
CLICK HERE TO ACCESS
01
TABLE 01
02
Extra material, a podcast
and a video from
Neale Blackwood CPA
A solution has been provided by the new
function BYCOL, which allows you to access
each column within a two-dimensional range.
BYCOL requires the use of the LAMBDA
function as the second argument to perform
a calculation.
The formula for cell B18 that spills across and
sums all the columns is
=BYCOL(B14#,LAMBDA(x,SUM(x)))
BYCOL extracts each column from a
range and transfers it to the x variable in the
LAMBDA function. The SUM function within
the LAMBDA function uses that x range and
adds up the column. Because B14# is used,
the formula spills across to match each
column of the spill range. Each column is
summed separately.
There is a new BYROW function as well. It
also requires LAMBDA as its second argument.
The total formula for cell O14 is
=BYROW(B14#,LAMBDA(x,SUM(x)))
This spills down and adds up the row ranges
within the two-dimensional spill range. Each
row within the spill range is transferred to the
LAMBDA and SUM functions to perform the
calculation.
DYNAMIC BY NATURE
The beauty of dynamic arrays is that they
automatically expand as required. If we add
a new Gizmo product to the table at the
top of sheet, the dynamic array formulas all
automatically expand to include it. See
Figure 02.
In the past, the SUM solution was the
missing piece of the total dynamic array
solution. That has now been solved.
If we need to add even more products, we
only need to insert extra blank rows below
the table and within the budget section – a
simple procedure.
The companion video will cover all the
examples above, and I will share three
custom functions to make the process
even easier. There is a companion file you
can download with all the examples and
formulas.
intheblack.cpaaustralia.com.au July 2022 59