The goal is to calculate a tax amount with both fixed and variable components according to the following logic:
In this example, the goal is to look up a given income value in a tax table and return the correct tax rate for that income. The tax rate is organized into 5 tiers in the range F5:F9 with the corresponding tax rate in the range G5:G9. For convenience, the range F5:G9 is named ...Read more
The first step is to construct a standard "A1" style reference using the column letter, by adding a "1" with concatenation:
B5...Read moreIn this example, the goal is to convert an ordinary number into a column reference expressed in letters. For example, the number 1 should return "A", the number 2 should return "B", the number 26 should return "Z", etc. The challenge is that Excel can handle over 16,000 columns, so the number of...Read more
In this example, the goal is to count the value in cell B5 ("Steven") in the sheets listed in B11:B13. The workbook shown in the example has four worksheets in total. The first sheet is named "Master" and contains the search string, the range, and the sheets to include in the...Read more
The range B7:B9 contains the sheet names we want to include in the search. These are just text strings, and we need to do some work to get them to be recognized as valid sheet references.
Working from the inside out, this expression is used to build a full sheet reference:
...Read more
The second sheet in the workbook, Sheet2, contains 1000 first names in the range B4:F203.
...Read more
This formula is a standard version of INDEX + MATCH with a small twist.
Working from the inside out, MATCH is used find the correct row number for the value in F4, 2100. Without the third argument, match_type, defined, MATCH defaults to approximate match and returns 2.
The small...Read more
In this example, the goal is to count rows in a set of data using multiple criteria and "not equals to" logic. Specifically, we want to count males that are not in group A or B. All data is in an Excel Table named data in the range B5:...Read more
