which returns the length of the hypotenuse, given lengths of side a and aside b, given in column B and C respectively. a2 + b2 = c2 When any two sides are know, this equation can be used to solve for the third side. When a and b are known, the length of the hypotenuse can be calculated with: When b and c are known, the length of side a can be calculated with:...

In the example shown, the formula in cell D6 is This tells Excel to take the value in B6 ($33.39 ) and round it down to the nearest multiple of the value in C6 (5). The result is $30.00, since 30 is nearest multiple of 5 below 33.39. Likewise, in cell D7, we get 30 when rounding down using a multiple of 10. You can use FLOOR to round prices, times, instrument readings or any other numeric value....

Excel Tables are one of Excel’s most powerful features for working with data. To create a table, just select a cell in the data and use Control + T. New tables have filters enabled. To toggle filters, use Ctrl-Shift-L in Windows, and use Command-Shift-F on a mac. A number of shortcuts for selecting cells take advantage of the table structure. You can select the entire table with Select All, that’s control + A on Windows, Command-A on a Mac....

where “code” is the named range K5:K9, and “value” is the named range L5:L9. Note: this is an array formula, and must be entered with control + shift + enter. which would return 4. The twist in this problem however is that we want to translate and sum a range of text values in columns C through G to numbers. This means we need to provide more than one lookup value, and we need INDEX to return more than one result....

The table array is the table named Table1, with data in the range B5:F104. The column index is provided by the MATCH function. And match type is zero, so force VLOOKUP to perform an exact match. The MATCH function is used to get a column index for VLOOKUP like this: This is what accomplishes the two-way match. Values in column H correspond to the headers in the table, so these go into match as lookup values....

When the value in column B is within +/- .005 of 0.250 (from column C), the formula returns “OK”. If not, the formula returns “Fail”. Core logic To check if a value is within a given tolerance, we can use a simple logical test like this: Inside the ABS function, the actual value is subtracted from the expected value. The result may be positive or negative, depending on the actual value, so the ABS function is used to convert the result to a positive number: negative values become positive and positive values are unchanged....

where “data” is the named range B5:C11. When VLOOKUP can’t find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or IFERROR function to trap this error. However, when the result in a lookup table is an empty cell, no error is thrown, VLOOKUP simply returns a zero. This can cause problems when the lookup table contains actual zero values, because it suggests that blank cells in the lookup table also contain zeros, when they in fact are empty....

where item (B5:B15) and price (D5:D15) are named ranges. The same formula without named ranges is: XLOOKUP’s arguments are configured as follows: The lookup_value comes from cell F5 The lookup_array is the named range item (B5:B15) The return_array is the named range price (D5:D15) The not_found argument is provided as “no match” The match_mode is set to 0 (exact match) The search_mode is set to -1 (last to first)...

where codes (C5:H15) and group (B5:B15) are named ranges. The result for the lookup code “BDBC” is “Epsilon. This lookup can also be done in older versions of Excel with INDEX and MATCH, as explained below. where codes (C5:H15) and group (B5:B15) are named ranges. At a high level, this formula uses the XLOOKUP function to perform the lookup, with the number 1 as the lookup value, and the named range group as the return array....

Boolean logic is an elegant way to apply multiple criteria. In this worksheet we have sample order data in a table called “data”. Let’s use the XLOOKUP function to find the first order in March where the color is red. To make things clear, I’m going to work out the logic in helper columns first. Then, I’ll move that logic into the XLOOKUP function, to make an all-in-one formula. First, we’ll test for dates in March with the MONTH function....

In the example shown, the formula in D5 is: Note: make sure results are formatted as time. As a result, if you have a decimal value for 6 hours, and a time in A1, you can add 6 hours of time to the value in A1 like this: With the TIME function You can also add time values with the TIME function. To add 15 hours to a time in A1, use: The TIME function saves you from having to remember the formula for converting decimal hours to an Excel time....

In the example shown, the formula in D5 is: Adding years To move forwards and backwards in years from a certain date, you can multiply by 12 inside EDATE like this: 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....

Pros Able to display multiple chart types in a single chart Able to plot values with completely different sales Can show how one factor is influenced by another Cons Inherently more complex than other chart types Can become complicated quickly Author 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....

Let’s take a look. Here we have the example we looked at previously. We have a single conditional formatting rule that uses a formula to highlight cells that have a value greater than the input cell. Let’s modify this rule to use two conditions, so that we can format cells that are between two numbers, using a lower and upper limit. To do that, I’ll first re-label the current input, and then make a new input for upper limit....

The results in column E are decimal values with the percentage number format applied. Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns a new price for each item in the table, based on the percentages shown in column D. Negative percentages A negative percentage will have the effect of increasing the original price. For example, with -10% in cell D5 (-0....

This worksheet contains daily sales numbers for a small online store. I’ll plot the data in a basic column chart. Let’s say we want to highlight the highest and lowest values by showing these values directly over the bars. This may seem tricky, but we can build a very straightforward solution using only data labels with a simple formula. I’ll work through the solution step-by step. First, I’ll enable data labels....

To create an absolute reference in Excel, add a dollar sign before the row and column. For example, an absolute reference to A1 looks like this: An absolute reference for the range A1:A10 looks like this: Example In the example shown, the formula in D5 will change like this when copied down column D: Note that the absolute reference to C2, which hold the hourly rate does not change, while the reference to hours in C5 changes with each new row....

Example In the example above, the three ranges map to arrays in a “row by column” scheme like this: If we display the values in these ranges as arrays, we have: Notice arrays must represent a rectangular structure. Array syntax All arrays in Excel are wrapped in curly brackets {} and the delimiters between array elements indicate rows and/or columns. In the US version of Excel, a comma (,) separates columns and a semicolon (;) separates rows....

To convert degrees back to radians, you can use the RADIANS function. Converting degrees to radians manually Because Pi = 180°, the general formula for degrees to radians is degrees * PI()/180. For example, to convert 45° to radians, the Excel formula would be 45*PI( )/180 which equals 0.7854 radians. More examples in the table below: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....