In this example, the goal is to return a TRUE or FALSE result for each value in column B, based on whether it appears in the range E5:E9, which has been named "things" for convenience.
Imagine you have a list of values in the range B5:B16 and you want to check each...Read more
The SUBSTITUTE function is full automatic. All you need to do is supply "old text" and "new text". SUBSTITUTE will replace every instance of the old text with the new text.
If you need to perform more than one replacement at the same time, you'll need to nest multiple SUBSTITUTE...Read more
Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015.
Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the...Read more
In this example, the goal is to get all names in a given group into the same row, in separate columns, as seen in the worksheet. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns using common values, which in this case are the...Read more
This formula uses the NETWORKDAYS function calculate total working days between two dates, taking into account weekends and (optionally) holidays. Holidays, if provided, must be a range of valid Excel dates. Once total work days are known, they are simply multiplied by a fixed number of hours...Read more
We asked users how important Excel shortcuts are to them and also about their favorite shortcuts. This is what we learned...Read more
An interesting problem in Excel is how to look up information related to the maximum value in a set of data. For example, if you have a dataset of property listings and prices, you might want to find details about the property with the highest price. The best way to solve this problem...Read more
In this formula, the goal is to return the numeric position of the most expensive property in the list. The formula in cell I5 is:
=MATCH(MAX(C3:C11),C3:C11,0)
The MAX function extracts the maximum value from the range C3:C11. In this case...Read more
The ABS function is fully automatic. All you need to do is supply a number and ABS will return the absolute value.
If you only need to convert negative numbers once, you can convert in-place with...Read more
In this example, the goal is to answer the question "What percentage of students were absent from each class". In other words, given a class with 30 students total, 27 of which were present, we want to return 10% absent. The general formula for this calculation, where "x" is the percent absent...Read more