Posts

ODD takes just one argument, number, which should be a numeric value. With positive numbers, ODD rounds number up to the next odd integer. With negative values, ODD rounds number down away from zero to the next odd negative integer. With one (1) and numbers that are already odd integers, number is unchanged. Examples The ODD function rounds positive numbers up to the next odd integer: Negative numbers are rounded away from zero to the next odd integer: The number 1 and numbers that are already odd integers are unaffected: To round numbers up to the next even integer, see the EVEN function....

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.

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.

The WORKDAY function takes three arguments: start_date, days, and holidays. Start_date must be a valid Excel date. The days argument is the number of days in the future or past to calculate a workday. Use a positive number for days to calculate future dates, and a negative number for past dates. Holidays is an optional argument to specify non-working days. Holidays should be provided as a range that contains valid Excel dates....

You can use the YEAR function to extract a month number from a date into a cell, or to feed a month number into another function like the DATE function: See below for more examples of formulas that use the DATE function. Note: dates are serial numbers in Excel, and begin on January 1, 1900. Dates before 1900 are not supported. To display date values in a human-readable date format, apply the number format of your choice....

where amount is the named range C5:C14, and type is the named range D5:D14, and group is the named range B5:B14. where type is the named range D5:D14, and G4 is a mixed reference with the row locked in order to match the column header in row 4 when the formula is copied down. To sum by group, the range/criteria pair is: where group is the named range B5:B14, and F5 is a mixed reference with the column locked in order to match group names in column F when the formula is copied across....

When the formula is copied down, it returns an index for each column listed in column H. Getting an index like this is useful when you want to refer to table columns by index in other formulas, like VLOOKUP, INDEX and MATCH, etc. The only trick to the formula is the use of a structured reference to return a range for the table headers to the MATCH function: The nice thing about this reference is that it will automatically adjust to any changes in the table....

When the formula is copied down, it returns an name for each column, based on index values in column H. This range goes into INDEX for the array argument, with the index value supplied from column H: The result is the name of the first item in the header, which is “ID”. Although the headers are in a horizontal array, with values in columns, INDEX will use the row number as a generic INDEX for one-dimensional arrays like this and correctly return the value at that position....

As the formula is copied down, it returns the first name from each name in column B. RIGHT function The RIGHT function takes two arguments, the text itself and num_chars, which specifies how many characters to extract: For example, if we use “apple” for text and 3 for num_chars, we get “ple”: So, at a high level, this formula uses RIGHT to extract characters from the right side of the name....

First, let’s look at the problem we’re trying to solve. Here we have monthly sales data. At the moment, we only have 5 months, but we’ll be adding more data over time. Now, if I insert a column chart, everything works fine. But notice if I add new data, the chart doesn’t change. It still plots the original data. To update the chart, I’ll need to expand the range manually How can I make this chart automatically include new data?...

Here we have a simple lookup table that shows material costs for various heights and widths. The formula in K8 uses the INDEX and MATCH functions to retrieve the correct cost based on width and height values entered in K6 and K7. Note the that the lookup is based on an approximate match. Since values are in ascending order, MATCH checks the values until a larger value is reached, and then steps back and returns the previous position....

Let’s take a look. To insert a cell horizontally, select the cell to the right of where you want the new cell. Then, click the Insert menu on the ribbon and choose “Insert Cells”. When the dialog box appears, select “Shift cells right” and click OK. An empty cell appears and cells are shifted to the right. You can also use the right-click menu to insert cells. To delete a cell horizontally, first select the cell you want to delete....

Selecting columns and rows is handy when you want to move information around, delete information, or when you want to copy a row or column. Let’s take a look. To select a column in Excel, just click the letter in the column heading. You’ll see Excel immediately select the entire column. If you want to select more than one column, and the columns are together, just click a column letter and drag to expand your selection....

Let’s take a look. To move some information in Excel using drag and drop, first select the cells you’d like to move. Next, hover over the edge of the selection until you see the cursor change to a symbol with four arrows. Then, just drag the selection to a new location. When you release the mouse, the contents of the cells are moved to the new location. To copy information with drag and drop, the approach is very similar....

Let’s take a look. Here we have a table that shows the number of widgets sold over a 12-month period. There are six different kinds of widgets, so the table has six rows of data. What we want to do is show the total number of widgets sold each month in row 13, and the total number of each widget type sold for all 12 months in column O. First, let’s add a total for each month on row 13....

In this worksheet we have two tables. In the first table, we have order data. You can see that we’ve got a date, customer id, product, and total. In a second sheet we have customer data. We’ve got first and last name, street, city, state and so on. And because the id is in the first column, we can use VLOOKUP to extract all of the data in this table to the right of the id....

In the example shown, the formula in C5 is: Note that we are also using the asterisk (*) as a wildcard for zero or more characters on either side of the substrings. This is what allows COUNTIF to count the substrings anywhere in the text (i.e. this provides the “contains” behavior). Because we are getting back an array from COUNTIF, we use the SUM function to sum all items in the array....

The result is the text in B5 joined to the text in C5, separated by a space. When you use concatenation in a formula, be sure to enclose literal text in double quotes (""). Do not enclose the ampersand (&) in quotes, unless you want it to appear in the final result. With CONCATENATE You can also use the CONCATENATE function to join text like this: The CONCATENATE function keeps things tidy, but the ampersand creates shorter, more flexible formulas....

In the example shown, the formula in C4 is: In this case, holidays are supplied as the named range holidays (E4:E8), so holidays are taken into account as well. Notice that Excel only cares about holiday dates, not holiday names. Flexible weekends WORKDAY defines a weekend as Saturday and Sunday. If you need more flexibility, use the WORKDAY.INTL function instead. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

Context There may be times when you need to remove some of the variability of text before other processing. One example is when you want to count specific words inside larger text strings. Because Excel doesn’t provide support for regular expressions, you can’t construct precise matches. For example, if you want to count how many times the word “fox” appears in a cell, you will end up counting “foxes”. You can look for “fox " (with a space) but that will fail with “fox,” or “fox....