In this example, the goal is to return the sum for an entire row in an Excel worksheet. One way to do this is to use a full row reference.
Excel supports full row references like this:
=SUM(1:1) // sum all of row 1
=SUM(...Read moreIn this example, the goal is to extract unique values from three separate ranges at the same time: range1 (C5:C16), range2 (D5:D15), and range3 (F5:F13). At one time, this was a difficult problem, since UNIQUE is programmed to...Read more
In this example, the goal is to apply AND and OR logic to an array using the AND function and the OR function. The challenge is that the AND function and the OR function both aggregate...Read more
The VALUETOTEXT function converts a value to a text string. By default, text values pass through unaffected. However, in strict mode, text values are enclosed in double quotes (""). VALUETOTEXT will always remove number...Read more
In this example, the goal is to look up a number with a certain amount of allowed tolerance, defined as n. In other words, with a given lookup number we are trying to find a number in a set of data that is ± n. In the worksheet shown, the number to find is in...Read more
In this example, the goal to calculate the difference as a percentage between two values then check the result to see if its within a given target percentage. The values come from the Expected and Actual columns in the worksheet. The challenge is that the difference might be negative or positive...Read more
In this example, the goal is to lookup the first negative value in a set of data. In addition, we also want to get the corresponding date. All data is in an Excel Table called data, in the range B5:C16. This information...Read more
In this article, I attempt to explain why you see SUMPRODUCT so often in formulas, and when you can use the SUM function instead. The short version: SUMPRODUCT supports array operations natively, which makes it very useful for solving seemingly unrelated...Read more
Excel's formula engine has some quirks that you should be aware of. One of these quirks is that Excel will treat a text value as larger than a number by default. For example:
=90>100 // returns FALSE
="A">100 // returns TRUE
The second...Read more
In this example, the goal is to display a Happy Birthday message when a birthday in a given cell matches the current date. The core of the problem is to compare the given birthday to the current date while ignoring the year. There are two main ways to approach the problem. The first way is to...Read more