where data is the named range C5:I14. Note this is an array formula and must be entered with control shift enter. where data is the named range C5:I14. This generates a TRUE / FALSE result for every value in data, and the double negative coerces the TRUE FALSE values to 1 and 0 to yield an array like this: Like the original data, this array is 10 rows by 7 columns (10 x 7) and goes into the MMULT function as array1....
In the example, the active cell contains this formula: Because we are using SUMPRODUCT, the result of all this calculation is a list of items (an array), where there is one item per cell in the range, and each item a number based on the calculation described above. In other words, we have a list of character counts, with one character count per cell. SUMPRODUCT then sums the numbers in this list and returns a total for all cells in the range....
Sometimes you want to reference a worksheet dynamically in a formula, so it can be easily changed. In this workbook we have five weeks of test scores, each in the same format. Let’s assume we want to build a simple dashboard that shows the Maximum score, the Minimum score, and the Average score for whichever worksheet a user selects. We’ll take this one step at a time. First, let’s just add the formulas we need and hardcode a reference to a single worksheet....
where emails is the named range C5:C9. COUNTIF returns a count of the value in C5 inside the named range emails (C5:C9). If the count is less than 2, the expression returns TRUE and validation succeeds. If not, the expression returns FALSE and validation fails. 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 B5....
If you’ve never really used Excel, Excel 101 gives you a quick introduction. Each video is short – typically 3 minutes or less – and all videos come with a practice worksheet that you can download directly below the video. The course is is self-paced, so can move through it as fast or slow as you like. Will the course work for both Windows and Mac? Yes, but there are some differences in the Mac and Win interface....
The ABS function takes just one argument, number, which must be a numeric value. If number is not numeric, ABS returns a #VALUE! error. Basic example Negative numbers become positive, while positive numbers and zero (0) are unaffected: Absolute Variance Calculating the variance between two numbers is a common problem. For example, with a forecast value in A1 and an actual value in B1, you might calculate variance like this: When B1 is greater than A1, variance is a positive number....
In the example show, cells F5:F7 contain this multi-cell array formula: The first range results in an array with 3 items. The second range results in an array with only 1 item. Broadcasting occurs when the second array is resized to contain 3 items, {2;2;2}, to match the dimensions of the first array {2;4;6}: Notice Excel simply repeats the value 2 during the operation. Broadcasting only occurs when the value to copy is unambiguous....
Using Degrees The CSC function expects radians. To supply an angle to CSC in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians. For example, to get the CSC of 60 degrees, you can use either formula below: Explanation The graph of cosecant, shown above, visualizes the output of the function for angles from 0 to a full rotation. The function has vertical asymptotes within the range [0, 2π] at the points 0, π and 2π where the output diverges to infinity....
Available Data Types You can find a list of supported data types on the Data tab of the ribbon. The screen below shows the data types available in Excel 365 as of January 2022: Using Data Types To use Data Types, first enter values in one or more cells. These should be values Excel can recognize as potential Data Types. Then select one or more cells, and choose an appropriate data type from the options on the Data tab of the Ribbon:...
In the example shown, the formula in H11, copied down, is: Note ISPMT assumes principal amounts are equal, but the payment is variable. For a loan where the payment is a fixed amount, see the IMPT function. Notes: 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....
Example Assume we need to calculate the price per $100 face value of a bond with a first coupon date of 15-Feb-2019. The bond was issued on 1-Dec-2018, matures on 15-Feb-2022, and pays a coupon rate of 5% with a required return of 6%. Payments are semi-annual, the day count basis is US 30/360, and the redemption value is $100. In the example shown, the formula in F5 is: With these inputs, the ODDFPRICE function returns 97....
Example In the example shown, we want to calculate the yield of a bond with a last interest date of 15-Oct-2017 and a settlement date of 5-Feb-2018. The bond matures on 15-Jun-2018, and pays a coupon rate of 5%. Payments are semi-annual, the day count basis is US 30/360, and the redemption value is $100. In the example shown, the formula in F5 is: With these inputs, the ODDLYIELD function returns 6....
The POWER function takes two arguments: number and power. Number should be a numeric value, provided as a hardcoded constant or as a cell reference. The power argument functions as the exponent, indicating the power to which number should be raised. Examples To raise 2 to the 3rd power, you can use POWER like this: To raise 2 to the 8th power: To cube the value in cell A1:...
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.
On the Mac, there is no equivalent shortcut. 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.
There is no equivalent shortcut on the Mac 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.
Note: As of February 2022, the Mac shortcut for Align Right appears to be overridden by the Fill from left shortcut. 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.
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.
