Many of you know that Excel’s MAX function will define the largest value within a range. And many of you also know that Excel’s SUMIFS function will allow you to merge IF checks into your sum formulas. But did you know that Excel has a function called MAXIFS which merges these two concepts together?
The MAXIFS function is used to determine the largest number within a range that satisfies one or more conditions.
The syntax of the function is:
=MAXIFS (max_range, criteria_range1, criteria1 [,criteria_range2, criteria2] [..])
- Max_range
- This is required
- This defines the range to search
- Criteria_range1
- This is required
- This defines the range to perform the IF check
- Criteria1
- This is required
- This defines the criteria of the IF check
- Criteria_range2 and Criteria2
- These are optional
- These allow you to have multiple IF checks
- You can have more than 2 criteria
The following example shows how to define the maximum value for Lee.
Other iterations of this formula include MINIFS, AVERAGEIFS and COUNTIFS.
Revelwood has worked with IBM Planning Analytics / TM1 for more than 27 years. We’ve partnered with hundreds of companies on the design, development, maintenance and updates of IBM Planning Analytics applications, across every industry. Have a challenge with Planning Analytics / TM1? We can help you!
Read more IBM Planning Analytics Tips & Tricks:
IBM Planning Analytics Tips & Tricks: Unhide Multiple Excel Sheets
IBM Planning Analytics Tips & Tricks: Refresh PAW Visualizations Automatically
IBM Planning Analytics Tips & Tricks: Improve Workbook Performance
