Purpose Return value Syntax =BIN2HEX(number,[places]) number - The binary number you want to convert to hexadecimal. places - [optional] If omitted uses the least number of characters required to represent the number, otherwise pads the result with zeros up to the specified number of places. Using the BIN2HEX function The input number must contain only zeros and ones and be less than 10 characters long, otherwise the function returns the #NUM!...

Explanation Compound interest is a financial concept that describes how an initial investment grows over time, taking into account not only the interest earned on the initial amount but also the interest earned on the interest itself. Compound interest allows your money to grow exponentially, which makes it a powerful tool for building wealth over the long term. To calculate the effect of compound interest in Excel, you can use the FV function, which is designed to calculate the future value of an investment....

Explanation In this example, the goal is to calculate the difference between two times in Excel in hours and minutes. This basic problem comes up frequently when tracking time and may be described in various ways: Calculate elapsed working time in hours. Calculate the duration of an activity in hours. Calculate the hours needed for a task. Calculating the hours between two times is oddly complicated, partly because Excel stores time as fractional values ....

Explanation In geometry, the circumference of a circle with radius (r) is defined by the following formula: =2πr 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. =PI() // returns 3.14159265358979 Therefore, the formula to calculate the circumference of a circle with a radius of 3 is:...

Combo charts combine more than one Excel chart type in the same chart. One way you can use a combo chart is to show actual values in columns together with a line that shows a goal or target value. In the chart shown in this example, daily sales are plotted in columns, and a line shows target sales of $500 per day. This example uses a combo chart based on a column chart to plot daily sales and an XY scatter chart to plot the target....

Explanation In this example, the goal is to count the number of cells in a range that contain numbers. This problem can be solved with the COUNT function or the SUMPRODUCT function. Both methods are explained below. COUNT function The COUNT function counts the number of cells in a range that contain numeric values. In this example, we simply need to give COUNT the range B5:B15: =COUNT(B5:B15) // returns 8 The COUNT function is fully automatic, so there is nothing to configure....

Explanation The YEAR function extracts the year from a valid Excel date . For example: =YEAR("15-Jun-2021") // returns 2021 In this case, we are giving YEAR and array of dates in the named range dates : YEAR(dates) Because dates contains 11 cells, we get back 11 results in an array like this: {2018;2017;2019;2019;2017;2019;2017;2019;2019;2018;2018} Each date is compared to the year in column D, which creates a new array of TRUE FALSE values:...

Explanation Note: Prior to Excel 365, Excel did not have a dedicated function to count unique values. This formula shows one way to count unique values, as long as they are numeric. If you have text values, or a mix of text and numbers, you’ll need to use a more complicated formula . The Excel FREQUENCY function returns a frequency distribution, which is a summary table that contains the frequency of numeric values, organized in “bins”....

Purpose Return value Syntax =CUMIPMT(rate,nper,pv,start_period,end_period,type) rate - The interest rate per period. nper - The total number of payments for the loan. pv - The present value, or total value of all payments now. start_period - First payment in calculation. end_period - Last payment in calculation. type - When payments are due. 0 = end of period. 1 = beginning of period. Using the CUMIPMT function The CUMIPMT function returns the cumulative interest over a range of time defined by a given start and end period....

By default, VLOOKUP will do an approximate match. This is a dangerous default because VLOOKUP may quietly return an incorrect result when it doesn’t find your lookup value. Read below to see some examples of how VLOOKUP can cause trouble when you don’t manage match behavior. Note: the MATCH function has this same behavior – match type is optional and defaults to approximate match. When VLOOKUP is in approximate match mode, it assumes your table is sorted in ascending order, and does a binary search....

Explanation Data validation rules are triggered when a user adds or changes a cell value. The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. As a result, all numeric input will pass validation. Be aware that numeric input includes dates and times, whole numbers, and decimal values. Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case, C5....

Explanation Data validation rules are triggered when a user adds or changes a cell value. In this case, the COUNTIF function is part of an expression that returns TRUE when a value exists in a specified range or list, and FALSE if not. The COUNTIF function simply counts occurrences of the value in the list. Any count greater than zero will pass validation. Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5....

About This Shortcut In Windows you can delete a comment on the keyboard as follows: Shift + F10 to open right-click menu Type “m” to Delete comment In Excel 2016 on a Mac, you can actually type letters to select menu items in the right-click menu. For example “e” will select “Edit comment”. However, there doesn’t seem to be a key that selects the Delete comment item. On a Mac, you can use arrow keys:...

Support Exceljet and say goodbye to ads forever. Exceljet is supported by advertising, but we know ads can be annoying. The Exceljet Lifetime Pass gives you a clean, ad-free browsing experience across the entire site, forever: Ad-free browsing across the entire site No more banners, pop-ups, or distractions. Faster pages and a cleaner reading experience. One-time payment — no subscriptions Enjoy your ad-free pass for life. No renewals, no recurring charges....

Transcript In this video, we’ll set up the FILTER function with a basic example. Filtering to extract data based on matching criteria is a traditionally hard problem in Excel. However, the new FILTER function makes this task much easier. The FILTER function is designed to extract data from a list or table using supplied criteria. In this worksheet, we have data that contains names, scores, and groups. Our goal is to use the FILTER function to filter the data by group....

About This Shortcut In a new column next to existing data, enter a value that represents what you want to extract, combine, etc. Then, in the cell below, use this shortcut. Excel will try to apply the same pattern to all rows in the data. You can use Flash Fill with text, numbers, and dates. Flash fill is a new feature added in Excel 2013. About This Shortcut This shortcut is a gateway to many formatting options, even when the ribbon is collapsed....

Explanation In this example, we have a list of URLs. The goal is to get the portion of each URL that appears after the domain name. In the current version of Excel, the easiest way to do this is to use the TEXTAFTER function. In an older version of Excel, you can use a formula based on the MID, FIND, and LEN functions. Both approaches are explained below. TEXTAFTER function The TEXTAFTER function returns the text that occurs after a given delimiter....

Explanation To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level. When dates are grouped, they can be queried based on the numeric equivalent: Grouped by month - use numbers 1-12 Grouped by quarter - use numbers 1-4 Grouped by year - use year numbers (2012, 2013, etc.) In this case, we want a subtotal of the “sales” field, so we provide the name the field in the first argument, and supply a reference to the pivot table in the second:...

Explanation This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don’t want to use named ranges, make sure you use absolute references for both of these ranges in the formula. This formula is based on the LARGE function , which returns the nth largest value from a range or array of values. The range appears as the first argument in LARGE, and the value for “n” appears as the second:...

Transcript In this lesson we’ll look at several ways to create a new document in Excel: The New menu on the File tab The keyboard shortcut Control-N The Quick Access toolbar Let’s take a look. When you first start Excel, you’ll see you have a new blank document ready to use. If you want to create your own new document, you have several options. First, you can create a new workbook by going to the File tab, and selecting New....