with these inputs, the YIELDDISC function returns 0.03264023 which, or 3.26% when formatted with the percentage number format. 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. If you want to enter valid dates directly inside a function, the DATE function is the best approach. Basis The basis argument controls how days are counted....

Challenge What formula will translate the “N” and “Y” to weekday abbreviations as shown in the screenshot above? The workbook is attached below. Post your answer in the comments. Extra points for style and elegance, but workhorse solutions are fine, too :) Assumptions This would be a typical solution, and nicely illustrates how concatenation works. Note: you are free to use line breaks inside the formula bar to make formulas easier to read....

The challenge The data below represents orders, one order per row. There are three separate challenges. What formulas in F9, G9, and H9 will correctly count orders with the following conditions: The green shading is applied with conditional formatting and indicates matching values for each set of OR criteria in each column. For your convenience, the following named ranges are available: item = B3:B16 color = C3:C16 city = D3:D16 The worksheet is attached....

The results in column E are decimal values with the percentage number format applied. The general formula for this calculation, where “x” is the original number, is: Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns the original price for each item in the table, based on the percentages shown in column C and the current values in column D....

Here we have historical, 30-year mortgage interest rates going back to 1971. Let’s plot this data in a line chart. Line charts are especially good at visualizing trends over time. To start off, I’ll place my cursor anywhere in the data, then navigate to the insert tab and click the line chart icon. The first option is the one I want. The result is a simple line chart. This is a great example of the simplicity of a line chart....

Let’s take a look. So, we’ve looked at both relative and absolute references, and also at a situation where we needed to use both at the same time. These are sometimes called “mixed references.” A good example of a problem that requires a mixed reference is a multiplication table. If we enter a formula to multiply the numbers in column B by row 4, and use only relative references, we end up with huge numbers....

Here we have the city population data we looked at before. We used the INDEX function to retrieve information about a city with a hard-coded position value. When we supply a number, INDEX retrieves information for the city at that position in the list. Now instead of fetching information by position, let’s convert this worksheet to retrieve information by city name. I’ll do this by using the MATCH function to find the position of the city in the list and using INDEX to retrieve information at that position....

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.

Here we have a list of 16 properties with prices and other information. Let’s calculate some averages based on the criteria shown in column K. Note that this data already contains a number of named ranges. We have “prices,” “beds,” “size,” “listed,” and “status.” The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. The first argument is average_range; this is the range that contains the values to average....

which returns “x” when B6 contains anything except “red” or “green”, and an empty string ("") otherwise. Notice the OR function is not case-sensitive. In the example shown, we want to “flag” records where the color is NOT red OR green. In other words, we want to check the colors in column B, and take a specific action if the color is any value other than “red” or “green”. In D6, the formula were using is this: In this formula, the logical test is this bit: Working from the inside out, we first use the OR function to test for “red” or “green”: OR will return TRUE if B6 is “red” or “green”, and FALSE if B6 contains any other value....

which generates a series of 12 dates, beginning with May 1, 2019, the date in C4. SEQUENCE can generate results in rows, columns, or rows and columns. In this example, we are asking sequence for an array of numbers that is 12 rows by 1 column, starting with the date in C4, and incrementing by 1. Because dates in Excel are just serial numbers, and the date in C4 is equivalent to 43586, SEQUENCE outputs an array like this: which spills into the range E5:E16....

To drag and drop in Excel, just make a selection and hover the mouse over the edge of the selection. When the cursor changes, you can drag the selection to a new location. With this method, drag and drop is equivalent to cut and paste. The content of selected cells is moved, and the selection you drag will completely overwrite the destination cells. You can hold down the shift key to drag and insert....

where data is an Excel Table in the range B5:C16. As the formula is copied down, the formula returns a sum for each day of the week in column E. Why not SUMIFS? You might wonder why we aren’t using the SUMIFS function to solve this problem? The reason is that SUMIFS is in a group of eight functions that requires a range for the criteria_range argument; it is not possible to provide an array instead....

where data is an Excel Table in the range B5:C16 and n is 3. The result is the sum of quantity for the first 3 Red values. Example formula In the example shown, the formula in cell G5, copied down, is: Notice the value for n is hardcoded as 3. This formula is a good example of nesting one function inside another. Extracting matching data Working from the inside out, the first task is to extract a list of quantities by color....

In the example shown the formula in cell E4 is: This formula uses the number of years a salesperson has been with a company to determine which commission rate table to use. In other words, if years is less than 2, table1 is used as for table_array, and, if not, table2 is used as for table_array. Alternate syntax If the lookup tables require different processing rules, then you can wrap two VLOOKUP functions inside of an IF function like so: This allows you to customize the inputs to each VLOOKUP as needed....

which calculates the area of a trapezoid given the length of side a in column B, the length of side b in column C, and the height (h) in column D. where a is the length of side a, b is the length of side b, and h is the height, measured at a right angle to the base. In Excel, the same formula can be represented like this: So, for example, to calculate the area of a trapezoid where a is 3, b is 5, and h is 2, you can use a formula like this: In the example shown, the goal is to calculate the area for eleven trapezoids where a comes from column B, b is in column C, and h comes from column D....

To allow a user to enter only dates in a certain year, you can use data validation with a custom formula based on the YEAR function. In the example shown, the data validation applied to C5:C7 is: This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function. When a user enters a value, the YEAR function extracts and compares the year to 2016: When the years match, the expression returns TRUE and validation succeeds....

This table contains a list of projects showing a sales pipeline. The last column is meant to show the current state of a given opportunity. To the right, in column G, is a list of possible stages. To illustrate how data validation works with tables, I’ll set up the last column of the table to enforce the values in column G, by providing a dropdown list. To start off, I’ll select the stage column, then click the data validation button on the Data tab of the ribbon....

If you need to insert the current date in a way that will not change, use the keyboard shortcut Ctrl + ; This shortcut will insert the current date in a cell as a value that will not automatically change. If you need to display the current date and time, use the NOW function. 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....

When assumptions in column C are changed, the estimated payment will recalculate automatically. To calculate the monthly payment with PMT, you must provide an interest rate, the number of periods, and a present value, which is the loan amount. In the example shown, the PMT function is configured like this: rate = C5/12 nper = C6*12 pv = -C9 Because mortgage rates are annual, and terms are stated in years, the arguments for rate and periods are adjusted in this example....