Let’s take a look. The COUNT function counts numeric values. For example, if I enter the formula =COUNT(B7:B11), Excel will return “4” because that range contains four numbers total. The COUNT function ignores blank cells and text values. The formula =COUNT(D7:E9) will return “3” because COUNT treats the date, time, and percentage as numbers. A good way to check the actual value in a cell is to temporarily apply the General format....
A boolean is a data type with only two possible values. In Excel, these are the logical values TRUE and FALSE. You’ll notice that Excel treats TRUE and FALSE in a special way. If I type the word “true” in lowercase, Excel automatically capitalizes it. The same thing happens if I type “false”. Note also that Boolean values are automatically centered in the cell. Excel actually has a function called “ISLOGICAL” that will test for the boolean values TRUE and FALSE....
where B5 contains the text to process, things is the named range E5:E9, the delimiter is a comma, and the default value is an empty string (""). See below for a detailed explanation. The first step in creating a custom function with the LAMBDA function is to verify the logic needed to solve the problem. The formula below will do the job and return the result seen in column C: This formula uses four separate functions: TEXTJOIN, FILTER, ISNUMBER, and SEARCH....
The lookup value is a so-called “big text” (sometimes abbreviated “bigtext”) which is intentionally a value “bigger” than any value that will appear in the range. When working with text, which sorts alphabetically, this means a text value that will always appear at the end of the alphabetic sort order. Since this formula matches text, the idea is to construct a lookup value that will never occur in actual text, but will aways be last....
The N function takes a value and returns a number. When given a text value, the N function returns zero. In this case, the primary formula the SUM function, and the N function simply evaluates the comment text and returns zero: The comment is visible in the formula bar when the cell is selected. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel....
In the example, we need to map five text values (statuses) to numeric status codes as follows: Since there is no way to derive the output (i.e. it’s arbitrary), we need to do some kind of lookup. The VLOOKUP function provides an easy way to do this. In the example shown, the formula in F8 is: Although in this case we are mapping text values to numeric outputs, the same formula can handle text to text, or numbers to text....
Fields The pivot table shown is based on three fields: Region, Color, and Sales: Region has been configured as a Row field, Color as a Column field, and Sales is a Value field. Data has been filtered by Region to exclude East: To force the display of items with no data, “Show items with no data” has been enabled on the Layout & Print tab of the Color field settings, as seen below:...
Fields The pivot table shown is based on two fields: Date and Color: The Color field is configured as a row field, and a value field. In the Values area, the Color field has been renamed “Count” and set to summarize by count: The Date field is grouped by Months only: To force display of months with no data, the Date field has “Show items with no data” enabled: Date filter is set to display only desired months:...
Radar charts can be used to plot the performance of employees, athletes, products, and companies in various categories. They can be used for performance evaluations and satisfaction surveys. Pros Compact presentation Can show at-a-glance strength or weakness Can handle more than one data series Cons Unusual chart type may confuse audience More difficult to read for most people Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
where tblData (B5:E15) and tblPoints (G5:H10) are Excel Tables. Background study Excel Tables (overview) What is an Excel Table (3 min. video) Introduction to structured references (3 min. video) How to use INDEX and MATCH (overview) Basic index and match approximate (example) Calculating rank The first part of the problem is to calculate the rank of each score in tblData. This is done with the RANK function like this: This is an example of a structured reference, a special kind of reference that makes formulas that deal with Excel Tables easier to work with....
The formula is a bit convoluted, but the steps are simple. We first count how many spaces exist in the text using LEN and SUBSTITUTE. Next, we use the somewhat obscure instance argument in the SUBSTITUTE function to replace the last space with a tilde (~). Finally, we use FIND to figure out where the tilde is, and the MID function to discard everything after the tilde. Working from the inside-out, we use the LEN and SUBSTITUTE functions to count how many spaces appear in the next: For the text in B5, this returns 6....
In this worksheet, we have a list of names, projects, values, and regions. This data is not sorted. Our goal is to sort the data first by region, then by name, and finally by value, with larger values first. Let’s start with the SORTBY function. The first argument, array, is the data we want to sort, in this case, all data in the range B5:E16. Now, the SORTBY function is designed to sort data by more than one dimension at the same time....
where data (B5:C16) and birthdays (C5:C16) are named ranges. The result is the list of birthdays sorted by month and day, ignoring year. Note: the SORTBY function is new in Excel 365. Below, I explain how to use a helper column to sort birthdays by month and day. To sort the data by month and then by day, ignoring year, we need to construct another array in memory that we can use for sorting....
where data is the named range B5:B14. The result is a new random sort order whenever the worksheet is recalculated. COUNTA counts the values in data, and returns the number 10, which goes into the RANDARRAY function as the rows argument. RANDARRAY returns an array of 10 decimal values like this: This array is provided to the SORTBY function as the by_array1 argument. SORTBY uses the random values to sort the data, and returns the 10 letters into a spill range starting in D5....
which returns 7, the position of the number 3 in the string “apples30”. As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the position, you can use other functions to extract what you need. In this case, we are assuming that numbers and text are combined, and that the number appears after the text....
where data (C5:E16) and group (B5:B16) are named ranges. The result is the sum of values in group “A” for all three months of data. As the formula is copied down, it calculates a sum for each group in column G. The FILTER function is a new function in Excel. See below for alternatives that will work in older versions of Excel. In the latest version of Excel, the FILTER option (#2) is easy and intuitive....
which calculates the surface area of a sphere with the radius given in column B. Units are indicated generically with “u”, and the result is units squared (u2). 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.14159265358979, accurate to 15 digits: To square a number in Excel, you can use the exponentiation operator (^): Or, you can use the POWER function: Rewriting the formula using Excel’s math operators we get: Or, with the POWER function: For example, with a radius of 5 hardcoded into the formulas we have: and both formulas return the same result....
where “data” is an Excel Table in B5:E15. The result is $30.00, the price of a Large Red Hoodie. This is an array formula, and must be entered with control + shift + enter, except in Excel 365. The VLOOKUP function does not handle multiple criteria natively. Normally VLOOKUP looks through the leftmost column in a table for a match, and returns a value from a given column in a matching row....
Let’s take a look. Here we have a worksheet that contains property listings. Let’s quickly build a pivot table without using a table. We’ll select a cell in the data, choose Pivot Table from the Insert tab on the ribbon, and accept default settings. As you can see, we get a pivot table on a new worksheet. Let’s add the Address field to get a count of all properties, then add the Status field as a Row Label....
Because the lookup result in cell D9 is empty, the final result is an empty string (""). By contrast, a standard XLOOKUP formula in cell H5 returns nothing, which displays as “0-Jan-00”. In this example, the goal is to trap an empty lookup result from the XLOOKUP function and display the result as an empty cell. For example, in the worksheet shown, the formula in cell H5 is: Because H5 is formatted as a date, and because the result comes from cell D9 (which is empty) the result from XLOOKUP behaves like zero and displays as “0-Jan-00”....
