Posts

Syntax TRUE() There are no arguments for this function. Example In this example, we return the TRUE value.

which calculates the volume of a sphere with the radius given in column B. Units are indicated generically with “u”, and the result is units cubed (u3). Where r represents radius, and 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 cube 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: The result is the same for both formulas....

Where “holidays” is the named range E3:E13. For example, B4 contains January 1, 2014, but displays only “Jan” per the custom number format. The formula itself is based on the NETWORKDAYS function, which returns the number of working days between a start date and end date, taking into account holidays (if provided). For each month, the start date comes from column B and the end date is calculated with the EOMONTH function like so: EOMONTH takes a date and returns the last day of a month....

An Excel cell can contain many different data types such as text, numbers, dates, Booleans, and error values. Until recently, images have not been a data type that you could insert into a cell. Previously, you could only place the image above the cell and then change the image properties so it moves and resizes with the cells. There are new features available in Excel that allow you to add images directly inside a cell....

For some reason, your VLOOKUP‘s are returning an #N/A error and you have no idea why. Everything looks good and the formula should be working. But for some unknown reason, a handful of results are errors. The first time this happened to me, I spent ages trying to find the problem only to discover some of the lookup data had extra space characters attached at the end! This happens a lot....

Adding a new column based on the joining of two columns is a basic task in data analysis. In data entry forms, most of the time the developer will keep two fields for first and last name instead of the full name. It helps to avoid confusion, is easier to find, and will be very useful for further data analysis. Suppose you are creating a report where you need to display a customer’s name....

All Excel tables are composed of rows and columns, and share a number of common elements. First, there’s the table itself, which is a rectangular range of cells with a unique name. You’ll see the table name on the design tab, and in the name box menu. The data range in a table is dynamic. As rows are added or removed, Excel will keep track of these changes. All tables contain three primary parts - the header row, the data in the table, and the total row....

Sorting with formulas is one of those traditionally hard problems in Excel that new dynamic array formulas have made much easier. In this worksheet, we have a list of names, scores, and groups. Currently the data is not sorted. Our goal is to sort this data in descending order by score. There are two new functions in Excel for sorting: SORT and SORTBY. Either function will work in this case, but we’ll use the SORT function....

No need to enter 1 for num_chars in LEFT, since it will default to 1. The second expression extracts the remaining characters with MID: The text comes from B5, the start number is hardcoded as 2, and num_chars is provided by the LEN function. Technically, we only need to extract (length - 1) characters, but MID won’t complain if we ask for more characters, so we’ve left things in the simplest form....

In this example, a conditional formatting rule is set up to highlight cells in the range C5:G15 when then are greater than the value entered in cell J6. The formula used to create the rule is: The rule is applied to the entire range C5:G15, and the value in J6 can be changed at any time by the user. When a new value is entered, the highlighting is immediately updated....

To extract the time, the formula in D5 is: To assemble a datetime, the formula in E5 is: Date To get the date, we extract the first 10 characters of the value with the LEFT function: The result is text, so to get Excel to interpret as a date, we wrap LEFT in DATEVALUE, which converts the text into a proper Excel date. Time To get the time, we extract 8 characters from the middle of the text with the MID function: Again, the result is text....

The result is 4, since there are four cells in B5:B15 that contain five characters. SUMPRODUCT with LEN One way to solve this problem is to use the SUMPRODUCT function with the LEN function. In the example shown, the formula in E6 is: Working from the inside out, the LEN function is used to get the length of each value in the range like this: Since the range B5:B15 contains 11 cells, LEN returns 11 results in an array like this: Each number in the array is the length of a cell in B5:B15....

Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365. They key to the formula is knowing that the FREQUENCY function gathers numbers into “bins” in a particular way. Each bin represents an upper limit, and contains a count of all numbers in the data set that are less than or equal to the upper limit, and greater than the previous bin number....

which returns 4, since there are 4 unique employee ids in the list. The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of numeric values, organized in “bins”. We use it here as a roundabout way to count unique numeric values. Working from the inside-out, we supply the same set of numbers for both the data array and bins array to FREQUENCY: FREQUENCY returns an array with a count of each numeric value in the range: The result is a bit cryptic, but the meaning is 905 appears four times, 773 appears two times, 801 appears once, and 963 appears three times....

Because we provide 10 values for criteria, we get back an array with 10 results like this: Each number represents a count – “Jim” appears 3 times, “Sue” appears 2 times, and so on. This array is configured as a divisor with 1 as the numerator. After division, we get another array: Any values that occur in just once in the range will appear as 1s, but values that occur multiple times will appear as fractional values that correspond to the multiple....

In the summary table in F3 to I7, we have the 4 names that appear in the data log in rows, and 3 courses we want to track as column headers. Note names and courses match entries in the data log exactly. The core of the formula is the COUNTIFS function, which is configured with 2 range/criteria pairs. The first pair matches on the named range “name” (K5:K11) with criteria coming from $F4 (with column locked to allow the formula to be copied across the table)....

Syntax DATE(Year, Month, Day) Year (required) – This is the year number from 1900 to 9999.Month (required) – This is the month number from 1 to 12.Day (required) – This is the day number from 1 to 31. Example In this example we create a few dates by reference to a year, month and day. Notice that if we use a month greater than 12 or a day greater than the actual number of days in the month, then the result rolls over into the next month or year....

The attached Excel workbooks include a working query, and each query returns data to an Excel Table. You can refresh the data by right-clicking in the table and selecting “Refresh”. When data has changed, you will see more recent data appear. If you’re new to Power Query, this article explains how to build a single query in more detail. Requirements This project depends on Power Query, so you’ll need Excel 2013 or later on Windows....

How It Works The shift_amount can either be positive or negative. If a negative number is provided, the bits are shifted to the left instead. 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. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Method DAYS360 takes an optional argument called method that can be set to either TRUE or FALSE. When method is FALSE (default) DAYS360 uses a US method to compute days. When the start date is the last day of the month, it is treated like the 30th day of that month. When the end date is the last day of the month, and the start date is less than 30, the end date is treated as the 1st of the next month, otherwise the end date is treated like the 30th of the same month....