Dave Bruns

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:

• Calculate a due date by
...Read more

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

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