Dave Bruns

In this example, the goal is to sum the amounts in column C by quarter in column G. Column D is a helper column, and the formula to calculate quarters from the dates in column B is explained below. All data is in an Excel...Read more

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.

Full row references

Excel supports full row references like this:

=SUM(1:1) // sum all of row 1
=SUM(...Read more

In 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

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

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