In the example shown, the formula in C5 is: The CHOOSE function simply maps numbers to values. The first argument is the number to map, and subsequent arguments represent associated values. In this case, 7 values have been provided in the order required to work with WEEKDAY’s Sunday through Saturday scheme. With a date from column B, WEEKDAY returns a number which is fed to the CHOOSE function. CHOOSE returns the value at that position in the list of abbreviations....

In the example shown, the data validation applied to B5:B9 is: where ids is the named range B5:B9. The AND function takes multiple arguments (logical expressions) and returns TRUE only when all arguments return TRUE. In this case, we need two conditions: Logical 1 tests if the input is a number using the ISNUMBER function: The ISNUMBER function returns TRUE when a value is numeric and FALSE if not. Logical 2 tests checks that the input doesn’t already exist in the named range “ids”: COUNTIF returns a count of the value in B5 inside the named range ids (B5:B9)....

Here we have a simple table for a driving trip across the United States. The route begins in San Francisco and ends in New York City. The route is divided into segments, with each segment starting and ending in a larger city. Let’s add formulas in columns E and F to calculate the estimated fuel usage and fuel cost. First, in column E, to estimate fuel consumption we need a formula that divides the distance by the estimated miles per gallon....

where start is the named range J6. In the example shown, conditional formatting is used to highlight the current date and holidays, and lighten days in other months. See below for a full explanation. Note: dynamic array functions are only available in Excel 365 and 2021. For a formula approach that works in older versions of Excel, see this example. In this example, the goal is to generate a dynamic calendar for any given month, based on a start date entered in cell J6, which is named “start” We assume that start is a valid first-of-month date like 1-Jan-2022, 1-Feb-2022, 1-Mar-2022, etc....

Convert Result to Degrees To convert the result from radians to degrees, multiply the result by 180/PI() or use the DEGREES function. For example, the convert the result of ACOS(0.5) to degrees, you can use either formula below: Explanation The graph of ACOS visualizes the output of the function in the range from -1 to 1. ACOS is the inverse of the COS function. However, because COS is a periodic function, the output of ACOS is limited to the range from 0 to π....

Using Degrees To supply an angle to COS in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians. For example, to get the COS of 60 degrees, you can use either formula below: Explanation The graph of cosine above visualizes the output of the function for all angles from 0 to a full rotation. Geometrically, the function returns the x-component of the point corresponding to an angle on the unit circle....

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.

Typically, the subscription version of Excel is available as part of a suite of applications called “Office 365”, which was renamed “Microsoft 365” in 2020. You can see subscription status by navigating to File > Account, which shows current product information. The subscription version of Excel is still downloaded and launched like a normal application on Windows and Mac OS. The key thing to understand is that Excel 365 has new features that are not yet available in any other version of Excel....

Examples The ISEVEN function returns TRUE or FALSE: If cell A1 contains 11, the formula below returns FALSE: Only the integer portion of value is tested. If value is a decimal number, the decimal portion is truncated: Notes If value is not numeric, ISEVEN will return the #VALUE error. Only the integer portion of value is tested, decimal values are truncated. Use the ISODD function to test for odd numbers....

Excel’s MDURATION function returns the modified Macauley duration for an assumed par value of $100. The Macaulay duration is the weighted average term to maturity of the cash flows from a security, which can be calculated with Excel’s DURATION function. Example In the example shown, we want to calculate the modified duration of a bond with an annual coupon rate of 5% and semi-annual payments. The settlement date is 15-Dec-2017, the maturity date is 15-Sep-2027, and the day count basis is US (NASD) 30/360....

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.

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.

With these inputs, the TBILLPRICE function returns a price per $100 of $97.54, with currency number format applied. To convert the face value of $1000 in cell C5 to a price using the value returned by TBILLPRICE, F6 contains this formula: Entering dates In Excel, dates are serial numbers. Generally, the best way to enter valid dates is to use cell references, as shown in the example. To enter valid dates directly inside a function, the DATE function is the best option....

The value returned by the TODAY function is a standard Excel date. To display the result as a date, apply a date number format. Optionally customize the number format as you like. If you want the current date with a time value, use the NOW function. Examples The TODAY function can be used on its own, or combined with other functions. The formulas below show how the TODAY function can be used in various ways:...

VARP ignores text and logicals passed into as cell references. For example, VARP will ignore FALSE when it appears in a range like A1:A10. However, VARP will evaluate logical values, and text representations of numbers hardcoded directly as arguments. Note: Microsoft classifies VARP as a “compatibility function”, now replaced by the VAR.P function. Variation functions in Excel The table below summarizes the variation functions available in Excel. Notes VARP assumes data is an entire population....

The first table in the screen below is a “question key” and shows how questions in Test A are ordered in the other 3 tests. The second table is an “answer key” that shows the correct answers for all 19 questions in all tests. Above: Correct answers in I5:K23, formula obscured For example, the answer to question #1 in Test A is C. This same question appears as question #4 in Test B, so the answer to question #4 in Test B is also C....