Posts

Purpose Return value Syntax =FIXED(number,[decimals],[no_commas]) number - The number to round and format. decimals - [optional] Number of decimals to use. Default is 2. no_commas - [optional] Suppress commas. TRUE = no commas, FALSE = commas. Default is FALSE. Using the FIXED function The FIXED function converts a number to text, rounding to a given number of decimals. Like the Number format available on the home tab of the ribbon, the FIXED function will round the number as needed using the given number of decimal places....

Transcript Once you’ve created a pivot table , you need to add fields to it in order for it to be useful. The fields in a pivot table correspond to columns in the source data. Let’s take a look. Here we have a set of data that’s already formatted as an Excel Table . Let’s use this table to create a pivot table and add some fields. Since the source data is already a Table, we’ll use the Summarize With Pivot Table command, on the Table Tools Design tab....

Transcript In this lesson we’ll look at how to use the border presets that appear in the border menu on the ribbon. There are quite a few options in this menu. The key is to choose the line style and line color before choosing the presets. Let’s take a look. The border menu is on the home tab of the ribbon, inside the font group. It contains a long list of border presets....

Transcript In this video, we’ll look at how to build a simple dynamic chart in Excel. 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....

Transcript In this video we’ll look at how to create a dynamic named range with a Table. This is the simplest way to create a dynamic named range in Excel. This table contains data for ten properties. I can easily create a named range for this data. For example, I can create a range called “data.” Then, using the ROWS function , I can get a total count of properties....

Transcript In this video I’ll show you how to create a formula that uses multiple, nested IF statements. This is a common technique to handle multiple conditions. Let’s take a look. This worksheet shows a class of students with five test scores in columns D through H, and an average in column I. In column J we need to add a formula that calculates a grade based on the average. This can be done with nested IF statements....

Transcript Once you create a Pivot Chart, you may need to filter to exclude or include certain data. Let’s take a look. Let’s create a pivot chart that shows product sales by Region. Since we have a pivot table already, we’ll create a pivot chart first, and then start customizing. Go to the Options tab of the PivotTable Tools ribbon and click the PivotChart button. For chart styles, we’ll use a simple column chart....

Transcript The COUNT function is a simple function that counts numbers. 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....

Transcript In a lot of cases, you’ll use VLOOKUP to find exact matches based on some kind of unique id, but there are many situations where you’ll want to use VLOOKUP to find non-exact matches. A classic case is using VLOOKUP to find a commission rate based on a sales number. Let’s take a look. Here we have one table that lists sales by salesperson, and another that shows the commission that should be earned based on the sales amount....

Explanation The goal is to do something if a cell equals a given value. The most common way to solve this problem is with the IF function. IF function The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. The generic syntax for IF looks like this: =IF(logical_test,if_true,if_false) The result from IF can be a value, a cell reference, or even another formula....

Purpose Return value Syntax =IMCOS(complex_num) complex_num - The complex number in the form “x+yi”. Using the IMCOS function The Excel IMCOS function returns the cosine of a complex number. For instance, given “1 + 1i” as input, the function returns a complex number equal to the cosine of the input. =IMCOS(COMPLEX(1,1)) // returns 0.833730025131149-0.988897705762865i Given real number input, the function behaves like the cosine function. For instance, when π/2 + 0i is provided as input, the function returns -3....

About This Shortcut This shortcut will insert the current date as a fixed value; it will not change. About This Shortcut This shortcut will insert the current time as a fixed value; it will not change. Note: In Mac 2016, Control + Shift + : stopped working to insert a time. The current shortcut on a Mac is Command + ;

Explanation One of the VLOOKUP function’s key limitations is that it can only look up values to the right. In other words, the column that contains lookup values must sit to the left of the values you want to retrieve with VLOOKUP. There is no way to override this behavior since it is hardwired into the function. As a result, with normal configuration, there is no way to use VLOOKUP to look up a rating in column B based on a score in column C....

Explanation In this example, the goal is to map the numbers 1-6 to the arbitrary values seen in the table below. For example: If the input is 1, the output should be 10 If the input is 2, the output should be 81 If the input is 3, the output should be 17 If the input is 4, the output should be 23 And so on… Input Output 1 10 2 81 3 17 4 23 5 13 6 31 Although we could solve this problem with a complicated nested IF formula , a better option is to put the table on the worksheet and perform a lookup operation....

Explanation In this example, the goal is to calculate the maximum value that occurs in a set of data on a given weekday (i.e. Monday, Tuesday, Wednesday, Thursday, Friday). In the current version of Excel, the simplest approach is to use the FILTER function. In older versions of Excel, you can use a traditional array formula based on the MAX function with the IF function. Both approaches are explained below....

Purpose Return value Syntax =MROUND(number,significance) number - The number that should be rounded. significance - The multiple to use when rounding. Using the MROUND function The MROUND function rounds a number to the nearest given multiple. The multiple to use for rounding is provided as the significance argument. Rounding occurs when the remainder from dividing number by significance is greater than or equal to half the value of significance. If the number is already an exact multiple, no rounding occurs and the original number is returned....

Explanation In this example, the goal is to extract the 3 best race times for each name from the 5 race times that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the fastest time, the 2nd fastest time, and the 3rd fastest time. This problem can be solved with the SMALL function. Note: I don’t know why the second argument for SMALL is called “k” ....

Explanation The goal is to verify the input of all required values before calculating a result. In the worksheet shown, the SUM function is used as an example only. You can use the same approach with any formula to prevent calculation until all required values are available. The logic can be adjusted in many ways to suit the situation. Below are several examples of how you can approach this problem. The best solution depends on the requirements of the project and your personal preference....

Bar charts are great for comparing things, because it’s easy to see how bar lengths differ. This chart is an example of a clustered bar chart showing product units sold this year versus last year. The data used for this chart looks like this on the worksheet: How to make this chart Select the data and insert a bar char on the ribbon: Insert the first 2D bar chart option: Right-click each data series and use fill tool to change color: After changing colors: Select vertical axis, and reverse category order: Move legend to top: Final chart: This chart shows actuals so far in a solid line with forecast numbers in a dashed line....

Purpose Return value Syntax =RANK(number,ref,[order]) number - The number to rank. ref - A range that contains numbers to rank against. order - [optional] Whether to rank in ascending or descending order. Using the RANK function The RANK function returns the rank of a numeric value compared to a list of other numeric values. RANK can rank values from largest to smallest (i.e., the rank of highest test scores) and smallest to largest (i....