The IFERROR function is designed to trap errors and perform an alternate action when an error is detected. The VLOOKUP function will throw an #N/A error when a value isn't found.
By nesting multiple VLOOKUPs inside the IFERROR function, the formula allows for sequential lookups. If the...Read more
In this article, we show you how to replace a complicated IF statement with a clever and compact formula based on MIN or MAX. This is a great tip any time you need to choose the smaller or greater of two values inside a formula.Read more
Important: This formula assumes that units are the last 2 characters of the text value that includes both a number and a unit of measure.
This formula works because digital units have a "power of 10" relationship. At the core, this formula separates the number part of the size...Read more
In this example, the goal is to sum total points for each student across five worksheets that all have the same structure. This can be accomplished with a 3D reference, as explained below.
Before we look at how 3D references work, let's look at the...Read more
This formula is a good example of nesting one function inside another. At the core, this formula uses the IF function to return a check mark (✓) when a logical test returns TRUE:
=IF(...Read moreIn this example, the goal is to add hours in decimal format (i.e. 1, 2, 3, etc.) to an existing Excel time. The complication is that Excel stores time as fractional values. The number 0.25 makes sense when you consider that 6 hours is one-quarter of a day, and a day in Excel equals 1....Read more
The MODE function is fully automatic and will return the most frequently occurring number in a set of numbers. For example:
=MODE(1,2,4,4,5,5,5,6) // returns 5
In the example shown, we give MODE the range B4:K4, so the formula is solved...Read more
In this example, the goal is to add minutes in decimal format (i.e., 1, 5, 10, etc.) to an existing Excel time. The complication is that Excel stores time as fractional values. The number 0.0104167 makes sense when you consider that 15 minutes is 1/96th of a day, and a day in Excel equals 1. But...Read more
In this example, the goal is to add days to a date. This is a frequent task in Excel when you need to calculate a new date by adding a specified number of days to an existing date. Here are some examples of situations where this might be useful:
In this example, the goal is to add a given number of months to a date. If the number of months is positive, the date should move forward. If the number of months is negative, the date should move backward. The standard solution for this problem in Excel is to use the EDATE function although in...Read more