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....
The result is a path without the filename like this: “C:\path". At a high level, this formula works in 3 steps: Get path and filename To get the path and file name, we use the CELL function like this: The info_type argument is “filename” and reference is A1. The cell reference is arbitrary and can be any cell in the worksheet. The result is a full path like this as text: Note the sheet name (Sheet1) appears at the end, and workbook name appears inclosed in square brackets, [workbook....
If you want to highlight cells that contain certain text, you can use a simple formula that returns TRUE when a cell contains the text (substring) that you specify. For example, if you want to highlight any cells in the range B2:B11 that contain the text “dog”, you can use: Note: with conditional formatting, it’s important that the formula be entered relative to the “active cell” in the selection, which is assumed to be B2 in this case....
