Posts

Unlike INDIRECT and OFFSET, INDEX is a non-volatile function. This means that INDEX will not recalculate whenever a change is made to a worksheet. This makes INDEX ideal for professional models and for worksheets that contain a large amount of data. However, INDEX is a complex function that takes time to understand. To keep things simple, we’ll build pieces of the final INDEX function step by step on the worksheet and then create a named range after we have the formula ready to go....

Let’s take a look. Here we have a workbook with five weeks of test scores for a group of students, and a summary sheet that’s been set up to hold test scores for all five weeks. What we need to do is create formulas on the Summary sheet that pull in the correct values from the other sheets. Referencing cells on other sheets is very similar to referencing cells on the same sheet....

When you first open a worksheet you didn’t create yourself, it may not be clear exactly where the formulas are. Of course, you can just start selecting cells, while watching the formula bar, but there are several faster ways to find all formulas at once. First, you can toggle the visibility of formulas on or off using the keyboard shortcut Control + Grave Accent. This key is just below the Escape key on US keyboards....

Let’s take a look. Sometimes you need to apply formatting to only a few characters or words. It doesn’t work to select a cell and apply the formatting, because that will affect all of the content in the cell. There are two ways to apply this kind of formatting. The first way is to select a cell and apply formatting in the formula bar. Start by selecting the text you want to format and select the format you like....

Let’s take a look. The fill handle is a special box that appears in the lower right-hand corner of a selection. When you hover over the fill handle, you’ll see the cursor change from a white selection cross to a thin black cross. When you see the black cross, click and drag the handle over the cells you’d like to fill. The fill handle can fill cells in four directions: right and left; up, down....

So, to start off, what’s a Boolean? A Boolean is a data type with only two possible values, TRUE or FALSE. You’ll often see Boolean results, or Boolean expressions in Excel. For example, if I enter the formula =B5>30 here, we’ll get the Boolean result of TRUE. This is a Boolean expression—or logical statement—that returns either TRUE or FALSE. If I copy the formula down, we’ll get a Boolean result for every number in the list....

In the example shown, the formula in F5 is: Note: this is an array formula and must be entered with control + shift + enter. In this example, we need include only values associated with group B. To do this, we use the IF function to filter: Since we are running a logical test on an range of cells, we get an array of results: {FALSE;98;FALSE;60;FALSE;95;FALSE} Note that only values in group B make it into the array....

The value in B6 is 1,234,567 and the result is 1,235,000. With the ROUND function, negative numbers for the second argument round to the left of the decimal and positive numbers round to the right of the decimal. In this case, by supplying -3, we are telling ROUND to round the number to the 3rd place on the left – the 1000’s place. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

where date (B5:B16), amount (C5:C16)), and FY (D5:D16)) are named ranges. When copied down, this formula returns sum of amounts for the fiscal years in F5 and F6 as shown, based on a fiscal start in July. Helper column To make the example easier to understand and to provide a simple way to use the SUMIF function (see below), column D is set up as a helper column that displays a fiscal year for each row, based on a July start....

Let’s take a look. In this first worksheet, we have a list of ten properties set up in a normal way. If we check the formulas that summarize this data to the right, you can see that each formula refers to a standard range of cells. So, what happens if I add some more properties to this list? Let’s try it out and see. On the data worksheet, I have data for another six properties....

The data used to plot this chart is shown below: See also: Same data in a stacked column chart. How to make this chart At this point, you can finalize the chart by setting a title, and adjusting overall chart size, font size, and colors. 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....

The result is that the range B5:B16 and range D5:D16 are joined together horizontally into a single range. The CHOOSE function The CHOOSE function is used to select arbitrary values by numeric position. CHOOSE is a flexible function and accepts a list of text values, numbers, cell references, in any combination. For example, if we have the colors “red”, “blue”, and “green”, we can use CHOOSE like this: If we give CHOOSE an array constant like {1,2}, CHOOSE will return the first and second values in an array at the same time: The result is an array that contains two values and, in the dynamic array version of Excel, these values spill onto the worksheet into the range G5:H16....

where data is the named range B5:B15. The result is 3, since there are three cells in B5:B15 that contain numbers less than zero. COUNTIF function The COUNT function counts the number of cells in a range that match the supplied criteria. For example, you can use COUNTIF like this: To count negative numbers in this example, we need to use the less than operator (<) with zero like this: To include zero in the count, use the less than or equal to operator (>=): Notice that the criteria is enclosed in double quotes ("")....

The result is a text string that contains both the name and email address enclosed in angle brackets (<>). In the worksheet shown, column B contains a name, and column C contains an email address. The formula in column D uses the ampersand character (&) to join the name and email address together: This is an example of concatenation. On the right side of the formula, the email address in C5 is wrapped in angle brackets: Notice the angle brackets are text and must be enclosed on double quotes ("")....

The first time a customer appears in the list, the formula returns 1. Subsequent occurrences return zero. Because the first reference is absolute and the second reference is relative, the range expands as the formula is copied down the column. The criteria is simply the value in the current row of column B. COUNTIFS returns the count of the current customer up to that point in the data. This means the first occurrence of a customer is 1, the second is 2, and so on....

To convert these fractional values to decimal hours, just multiply by 24. For example .5 * 24 = 12 hours, .24 * 24 = 6 hours, etc. Durations over 24 hours To calculate and display time durations over 24 hours, you’ll want to use a custom time format like [h]:mm. Otherwise, the duration will “reset” every 24 hours, in the same way that a clock “resets” each day. More on custom number formats here....

What is a formula? A formula in Excel is an expression that returns a specific result. For example: Note: all formulas in Excel must begin with an equals sign (=). Cell references In the examples above, values are “hardcoded”. That means results won’t change unless you edit the formula again and change a value manually. Generally, this is considered bad form, because it hides information and makes it harder to maintain a spreadsheet....

The MODE.MULT function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MODE ignores empty cells, text values, and the logical values TRUE and FALSE. The MODE function will accept up to 254 separate arguments. Examples In the example shown, the formula entered in D5:D9 is: In Excel 365, which supports dynamic arrays, multiple results will spill onto the worksheet automatically....

The PRODUCT function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range. All numbers in the arguments provided are multiplied together. Empty cells and text values are ignored. Examples The PRODUCT function returns the product of supplied arguments: PRODUCT multiplies all numeric values together, so the following formulas are equivalent: PRODUCT ignores text values and empty cells....

On Windows, you can sometimes use Ctrl+Alt+Tab to indent and Ctrl+Alt+Shift+Tab to un-indent. However, The application switcher in Windows 7 seems to conflict with these shortcuts. On the Mac, Ctrl+M and Cmd+Shift+Tab also work. Cmd+M should work, but it conflicts with the the Finder minimize window shortcut (see http://apple.stackexchange.com/questions/73886/disable-command-m-to-minimize-window) 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....