The Excel DAVERAGE function gets the average in a given field for a subset of records that match criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of...Read more
The Excel DMAX function gets the minimum value in a given field from a set of records that match criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of...Read more
The Excel DMAX function gets the maximum value in a given field from a set of records that match criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of...Read more
INDEX and MATCH is the most popular tool in Excel for performing more advanced lookups. This is because INDEX and MATCH are incredibly flexible – you can do horizontal and vertical lookups, 2-way lookups, left lookups, case-sensitive lookups, and even lookups based on multiple criteria...Read more
The Excel DCOUNT function counts matching records in a database using a specified field and criteria. The database argument is a range of cells that includes field headers, field is the name or index of the field to count, and criteria is a range of cells with headers...Read more
In this example, the goal is to calculate the maximum value of every "nth" column in a row of data, where n is a variable entered in the named range M2. This problem can be solved in several ways, as explained below. The explanation below also includes a formula...Read more
Working from the inside out, the logical criteria used in this formula is:
(data)<70
where data is the named range C5:I14. This generates a TRUE / FALSE result for every value in data, and the double...Read more
The SHEETS function returns the total number of sheets in a given reference. SHEETS takes one argument, reference, which should be a cell reference, or a 3D reference. When no references are supplied SHEETS returns the total number of...Read more
The INDIRECT function tries to evaluate text as a valid reference. In this case, the sheet name is pulled from column B and concatenated with an exclamation point and the text A1:
=B5...Read more