![]() The difference is that instead of IF's logical test, you multiply the elements of the 1's and 0's array by the elements of the long jump results array (C2:C10) in the corresponding positions. The non-array formula works in a similar manner. As the result, the IF function "keeps" all the items in C2:C10 ( value_if_true) for which any condition is TRUE (1) the remaining items are replaced with FALSE because the value_if_false argument is not specified. In array formulas, addition works as the OR operator:Īdding up two arrays of TRUE and FALSE (which result from checking the values in B2:B10 against the criteria in F1 and H1) produces an array of 1's and 0's where 1 represents the items for which either condition is TRUE and 0 represents the items for which both conditions are FALSE. The array formula works exactly the same way as MAX IF with AND logic except that you join the criteria by using the addition operation instead of multiplication. ![]() However, we need to replace all "x" values in column C with zeros in this case because SUMPRODUCT MAX only works with numeric data: The max value with the same conditions can also be found by using this non-array formula: =MAX(IF((B2:B10=F1) + (B2:B10=H1), C2:C10))Įnter the formula by pressing the Ctrl + Shift + Enter key combination and you will get this result: With the rounds listed in B2:B10, the results in C2:C10 and criteria in F1 and H1, the formula goes as follows: Please pay attention that in the Excel language, the task is formulated differently: return the max value if round is either 2 or 3. =SUMPRODUCT(MAX((( criteria_range1= criteria1) + ( criteria_range2= criteria2)) * max_range))Īs an example, let's work out the best result in rounds 2 and 3. ![]() Goes to the SUMPRODUCT function and it outputs the max number in a cell. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |