In this example, the goal is to convert negative numbers in column B to zero and leave positive numbers unchanged. Essentially, we want to force negative numbers to zero.
The MAX function provides an elegant solution...Read more
Most of the work in this formula is done by the TEXT function, which applies a custom number format for hours and minutes to a value created by subtracting the start date from the end date.
TEXT(C5-B5,"h"" hrs...Read moreThe 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