INTHEBLACK June 2022 - Magazine - Page 66
WORK SMART
// E XC E L YO U R S E L F
STORY NEALE BLACKWOOD CPA
20
YE ARS
OF E XC
YOUR SEELLF
CUSTOM
FUNCTIONS
WITHOUT MACROS
EXCEL’S NEW FUNCTIONS CONTINUE TO EXTEND AND EXPAND BOTH THE
POWER AND VERSATILITY OF EXCEL’S FORMULA CAPABILITY.
I
n my December 2020 article, I shared a
few custom functions that were created
in Excel’s programming language, Visual
Basic for Applications (VBA). Back then that
was the only way you could create your own
functions. This has recently changed with the
introduction of the LAMBDA function in the
subscription version of Excel (Microsoft 365,
formerly Office 365).
The LAMBDA function allows you to create
your own custom functions using range
names with no VBA (macros) involved.
This means the file is a standard .xlsx file.
The LAMBDA function is like the LET
function covered in previous articles. By itself
it doesn’t perform any calculations. It enables
you to capture cells and ranges as inputs and
then perform specialised calculations on those
cells or ranges and produce a result.
The beauty of this function is that you
can capture a complex calculation in a
single function and repeat that calculation
throughout the file using a descriptive
function name instead of a complex formula.
This can shorten and simplify formulas making
them easier to understand. The custom
01
66 ITB June 2022
function’s definition is centralised. If you
need to modify the formula you only need
to change it in one place.
This is a new feature and is still being
developed. I wanted to share two examples
and explain a few new features that can
assist in the use of LAMBDA. Along with the
LAMBDA function, other functions are being
added to make the Excel’s function language
behave more like a programming language.
GST EXCLUSIVE
Let’s start with a simple example. Let’s say
you have a system that provides you with
invoice totals including GST and all the
invoices include GST. You need the GST
exclusive amount. Figure 01 shows the
formula in cell C2 that calculates the GST
exclusive amount. It is based on the GST
percentage in cell H1 and the value in column
B. It is rounded to two decimal places.
We need to use this calculation throughout
the file. We can create our own function that
takes a value and returns the GST exclusive
value. We will create a range name called
GSTExclusive as our custom function.
02
Neale Blackwood CPA runs A4 Accounting,
providing Excel training, webinars and consulting
services. Questions can be sent to a4@iinet.net.au
Step 1 – Create the LAMBDA function in a cell
and test it.
The LAMBDA function is not meant to be
used in a cell, but you need to test it in a cell.
It uses a special layout to allow you to test it.
Figure 02 compares the ROUND function with
the LAMBDA function version incorporating
the ROUND function.
In the ROUND function cell B2 is the value
we need to convert. The LAMBDA will accept
that as an argument called Amount.
The (B2) on the end is the special layout that
is allowed with LAMBDA functions for testing.
This can’t be done with normal functions. This
passes the value of cell B2 to the Amount
parameter at the start of the LAMBDA function.
You can define more than one parameter. The
ROUND function uses the Amount value in
its calculation. The LAMBDA function returns
the value that the ROUND returns. Remember
LAMBA is not meant for use in cells. Things will
become clearer when you see the final solution.
Step 2 – Copy the LAMBDA function to a
range name.
In the Formula Bar use your mouse to select
and copy the LAMBDA function including the
= sign but excluding the (B2) on the end.
In the Formulas ribbon tab click the Define
Name button in the Defined Names section.
In the Name box enter GSTExclusive.
In the Refers to box paste in the LAMBDA
function. Click OK. See Figure 03.
Step 3 – Use the new range name function.
The range name can now be used like a
function throughout the file. See Figure 04.
Step 4 – Document the range name.
When you type a standard Excel function
name Excel will display the arguments