Posts

Explanation In this example, the goal is to get all names in a given group into the same row, in separate columns, as seen in the worksheet. This is sometimes referred to as a “pivot” operation. The idea is to restructure the data into multiple columns using common values, which in this case are the group names. The explanation below includes two options: (1) a modern approach based on the FILTER function and (2) a legacy approach based on INDEX and SMALL for older versions of Excel without the FILTER function....

Pivot tables make it easy to count values in a data set. One way this feature can be used is to display duplicates. In the example shown, a pivot table is used to show duplicate cities in an Excel Table that contains more than 250 rows. Fields The data contains 263 rows, each with a City and Country. The pivot table shown is based on just one field: City, which has been added as both a Row field and a Value field:...

Purpose Return value Syntax =RANDBETWEEN(bottom,top) bottom - An integer representing the lower value of the range. top - An integer representing the upper value of the range. Using the RANDBETWEEN function The RANDBETWEEN function returns a random integer between two numbers. The result from RANDBETWEEN is automatic, and a new random number will be recalculated each time a worksheet is opened or changed. RANDBETWEEN is a volatile function , and can cause performance issues in large or complex worksheets....

The concept of a cell range is one of the most important ideas in Excel. A range is a rectangular group of cells. For example, you can refer to the first 10 cells in column A with a range like this: =A1:A10 // first 10 cells in column A In Excel, the colon (:) is a range operator , and separates the first reference from the last reference in a range....

Explanation Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values. The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just one array, it simply adds up the items in the array and returns the result. Logically, any result greater than zero means that at least one value exists in the range....

A relative reference in Excel is a pointer to a cell or range of cells. For example, a relative reference to cell A1 looks like this: =A1 A relative addresses will change when copied to other location in a worksheet because it describes the “offset” to another cell, rather than a fixed address. To help understand what this means, consider the phrase “the house next door to the right”. You can only understand the location of this house if you understand the starting point, because the location is described in relative terms....

Transcript In this video we’ll look at how to simplify some formulas we created in a previous video by replacing IF statements with the MIN function and a bit of Boolean logic. Make sure you watch the first video if you haven’t already. The calculations for both tiers use one or more IF statements, and the second formula is a bit complicated. Let’s look at how we can simplify these formulas....

Explanation The goal in this example is to sum a range of Roman numbers. The challenge is that Roman numbers appear as text in Excel, not numeric values. If you try to use the SUM function to sum a range of Roman numbers directly, the result is zero (0). The solution is to use the ARABIC function to convert the Roman numbers to regular numbers, then sum the result. The ARABIC function takes a valid Roman number and returns its Arabic equivalent....

Purpose Return value Syntax =SUMIFS(sum_range,range1,criteria1,[range2],[criteria2],...) sum_range - The range to be summed. range1 - The first range to evaluate. criteria1 - The criteria to use on range1. range2 - [optional] The second range to evaluate. criteria2 - [optional] The criteria to use on range2. Using the SUMIFS function The SUMIFS function sums numbers in Excel when they meet one or more specific conditions. SUMIFS is one of Excel’s most widely used functions, and you will see it in all kinds of spreadsheets that calculate conditional sums based on dates, text, or numbers....

Explanation The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. For example, if we give SUMPRODUCT one array in the form of the data[Qty] column: =SUMPRODUCT(data[Qty]) // returns 54 SUMPRODUCT returns 54, the total of all numbers in the Qty column, like this: =SUMPRODUCT(data[Qty]) =SUMPRODUCT({9;6;4;8;5;1;7;5;4;2;3}) =54 What we need now is a way to “filter” these values to include only the value associated with “Red”, respecting case....

Explanation In geometry, a sphere is defined as the set of points that are all the same distance (r) from a given point in a three-dimensional space. The formula for calculating the surface area of a sphere is: The Greek letter π (“pi”) represents the ratio of the circumference of a circle to its diameter. In Excel, π is represented in a formula with the PI function , which returns the number 3....

Above: Tracking public COVID-19 testing data in Excel with Power Query In this article, I want to share a quick example of how to track testing for COVID-19 using Excel and publicly available data. This is a bare bones tutorial, focused only on the basics of connecting Excel with publicly available data. The end result is a simple Excel table that shows the most recent testing data by state. The data is fetched and “shaped” with Power Query , then dropped back into Excel, where it can be refreshed with a single click....

Explanation In this example the goal is to check if values in column B are within a tolerance of .005. If a value is within tolerance, the formula should return “OK”. If the value is out of tolerance, the formula should return “Fail”. The expected value is listed in column C, and the allowed tolerance is listed in column D. The solution is based on the IF function together with the ABS function....

Purpose Return value Syntax =VAR.P(number1,[number2],...) number1 - First number or reference. number2 - [optional] Second number or reference. Using the VAR.P function The VAR.P function calculates variance for data that represents an entire population. Variance measures how far a data set is spread out, giving you a general idea of the spread of your data. The VAR.P function can accept up to 254 arguments. Variation functions in Excel The table below summarizes the variation functions available in Excel....

Transcript What’s a formula? In Excel, a formula is simply an expression that begins with an equal sign. A formula can be as simple as 1 + 1 or 2 + 2. However, what gives Excel so much power is that a formula can reference cells. When values in cells that have been referenced change, Excel will recalculate the formula automatically and display a new result. Let’s take a look....

Introduction Excel offers a vast number of functions that cater to different users and their unique requirements. Among these functions, VLOOKUP has long been the go-to choice for basic lookups in a table or range. In almost every industry, millions and millions of existing spreadsheets use VLOOKUP to do something useful. However, with the introduction of XLOOKUP in 2019, Excel users have a powerful new lookup option available. XLOOKUP can do everything VLOOKUP can do, and much more....

Explanation In older versions of Excel (before Excel 2016?), the character used for line breaks is different depending on whether Excel is running on a Mac or Windows computer: On Windows Excel, the line break character is ASCII 10. In older versions of Excel on a Mac, the line break character is ASCII 13. These are invisible characters and therefore difficult to enter directly into a formula. The standard way to insert them in a formula is to use the CHAR function like this:...

Explanation When working with Excel, there are times when you need to determine if a range of cells is empty. This can be useful in various scenarios, such as data validation, error checking, or report preparation. In this article, we’ll explore a couple of formulas that can help you check if all cells in a given range are empty. The first and simplest formula is based on the COUNTA function, which will count numbers and text values in cells....

An array constant is a hard-coded set of values provided in an Excel formula. Array constants appear in curly braces {} like this: {"red","blue","green"} Array constants are often used in array formulas to create or manipulate several values at once, rather than a single value. For example, the LARGE function can be used with an array constant like this to get the top 3 values in B3:B11: =LARGE(B3:B11,{1,2,3}) In many cases, formulas that use array constants do not require Ctrl+Shift+Enter, even though they are in fact array formulas....

Purpose Return value Syntax =ASINH(number) number - The number to get the inverse hyperbolic sine of. Using the ASINH function The Excel ASINH function returns the inverse hyperbolic sine of a number. Given one as input, the function returns 0.881373587 as output. =ASINH(1) // returns 0.881373587 Explanation The inverse hyperbolic sine returns the hyperbolic angle of the point on the unit hyperbola with a y -coordinate equal to the input. For example, given one as input representing the point on the unit hyperbola with a y -coordinate of one, the inverse hyperbolic sine returns the hyperbolic angle of 0....