INTHEBLACK November 2021 - Magazine - Page 74
WORK SMART
// E XC E L YO U R S E L F
STORY NEALE BLACKWOOD CPA
USING LET TO
SOLVE A DIFFICULT
PROBLEM
THE LET FUNCTION OFFERS USEFUL SOLUTIONS
TO TRICKY EXCEL CHALLENGES.
I
recently received an interesting query from
a reader who created a list of departments
and full-time equivalent (FTE) values
using the UNIQUE function and the SUMIFS
function and wanted the final report sorted in
descending order based on the FTE numbers,
without using any helper cells.
The reader tried using the SORT function
on the Department column (created using the
UNIQUE function), but that only sorted the
department in alphabetical order. The snapshot
of data and the current report structure are
shown in Figure 01, using a formatted table in
columns A to D, named tblFTE.
The formulas for the three cells in Figure 01
are listed and explained in Table 01.
01
02
74 ITB November 2021
I initially wanted to use the new SORTBY
function, which sorts one column based on
entries in another column. However, that
created a circular reference between the FTE
column and the Department column. The
problem is that column F and column G have
two separate formulas, with column G, which
I needed to sort, linking back to column F.
Figure 02 shows the progression of formulas
I used to arrive at the solution to this problem,
without the use of helper cells. I recreated the
existing report using LET and then managed
to combine the two separate formulas into one
table. The solution formula is in cell P2.
Table 02 lists the four formulas involved.
These are all dynamic array formulas that spill
Neale Blackwood CPA runs A4 Accounting,
providing Excel training, webinars and consulting
services. Questions can be sent to a4@iinet.net.au
based on their size. I will explain and discuss
them below.
The first two formulas in Table 02 are the
building blocks of the solution.
Cell I2 recreates the cell F2 formula using
a LET function. The variable u holds the list
of unique departments. Having the variable
u as the last argument allows you to list all
the entries in the variable. This technique is
repeated in the formulas that follow.
Cell K2 recreates the cell G2 formula using
a LET function. The variable v holds the
summed values associated with each unique
department held in variable u. Note that
the SUMIFS uses the u variable as its last
argument.
Cell M2 creates a table that matches
the original report. The CHOOSE function
combines the two variables u and v into a
single table, held in the variable t.
CHOOSE FUNCTION
This function offers an alternative to multiple
IF functions and works in a sequential manner
best explained with some examples.
The first argument of CHOOSE is a whole
number. The remaining arguments are
a sequential list of entries separated by
commas. One of these will be returned based
on the number in the first argument.
=CHOOSE(3,"First","Second","Third")
This formula returns the text Third. If the
first argument in the CHOOSE were 4, this
formula would return an error, because
there are only three arguments after the first
argument.
Assume cell A1 contains the date 1/7/2021.
=CHOOSE(MONTH(A1),3,3,3,4,4,4,1,1,1,2,2,2)
This formula will return 1, which is the
seventh argument after the first argument.
The MONTH function returns the calendar
month, 7 in this case. The above CHOOSE
function converts the calendar month number
(7) from cell A1 into the Financial Year quarter
number (1).
CHOOSE MAGIC
We can use array syntax in the first argument
to combine entries. Array syntax has entries
separated by commas between braces (curly
brackets). Array syntax allows you to use more
than one value as the first argument. When