The choose function uses the first argument to "select" remaining elements. For example, in a scheme where 1 = small, 2 = medium, and 3 = large, this formula will "map" the number 2 to "medium".
=CHOOSE(2,"small","medium","large")
In the...Read more
The goal in this example is to return the amount of time completed in a year as a percentage value, based on any given date. In other words, when given the date July 1, 2021, the formula should return 50% since we are halfway* through the year.
*By default, the YEARFRAC function...Read more
In this example, the goal is to count the number of exact matches in two ranges, ignoring the sort order or location of the values in each range. This problem can be solved with the COUNTIF function or with the MATCH function. Each approach is explained below.
Note: Both...Read more
The goal is to do something if a cell contains a given substring. For example, in the worksheet above, a formula returns "x" when a cell contains "abc". If you are familiar with Excel, you will probably think first of the IF function. However, one limitation of IF is that it does...Read more
In this example, the goal is to count the number of items sold and remaining, based on the data visible in columns B and C. The ID column holds unique ids, and the Sold column is used to record a sale. An "x" in the Sold column indicates the item has been sold. As is typical in Excel, there are...Read more
This formula builds the final result in 2 parts, joined by concatenation with the ampersand (...Read more
The WEEKNUM function takes a date and returns a week number (1-54) that corresponds to the week of year. The WEEKNUM function starts counting with the week that contains January 1. WEEKNUM takes two arguments: a date, and (optionally) ...Read more
The WORKDAY.INTL function calculates a date in the future or past that is a given number of working days from a specified start date, excluding weekends and (optionally) holidays. You can WORKDAY.INTL to calculate project start dates, delivery dates, and completion dates that must ignore non-...Read more
The NETWORKDAYS.INTL function returns the number of working days between two dates, taking into account holidays and weekends. This function is more robust than the NETWORKDAYS function because it allows you to control which days of the week are...Read more
If you need to group times into buckets that are not the same size (i.e. 12 AM-7 AM, 7 AM-12 PM, etc.) you can use the VLOOKUP function in approximate match mode.
There are several ways to group times in Excel. If you just need to group times by the hour, a pivot...Read more