Ever needed to calculate someone's retirement date? Or figure out when a 30-year mortgage will end? Or maybe you're setting a contract expiration date? In each case, you need a way to add years to a date.
Here's something that might surprise...Read more
In this example, the goal is to calculate the percentage sold for each item listed in the table, where original number (Total) is in column C and the Sold number is in column D. In other words, if we know we started with 144 apples, and sold 108 apples, we want to calculate that 75% of the...Read more
In this example, the goal is to calculate a retirement date at age 60, based on a given birthdate. The simplest way to do this is with the EDATE function. The EDATE function will return a date n months in the future or past when given...Read more
Working from the inside out, this part of the formula searches each cell in B4:B11 for all values in the named range "things":
--ISNUMBER(SEARCH(things,B4)
The SEARCH function returns the position of the value if found, and the #VALUE error...Read more
Note: this formula uses the named range "data" (B5:E104) for readability and convenience. If you don't want to use a named range, substitute $B$5:$E$104 instead.
To pull a random value out of a list or table, we'll need a random row number. For that, we'll use the RANDBETWEEN...Read more
In this example, the goal is to highlight the 5 bottom values in B4:G11 where the number 5 is a variable set in cell F2.
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don't want to use named ranges, make...Read more
This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don't want to use named ranges, make sure you use absolute references for both of these ranges in the formula.
This formula is based on the...Read more
Although Excel ships with many conditional formatting "presets", these are limited. A more powerful way to apply conditional formatting is with formulas, because formulas allow you to apply rules that use more sophisticated logic. This article shows 10 examples, including how to highlight rows,...Read more
In this example, the IFERROR function is used to trap and suppress the #DIV/0! error that occurs when there is no value for Orders (column D). Without IFERROR, the formula C5/D5 would display a #DIV/0! error in E6 and E9.
The IFERROR function takes two arguments: a value (usually entered...Read more
The INDIRECT function tries to evaluate text as a worksheet reference. This makes it possible to build formulas that assemble a reference as text using concatenation, and use the resulting text as a valid reference.
In this example, we have Sheet...Read more