Normally, SUMIFS is used with data in a vertical arrangement, but it can also be used in cases where data is arranged horizontally. The trick is to make sure the sum_range and criteria_range are the same dimensions. In the example shown, the formula in cell I5, copied down the...Read more
The #DIV/0! error appears when a formula attempts to divide by zero, or a value equivalent to zero. Like other errors, the #DIV/0! is useful, because it tells you there is something missing or unexpected in a spreadsheet. You may see #DIV/0!...Read more
The #N/A error appears when something can't be found or identified. It is often a useful error, because it tells you something important is missing – a product not yet available, an employee name misspelled, a color option that doesn't exist, etc.
However...Read more
Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers. To calculate standard deviation in Excel, you can use one of two primary functions, depending on the data set. If the data...Read more
The NUMBERVALUE function converts a text value representing a number into a valid numeric using custom decimal and group separators. You can use NUMBERVALUE to translate numbers from a locale-specific text format into a locale-independent number.
To perform a numeric conversion, the ...Read more
This formula relies on a technique called "nested IFs" to handle a series of options and results. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here.
The formula in this example is purposely more...Read more
To make a dynamic chart that automatically skips empty values, you can use dynamic named ranges created with formulas. When a new value is added, the chart automatically expands to include the value. If a value is deleted, the chart automatically removes the label.
In the chart shown, data is plotted in one series. Values come from a named range called "values", defined with the formula provided below:
Most people never receive proper Excel training and spend years frustrated by the simplest tasks, especially tasks that involve formulas. To use Excel with confidence, you must have a good understanding of formulas and functions. This article introduces the basic concepts you need to know...Read more
The Excel UNICODE function returns a number (code point) corresponding to a Unicode character given as text. The result is a number in decimal notation. For example, the Euro symbol (€) is code point 8364 in decimal notation, so UNICODE returns 8364:
...Read more
The UNICHAR function returns the Unicode character at a given code point, provided as a number in decimal format. To illustrate, the Euro symbol (€) has a code point of 8364 in decimal notation. The following formula will return the Euro character:
...Read more
