=AVERAGEIFS(C3:C15, A3:A15, F3, B3:B15, F4) AVERAGEIFS with logical operators With criteria in predefined cells (F3 and F4), the formula takes this form: Putting the arguments together, we get the following formula: Criteria_range2 is B3:B15 (Regions to check) and criteria2 is "north".Criteria_range1 is A3:A15 (Items to check) and criteria1 is "apple".Average_range is C3:C15 (cells to average).For this, we make an AVERAGEIFS formula with two criteria: To get an average of numbers in one column if another column(s) contains certain text, use that text for criteria.Īs an example, let's find an average of the "Apple" sales in the "North" region. An AVERAGEIFS formula should always contain an odd number of arguments: average_range + one or more criteria_range/criteria pairs.The pairs can be arranged in any order, but the criteria always follows the range it applies to. In subsequent arguments, specify range/criteria pairs.In the first argument, supply the range that you want to average.To construct an AVERAGEIFS formula correctly, please follow these guidelines: Now that you know the theory, let's see how to use the AVERAGEIFS function in practice.įirst, let us outline the generic approach. Each criteria_range must be of the same size and shape as average_range, otherwise a #VALUE! error occurs.AVERAGEIFS' criteria may apply to the same range or different ranges.If no cells meet all of the specified criteria, a #DIV/0! error is returned.If average_range doesn't contain a single numeric value, a #DIV/0! error occurs.If criteria is an empty cell, it is treated as a zero value.In the average_range argument, empty cells, logical values TRUE/FALSE, and text values are ignored.To get a clear understanding of how the function works and avoid errors, take notice of the following facts: To calculate cells for which any single condition is TRUE, use the AVERAGE IF OR formula. only those cells are averaged for which all the conditions are TRUE. The AVERAGEIFS function works with the AND logic, i.e.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |