How many active months
A client of mine came to me with
this problem:
Once a year he gets a yearly activity
of items. Since the data is sent from a legacy system, Each item's activity is represented
by a 24-character string which contains only digits. Each two digits represent
a month, so we have 12 consecutive months in each data item,
as can be seen in the picture (the data are painted for better visibility)
Pic1: the raw data – each 2 digits represent a month
The problem:
We want to know, from
the year, how many months yielded an activity.
If the 2-digits are "00", then there was no activity in that month.
Otherwise, that month had an activity and should be counted.
The solution can be achieved with one
formula:
Pic 2: Solution in one formula
The formula can be better understood
by splitting it into two parts:
Part 1:
create an array of 12 horizontal cells, each cell for a month.
This can easily be accomplished with
my favourite function: SEQUENCE.
The
multiplication by 1 converts the text digits into "real" numbers.
Pic 3: Part 1 of the solution – creating a numeric array of 12 months
Part 2:
Using any method of conditional counting,
to count only non-zero numbers in the array. This can be done in several ways.
For example:
1) Using the same
formula of Part 1 and wrapping it with: SUM(IF(…..),1,0)
Pic 4: Part 2 of the solution – the same formula
2) COUNTIF with the
spilled array
Pic 5: Part 2 of the solution – two formulae
אין תגובות:
הוסף רשומת תגובה