# Exceljet

## Formulas

### Replace one character with another

The SUBSTITUTE function is full automatic. All you need to do is supply "old text" and "new text". SUBSTITUTE will replace every instance of the old text with the new text. If you need to perform more than one replacement at the same time, you'll need to nest multiple SUBSTITUTE functions. See the...Read more

### Flip table rows to columns

The TRANSPOSE function is fully automatic and can transpose cells vertical to horizontal, and vice versa. The only requirement is that there be a one to one relationship between source and target cells. In the example shown, we are transposing a table that is 2 columns by 7 rows (14 cells), to a...Read more

### Average and ignore errors

In this example, the goal is to average a list of values that may contain errors. The values to average are in the named range data (B5:B15). Normally, you can use the AVERAGE function to calculate an average. However, if the data contains errors, AVERAGE will return an error. You can see this in...Read more

### If Monday, roll back to Friday

The WEEKDAY function returns a number, 1-7, that corresponds to particular days of the week. By default, WEEKDAY assumes a Sunday-based week, and assigns 1 to Sunday, 2 to Monday, and so on, with 7 assigned to Saturday. In this case, we only want to take action if the date in question is Monday. To...Read more

### Get work hours between dates custom schedule

At the core, this formula uses the WEEKDAY function to figure out the day of week (i.e. Monday, Tuesday, etc.) for every day between the two given dates. WEEKDAY returns a number between 1 and 7. With default settings, Sunday=1 and Saturday = 7. The trick to this formula is assembling an array of...Read more

## Videos

### What's a relative reference?

In this video we explain the concept of a relative cell reference; perhaps the most important concept in Excel formulas.
Run time: 2:38

### How to chart sunrise and sunset

In this video, we'll look at how to plot average daylight hours per month in a chart, using sunrise and sunset data.
Run time: 3:49

### What is an array?

The term "array" comes from programming, but you'll hear it often in the context of more advanced Excel formulas. In this video, we'll answer the question "what is...
Run time: 2:34

### What is an array formula?

In this video, we'll answer the question: What is an array formula? The term "array formula" is probably responsible for more confusion than just about any other concept in...
Run time: 2:53

### How to use VLOOKUP to merge tables

This video shows how to use VLOOKUP to merge data in two tables based on a common ID. You can even use this technique to reorder the columns you retrieve.
Run time: 3:06

## Blog

### Why SUMPRODUCT?

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 Excel problems. In the current version of Excel, you can use the SUM instead, but SUMPRODUCT is better for backwards compatibility.

### How to concatenate in Excel

Concatenation is one of the most important operations in more advanced formulas. This article explains how to perform concatenation manually with the ampersand operator (&) and with the three Excel functions designed for concatenation: CONCATENATE, CONCAT, and TEXTJOIN.

### What is an array formula?

In the world of Excel formulas, the term "array formula" is probably responsible for more confusion than just about any other concept. This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter.

### Excel's RACON functions

There are eight functions in Excel that work differently than you might realize. The same features that were supposed to make them easier to use, can actually make them harder to use :) Read on for important information about COUNTIF, COUNTIFS, SUMIF, SUMIFS,  AVERAGEIF, AVERAGEIFS, MINIFS, and MAXIFS.