Excel's array formulae are a robust and useful tool that enable FP&A experts to carry out sophisticated calculations without the need of VBA.
Excel's array formulae are a robust and useful tool that enable FP&A experts to carry out sophisticated calculations without the need of VBA (Visual Basic for Applications), including data analysis and summarization, financial model creation, and report generation.
When dealing with numerous connected variables or calculations, or when working with vast or complex datasets, array formulae can be extremely helpful. By becoming an expert at using array formulae, you can speed up your work and improve the effectiveness and accuracy of your analysis and reporting.
Using functions like SUMIFS() or SUMPRODUCT(), array formulas can be used to sum sales figures for a specific product in a given region or to sum costs for a specific department over a specific period.
To use SUMIFS for this purpose, the formula would have the following syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
For example, to sum sales values for a specific product (Product A) in a specific region (Region B), the formula could be written as follows:
=SUMIFS(D2:D10, B2:B10, "Product A", C2:C10, "Region B")
To use SUMPRODUCT, the formula would have the following syntax:
=SUMPRODUCT((criteria_range1=criteria1)(criteria_range2=criteria2)..., sum_range)
For example, to sum expenses for a specific department (Department X) over a specific time period (January-March 2021), the formula could be written as follows:
=SUMPRODUCT((B2:B10="Department X")(D2:D10>=DATEVALUE("2021-01-01"))(D2:D10<=DATEVALUE("2021-03-31")), E2:E10)
For instance, you can compute the average of the top 10 sales values for a particular product or the average of the top 5 expenses for a particular department using functions like LARGE() and AVERAGE().
The syntax of the formula would be as follows:
=AVERAGE(LARGE(value range,{1,2,...,N}))
As a result, the following formula might be used to determine the average of the top 10 sales figures for a certain product (Product A):
=AVERAGE (LARGE (IF(D2:D10),1,2,3,4,5,6,7,8,9,10="Product A",B2:B10="Product A");)
You can determine the greatest sales value for a certain product in a particular location or the lowest expense for a particular department for a particular time period using functions like MAX() and MIN().
The formula would have the following format:
=MAX(value_range * (criteria_range = criteria))=MIN(value_range * (criteria_range = criteria))
Therefore, to get the maximum sales value for a particular product (Product A) in a particular region (Region B), the formula could be:
=MAX(D2:D10 * (B2:B10="Product A") * (C2:C10="Region B"))
The formula could be expressed as follows to determine the lowest expense for a certain department (Department X) for a specific period (January–March 2021):
=MIN(E2:E10 * (B2:B10="Department X") * (D2:D10>=DATEVALUE("2021-01-01")) * (D2:D10<=DATEVALUE("2021-03-31")))
You may figure out the median sales price for a particular product in a particular location using functions like MEDIAN() and IF(), or you can figure out the median cost for a particular department during a particular time period.
The following formula could be used to determine the median sales value for a particular product (Product A) in a particular area (Region B):
=MEDIAN(IF((B2:B10="Product A")*(C2:C10="Region B"),D2:D10))
The formula might be stated as follows to determine the median expense for a certain department (Department X) for a particular time frame (January-March 2022):
=MEDIAN(IF((B2:B10="Department X")(D2:D10>=DATEVALUE("2022-01-01"))(D2:D10<=DATEVALUE("2022-03-31")),E2:E10))
You may identify the most often occurring department over a specified time period or the most frequently occurring product in a particular region by using functions like MODE() and IF().
The formula would have the following syntax in order to employ MODE() and IF() for this purpose:
=MODE(IF(criteria_range=criteria,value_range))
Therefore, to find the most frequently occurring product in a specific region (Region B), the formula could be:
=MODE(IF(C2:C10="Region B",B2:B10))
The formula could be expressed as follows to get the department that occurs the most frequently from January to March 2022:
=MODE(IF((D2:D10>=DATEVALUE("2022-01-01"))*(D2:D10<=DATEVALUE("2022-03-31")),B2:B10))
You can use an array formula to count the number of sales transactions for a given product in a certain region or the number of expenses for a specific department over a specific time period using functions like COUNTIFS() or COUNTIF().
The syntax for the formula would be as follows if COUNTIFS() were to be used for this purpose:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)
For instance, the formula could be stated as follows to calculate the total number of sales transactions for Product A in Region B:
=COUNTIFS(B2:B10, "Product A", C2:C10, "Region B")
The formula would have the following syntax in order to use COUNTIF() for this purpose:
=COUNTIF((criteria_range1=criteria1)(criteria_range2=criteria2)..., value_range)
For instance, the formula could be stated as follows to calculate the total expenses for Department X for a specified time frame (January to March 2021):
=COUNTIF((B2:B10="Department X")(D2:D10>=DATEVALUE("2021-01-01"))(D2:D10<=DATEVALUE("2021-03-31")), E2:E10)
Using functions like RANK() and IF(), array formulae can be used to rank values in a range according to specific criteria. An FP&A expert might, for instance, use an array formula to rank the sales figures for a particular product in a certain area or the costs for a particular department over a particular time period.
For this, the formula would have the following syntax, using RANK() and IF():
=RANK(value, IF(criteria_range=criteria,value_range), order)
For instance, the formula could be stated as follows to rank sales numbers for a particular product (Product A) in a particular region (Region B):
=RANK(D2, IF((B2:B10="Product A")(C2:C10="Region B"),D2:D10), 0) The formula could be stated as follows to rank expenses for a certain department (Department X) over a specific time frame (January–March 2021):=RANK(E2, IF((B2:B10="Department X")(D2:D10>=DATEVALUE("2021-01-01"))*(D2:D10<=DATEVALUE("2021-03-31")),E2:E10), 1)
These are only a few examples of how Excel's array formulae can be applied to FP&A tasks. They are a strong and flexible feature that let you evaluate data and carry out intricate calculations without requiring VBA.
Despite the fact that array formulas can be quite helpful for many different kinds of calculations, it's crucial to be aware of their constraints and potential flaws, as well as to thoroughly plan and test them before utilizing them in real tasks. FP&A professionals can master the use of array formulae to execute a variety of tasks and add value to their organization with practice and experience.
We get it. Not everyone is excited about setting up complicated Excel formulas or writing VBA code. That's where FuzionFi comes in. Created by Bryan Austin, one of the top financial analysts in the US, and his team of FP&A experts, FuzionFi is a custom VBA tool that helps you quickly create accurate forecasts by automating repetitive (and boring) tasks.
Our automations, tailored to your specific needs, handle all the hard work for you, resulting in a significant reduction in the risk of human error and faster completion of your tasks. The end result? Precise reports you can trust, promptly delivered!
