One way to calculate Net Present Value in Excel is to use NPV to get the present value of all expected cash flows, then subtract the initial investment. This is the approach taken in the example shown, where the formula in F6 is: The NPV function returns 50962.91. The initial investment (-50,000, recorded as a negative value because it is an outflow) is then added, and the final result is 962....
In the data, we have the fields: Name, Gender, Age, and Vote. And, you can see, that there are 3 voting options, A, B, and C. First, how many people voted? By counting Name, you can see that we have 300 votes total. Next, what was the winning vote? The winning vote is Option B, with 156 votes. What’s the breakdown by Percentage? Option A got 31%, Option C got 17%, and Option B is the winner with 52%....
When copied down the column, this formula will return a running total at each row. Where “first” is the first cell in the Total column, and “current” is a reference to a cell in the current row of the Total column. To get the a reference to the first cell, we use INDEX like this: Here, the array is the entire “Total” column and row number is 1. This works because, the INDEX function returns a reference to the first cell, not the actual value....
This worksheet contains measurement data. All together, the table has more than 1000 rows, and each row has 15 measurements, using codes A through F. In this case, I also want to remove blank rows, but it’s much harder to see which rows are really blank. I can’t just select all blanks in a column, because adjacent values in the same row may not be blank. One way to handle this is to add a helper column with a formula that does the check for me....
To copy cells to the clipboard, use the shortcut Control + C on Windows, and Command C on the Mac. To paste, use Control + V on Windows and Command + V on the Mac. Note that when formulas are copied, relative cell references will update automatically. Use cut when you want to actually move data or formulas from one location to another. The shortcut is Control + X on Windows, and Command + X on the Mac....
Where amount is the named range C4, months is the named range C5, and start is the named range C6 The trick is to “cancel out” this amount in months where it doesn’t apply. To do this, we use this logical expression: Here we use the AND function to test each month in row 4 to see if it’s both greater than or equal to the given start month, and less than the end month, calculated by adding the start month to total months....
I’ve also compiled a list of the most 20 popular Excel shortcuts below. This is a tricky business, because people often don’t know what to call shortcuts, or how to refer to them. So I’ve had to “interpret” many of the shortcuts peopled named. That said, the patterns are clear, and the best shortcuts naturally bubbled up again and again. It’s a great list. Dave Survey Results People think Excel Shortcuts are important 99% said Excel shortcuts are important, very important, or critical....
All right, so here we are in Excel, and now I’m going to walk you through some very popular and useful Excel shortcuts. First, since we have a lot of tabs in this workbook, you should know that you can move right through tabs with control page down, and left with control page up. On a Mac, you can also use option with the right and left arrow keys Now, the most popular shortcuts by far in Excel are copy and paste....
Inside the parentheses, the result of B5:D5<>"" looks like this: The double negative then converts the TRUE FALSE values to one’s and zeros: Note that the 1’s in this array correspond to cells that are not blank or empty. Then, with only one array to work with, SUMPRODUCT simply multiples these values together and returns the result. Any time the result is greater than zero, we know that not every cell in the range is blank....
CAGR with the RRI function In Excel 2013 and later, you can use the RRI function to calculate CAGR with a simple formula. The formula in H9 is: where C11 is the ending value in year 5, C6 is the starting value (initial investment), and B11 is the total number of periods. Note: unlike most other financial functions in Excel, fv (future value, the third argument) does not need to be entered as a negative number in RRI....
In the US, many companies match an employee’s retirement deferral up to a certain percent. In this example, the match has two tiers: In Tier 1, the company matches 100% up to 4% of the employee’s compensation. In Tier 2, the company matches 50% on deferrals between 4% and 6%. So, if an employee contributes 10%, the company matches 100% up to 4%, then 50% from 4% to 6%. After that, there’s no match....
Date format codes Assuming a date of January 9, 2012, here is a more complete set of formatting codes for date, along with sample output. You can use the TEXT function to convert dates or any numeric value to a fixed text format. You can explore available formats by navigating to Format Cells (Win: Ctrl + 1, Mac: Cmd + 1) and selecting various format categories in the list to the left....
where data is the named range B5:B15. The result is 4, because there are four cells in the range B5:B15 that contain text values. Note: This formula counts cells that contain any text value. To count cells that contain specific text, see this formula. To count cells that are not empty (i.e. cells that contain text, numbers, dates, etc.), see this formula. COUNTIF function The simplest way to solve this problem is with the COUNTIF function and the asterisk () wildcard....
where code is the named range B5:B16. COUNTIF with leading zeros A common situation where leading zeros do not behave as expected is when functions like COUNTIF, COUNTIFS, SUMIF, SUMIFS, etc. are configured to use numbers with leading zeros. To demonstrate this problem, consider the formula below: Here the COUNTIF function is set up to count values in B4:B8 that are equal to “01”. We expect a result of 1, but COUNTIF returns 5....
which returns 4, since there are 4 unique names in B5:B14. Note: Another way to count unique values is to use the COUNTIF function. This is a much simpler formula, but it can run slowly on large data sets. Working from the inside-out, the MATCH function is used to get the position of each item that appears in the data: The result from MATCH is an array like this: Because MATCH always returns the position of the first match, values that appear more than once in the data return the same position....
In the example shown, the data validation applied to C5:C9 is: In this formula, the LEFT function is used to extract the first 3 characters of the input in C5. Next, the EXACT function is used to compare the extracted text to the text hard-coded into the formula, “MX-”. EXACT performs a case-sensitive comparison. If the two text strings match exactly, EXACT returns TRUE and validation will pass. If the match fails, EXACT will return FALSE, and input will fail validation....
The date January 1, 2021 can be displayed as “1-Jan-20” with the number format: The most important thing to understand about number formats is that they control the display of numbers only – number formats have no effect on underling numeric values. There are quite a few number formats built into Excel, including: General format Currency format Percentage format Scientific format Date format Time format Custom formats You can apply number formats from the Home tab of the ribbon:...
In the example shown, the formula in C5 is: where “data” is the named range C5:C12. Interpolation When x does not exist within the array, the function interpolates a value between data points. For example, when the x value of 4.00 is passed as an argument to the function, the percentage is interpolated to the value 44.4%, which lies between the percent rank of 3.3 and 4.56 which are 37....
The SHEET function takes one argument, value, which should be a reference, a named range, or an Excel Table. Value is optional. When value is omitted, SHEET will return a numeric index for the current sheet (i.e. the sheet the formula exists in). Examples For example, in a workbook with Sheet1, Sheet2, and Sheet3 running left to right: If Sheet2 is dragged all the way to the left, a reference to A1 on Sheet2 will return 1: SHEET can report the sheet number for a cell reference, named range, or Excel Table....
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.
