Every day, thousands of questions about Excel are posted on the internet. Many of these questions go unanswered because they are unclear. This article contains some tips on how to write a question that people will quickly understand and answer if they can.Read more
In this example, the goal is to find the longest text string in the range C5:C16 that belongs to the group entered in cell F5. The group is a variable that may be changed at any time. At the core, this is a lookup problem, and the challenge is that the value we need to look up (the string...Read more
The goal is to find the longest text string in the range B5:B16. At the core, this is a lookup problem that requires creating a value (the string length) that does not exist in the data as part of the formula. The easiest way to solve this problem is with the ...Read more
The goal is to return the full address of a range or named range as text. The purpose of this formula is informational, not functional. It provides a way to inspect the current range for a named range, an Excel Table, or even the result of a formula. The core...Read more
Note: it's important to understand that Excel deals with time as fractions of a day. So, 12:00 PM is .5, 6:00 AM is .25, 6 PM is .75, and so on. This works fine for standard time and date calculations, but in many cases you'll want to convert times to...Read more
In this example, the goal is to sum values in matching columns and rows. Specifically, we want to sum values in data (C5:G14) where the column code is "A" and the day is "Wed". One way to solve this problem is with the SUMPRODUCT function...Read more
At the core, this formula uses the MID function to extract characters starting at the second to last space. The MID function takes 3 arguments: the text to work with, the starting position, and the number of characters to extract.
The text comes from column B, and the number of...Read more
Working with formulas takes time, and too often a problem that seems simple ends up taking far too long. In this article, I share tips that will save you time when working with formulas in Excel.Read more
The XNPV function returns the net present value (NPV) of an investment based on a discount rate and a series of cash flows that occur at irregular intervals. Values represent cash flows and be correspond to dates. Negative values represent cash paid out; positive...Read more
The XIRR function calculates the internal rate of return for a series of cash flows that occur at irregular intervals. Payments are expressed as negative values and income as positive values. If the first value is a cost or payment, it must be a negative value. Subsequent payments are...Read more
