CSE Formulas
You create array formulas by pressing the Ctrl, Shift, and Enter keys on the keyboard in succession once you’ve typed in the formula. Because of the keys pressed to create the array formula, people sometimes call them CSE formulas.
MAX IF Nested Formula Syntax and Arguments
The job of each part of the formula is:
The MAX function finds the highest result for the event chosen. The IF function allows us to choose the event by setting a condition using the event names. The array formula lets the IF function test for multiple conditions in a single cell, and, when the data meets a condition, the array formula determines what data (event results) the MAX function will examine to find the best result.
The syntax for the MAX IF formula is:
Since the IF function nests inside the MAX function, the entire IF function becomes the sole argument for the MAX function.
The arguments for the IF function are:
logical_test (required): A value or expression that is tested to see if it is true or false.value_if_true (required) The value that is displayed if logical_test is true.value_if_false (optional) The value that is displayed if logical_test is false.
In this example:
The logical test tries to find a match for the event name typed into cell D10 of the worksheet. The value_if_true argument will be, with the help of the MAX function, the best result for the chosen event. The value_if_false argument isn’t necessary in this case, and its absence will shorten the formula. If an event name that is not in the data table — such as the long jump — is typed into cell D10 it will return a zero (0).
Entering the MAX IF Nested Formula
The job of each part of the formula is:
The MAX function finds the highest result for the event chosen.The IF function allows us to choose the event by setting a condition using the event names.The array formula lets the IF function test for multiple conditions in a single cell, and, when the data meets a condition, the array formula determines what data (event results) the MAX function will examine to find the best result.
Since we are creating both a nested formula and an array formula, we will need to type it directly into a worksheet cell. Once you have entered the formula, do not press the Enter key on the keyboard or click on a different cell with the mouse as we need to turn the formula into an array formula. =MAX( IF(D2:D7=D10, E2:E7) )
