We are going to highlight 3 practical financial
modelling uses with the new Excel 2019 function MAXIFS
Our example model reports real estate holdings in USD,
which will be used to demonstrate MAXIFS.
Here is the syntax for MAXIFS.
As per the Microsoft website, the MAXIFS function will return
the "the maximum value among cells specified by a given set of conditions or criteria."
MAXIFS will derive the maximum value across our budgetary
property assets based on criteria.
Example 1 will return the largest value for
Canada-located assets in the month of January.
Max_range will be column J
because we are seeking the largest January
value based on criteria.
criteria_range1 will be the located in column F.
Criteria1 will be Canada.
The Factory asset contains the maximum asset
value of $547.5 across the Canadian assets.
Example 2 will return the maximum value of all
the Offices assets in March, which are located in
a fund greater than or equal to Fund #2.
Max_range is the column L range (March)
Criteria_range1
is the asset type range in column B.
Criteria1 is Offices.
Criteria_range2 is column H -
the Fund number that holds the asset.
Criteria2 is greater than or equal to 2.
The largest asset value in March
based on the two criteria is 1,457.0
The third and final example will apply three criteria.
will apply three criteria.
US located assets.
Assets located in only Fund #1.
And assets values less than 1,000
for the month of May.
The max_range will be column O -
the asset values for May.
The criteria_range1 is column F -
the asset location.
The United States is criteria1.
The criteria_range2 is column H -
the Fund number of the asset.
Criteria2 is 1.
Finally,
criteria_range3 is again column O,
because we want to return the largest asset
value for May, which is less than 1000.
This should return the asset value 989.6