BYCOL Function
The new BYCOL function is currently only available to Office Insiders, but it promises to make calculating columns easier and more efficient.
The Sales by Month table contains monthly sales figures for six products. If you wanted to calculate the total for each month, you’d likely use 12 SUM formulas (Example 1). This works as far as results, but it’s based on a decentralised approach.
Example 2 offers an alternative, which uses the CHOOSE function to group the SUM statements in a single formula, spilling them across the adjoining cells. This is better, as it reduces the risk of issues later on, however, it is rather lengthy.
Example 3 introduces BYCOL to carry out the same task — with a much shorter formula.
The syntax is:
BYCOL (𝗮𝗿𝗿𝗮𝘆, [𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻])
• 𝗮𝗿𝗿𝗮𝘆 — the array to break down by column.
• [𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻] — a LAMBDA to apply on each column.
The formula starts with BYCOL and references the sales figures in its 𝗮𝗿𝗿𝗮𝘆 argument. In [𝗳𝘂𝗻𝗰𝘁𝗶𝗼𝗻], LAMBDA holds a parameter named Sales, which is used in the SUM calculation. Each column of the sales table is totalled up to return 12 separate results.
Example 4 returns the number of products for each month that made more than £1,000. The previous LAMBDA calculation is altered to become SUM(–(Sales>1000)), which generates an array of TRUE/FALSE values that coerce into 1s and 0s, allowing the SUM function to add them up.
Example 5 asks the question: which months made more than £10,000?
This time the calculation is SUM(Sales)>10000). The whole BYCOL statement is nested in FILTER’s 𝗶𝗻𝗰𝗹𝘂𝗱𝗲 argument, with the months referenced in 𝗮𝗿𝗿𝗮𝘆. The ones that made more than £10,000 are returned, but instead of spilling horizontally, TRANSPOSE flips them around to display vertically.
Finally, Example 6 uses a similar formula to list the months that had products generating over £2,000.
