Page 39 - Finanancial Management_Feb_Apr23

The second part, When used in mathematical operations, TRUE behaves
like one [1] and FALSE behaves as if it were zero [0]. These
COUNTIF(Example3,Example3&””) results are then summed together to give us six [6]. Easy
when you know how.
uses one of the more unusual ways of using COUNTIF. Again, The advantage of this approach is that it neither requires
it returns an array, but this time each value in the array dynamic arrays nor data refreshing. The problem is the
represents a count of the numbers in the array using each calculation is a little opaque. No solution is perfect, but this
value of the array as a criterion (the &”” addendum merely final option may prove to be the best all-rounder.
coerces the value to a text string, which may be required in
certain instances). This results in: Word to the wise
Some of you may be surprised that I did not use Power
3, 2, 1, 3, 2, 1, 2, 2, 3, 1 Query/Get & Transform as one of the options above, since
removing duplicates is a base transformation in the Power
ie, there are three [3] instances of “a”, two [2] of “b”, and so on. Query Editor. For the arbitrary purposes of this article,
The numerator is then divided by the denominator on an item- I merely wanted to consider basic Excel features and
by-item basis to give us: functions.
Indeed, Get & Transform may be used, too. It is a great
0.33, 0.5, 1, 0.33, 0.5, 1, 0.5, 0.5, 0.33, 1 method for cleansing data where there may be surplus
spaces (trimming), nonprintable characters (cleaning), or
Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a preponderance of haphazard upper/lowercase lettering.
a global consultancy specialising in Excel training. He is However, similar to the PivotTables solution detailed above,
also an Excel MVP (as appointed by Microsoft) and author of this requires data to be refreshed in order to be updated,
Introduction to Financial Modelling and Continuing Finan- which many end users forget. Therefore, given the “behind
cial Modelling. Send ideas for future Excel-related articles the scenes” nature of this option, I chose to discount it in
to him at liam.bastick@sumproduct.com. To comment on this instance. Nonetheless, this extract/transform/load (ETL)
this article or to suggest an idea for another article, contact tool should be viewed as an essential tool in every modeller’s
Oliver Rowe at Oliver.Rowe@aicpa-cima.com. armoury. It’s merely a case of knowing which may work best
when. n