Dave Bruns

In the example shown, the formula in B11 is:

=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"<>")

Working from the inside out, the work of setting up a variable range is done by the OFFSET function here:

...Read more

To convert an expense in one time unit (i.e. daily, weekly, monthly, etc.) to other time units, you can use a two-way INDEX and MATCH formula. In the example shown, the formula in E5 (copied across and down) is :

=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4...Read more

Introduction

A key limitation of VLOOKUP is it can only look up values to the right. In other words, the column with lookup values must be to the left of the values you want to retrieve with VLOOKUP. As a result, with standard...Read more

The FORMULATEXT is fully automatic. When given the reference of a cell that contains a formula, it will return the entire formula as text. In the example as shown, the formula:

=FORMULATEXT(C5)

returns the text "=IF(B5>=70,"Pass","Fail...Read more

This formula relies on concatenation to assemble a valid location for the HYPERLINK function.

In cell D5, the link location argument is created like this:

"#"...Read more

The SORT function sorts a range using a given index, called sort_index. Normally, this index represents a column in the source data.

However, the SORT function has an optional argument called "by_col" which allows sorting values organized in columns. To sort by column...Read more

At the core, this is a normal INDEX and MATCH function:

=INDEX(array,MATCH(value,range,0))

Where the MATCH function is used to find the correct row to return...Read more

If you are new to the SUMIFS function, you can find a basic overview with many examples here.

The SUMIFS function is designed to sum numeric values based on one or more criteria. In specific cases however, you may be able to use SUMIFS to "look up...Read more

The SUMIFS function is designed to sum numeric values using multiple criteria. In the example shown, the data in the range B5:E15 shows a sales pipeline where each row is an opportunity owned by a salesperson, at a specific stage. The formula in H5 is:...Read more

The goal is to demonstrate how other formulas and functions can be nested inside the IF function. The example is a simple quantity-based discount formula.

IF function

The...Read more