INTHEBLACK November 2021 - Magazine - Page 75
CLICK HERE TO ACCESS
extra material, a podcast
and a video from
Neale Blackwood CPA
03
SORT(t,2,-1)
LET FUNCTION
RECOMMENDED DISPLAY
When creating long LET functions like these, it
can be helpful to display the formula as shown
in Figure 04.
This defines the variables on separate lines
at the top of the formula, and the output is
the last entry.
To enter the line breaks in the Formula Bar,
use Alt + Enter.
This example shows how powerful Excel’s
dynamic array functions are and how useful
the LET and CHOOSE functions can be in
solving difficult problems and avoiding
circular references.
04
used as the first argument in the CHOOSE
function, array syntax can create a table
structure. See example below in Figure 03.
=CHOOSE({1,2,3,2},"First","Second","Third")
This formula creates a horizontal list of four
entries based on the four numbers between
the braces.
Used by itself, this formula spills across the
columns to the right.
We used the following CHOOSE function as
part of the solution.
CHOOSE({1,2},u,v)
This creates a table with the unique
department codes (u) in the first column and
the corresponding summed values (v) in the
second column. We captured this table in the
t variable. This solves the problem we had
in columns F and G, where we had two
separate lists.
In cell M2 I have displayed the virtual table
held in the t variable. We now have a twocolumn table that we can use with the SORT
function.
In the final formula in cell P2, we take this
table (t) and sort it by the second column (2)
and in descending order (-1).
THINGS TO NOTE
• By using a formatted table as the data
source, you automatically include any
new rows added in the calculation.
• The MAX function in cell G1 extracts the
latest date from the data.
• The UNIQUE function identifies any new
departments in the data and lists them.
• The CHOOSE function combines lists
together to create a table.
• The SORT function updates automatically
based on the latest values.
TABLE 01
TABLE 02
intheblack.com November 2021 75