Explanation In this example, the goal is to sort a list of names and birthdays by month and year. The complication is that the birthdays also include a birth year, so if we try to sort the raw data by birthdays, we’ll end up with a list of birthdays sorted first by year. We will actually see the oldest people in the list first, because Excel dates are actually serial numbers that increase over time....
Explanation The SORT function requires very little configuration. In the example shown, we want to sort data in B5:D14 by the third column, Group. For array , we provide entire range, B5:D14. For sort_index , we provide 3: =SORT(B5:D14,3) With this formula in F5, the SORT function outputs the sorted array in F5:H14. Ascending vs. Descending Data is sorted in ascending order (A-Z) by default. This behavior is controlled by an optional third argument, sort_order ....
Explanation In this example, the goal is to sum every nth value in a range of data, as seen in the worksheet above. For example, if n =2, we want to sum every second value (every other value), if n =3, we want to sum every third value, and so on. All data is in the range B5:B16 and n is entered into cell F5 as 3. This value can be changed at any time....
Explanation In this example, the goal is to sum the quantities in column C when the text in column B contains “hoodie”. The challenge is that the item names (“Hoodie”, “Vest”, “Hat”) are embedded in a text string that also contains size and color. This means we need to apply criteria that looks for a substring in the item text. To solve this problem, you can use either the SUMIFS function or the SUMIF function with a wildcard ....
Purpose Return value Syntax =TBILLYIELD(settlement,maturity,price) settlement - Settlement date of the security. maturity - Maturity date of the security. price - Price per $100. Using the TBILLYIELD function The Excel TBILLYIELD function returns the yield for a Treasury bill, based on a settlement date, a maturity date, and a price per $100. In the example shown, the settlement date is 5-Feb-2019, the maturity date is 1-Feb-2020, and the price per $100 is 97....
Explanation At the core, this formula runs two tests on a value like this: =D5>MIN(B5,C5) // is D5 greater than smaller? =D5<MAX(B5,C5)) // is D5 less than larger? In the first expression, the value is compared to the smaller of the two numbers, determined by the MIN function. In the second expression, the value is compared to the larger of the two numbers, determined by the MAX function. The AND function will return TRUE only when the value is greater than the smaller number and less than the larger number....
Transcript When you’re building a pivot table based on data in an Excel worksheet, you should use a Table. A table offers important advantages and makes working with pivot tables a lot easier. Let’s take a look. Here we have a worksheet that contains property listings. Let’s quickly build a pivot table without using a table. We’ll select a cell in the data, choose Pivot Table from the Insert tab on the ribbon, and accept default settings....
A 100% Stacked Area Chart is a built-in Excel chart type, with data plotted as areas and stacked so that the cumulative area always represents 100%. Stacked Area charts can show progression and composition over time, and the 100% Stacked Area Chart is meant to show the percentage that each component contributes when the cumulative total is not important. In the example above, the number formatting used for percentages is 0% (no decimal places)....
Transcript A tour of the File tab In this lesson, we’re going to take a tour of the File tab. The File tab is also sometimes referred to as “backstage”. Unlike the main stage where you do your work, the File tab is a place to go for tasks that don’t involve working on your data—for example, sharing files, printing, and changing Excel settings. Let’s take a look. The first thing to notice about the File tab is that, unlike other ribbon tabs, you aren’t able to see the worksheet when you’re on the File tab....
About This Shortcut Hold down the shift key and click elsewhere on the worksheet to extend the current selection to include adjacent cells. About This Shortcut Hold down the control key and click (or click and drag) elsewhere on the worksheet to add another range of calls to the current selection.
Explanation If you have an annual interest rate, and a starting balance you can calculate interest with: =balance * rate and the ending balance with: =balance+(balance*rate) So, for each period in the example, we use this formula copied down the table: =C5+(C5*rate) With the FV function The FV function can also be used to calculate future value. The equivalent formula is: =FV(rate,1,0,-C5) The interest rate is used as-is, since we are compounding annually, nper is 1, since there is only one period per year, pmt is zero, since there are no additional payments, and pv is the starting balance, input as a negative value by convention....
Purpose Return value Syntax =ARABIC(roman_text) roman_text - The Roman numeral in text that you want to convert. Using the ARABIC function The ARABIC function converts a Roman numeral to a number in Arabic numeral form. The result from ARABIC is a numeric value. ARABIC takes just one argument, roman_text , which should be a Roman number provided as a text string . For example, the formula below converts “VII” to the number 7:...
Explanation The general formula for simple interest is: interest=principal*rate*term So, using cell references, we have: =C5*C7*C6 =1000*10*0.05 =500 Explanation The FV function calculates compound interest and returns the future value of an investment over a specified term. To configure the function, we need to provide a rate, the number of periods, the periodic payment, and the present value: Present value ( pv ) is the named range G4 Rate is provided as annual rate/periods, or rate /C5 Number of periods (nper) is given as periods * term, or C5 * term There is no periodic payment, so we use zero (0) By convention, the present value (pv) is input as a negative value, since the $1000 “leaves your wallet” and goes to the bank during the term....
Transcript In this video, we’ll look at how to use chart styles and colors. On the Design tab of the Chart Tools menu of the ribbon, you’ll find controls for chart colors and chart styles. Once you’ve selected a chart type, you can use these tools to apply colors and styles. Similar to chart layouts, these controls let you quickly adjust a chart to follow a certain look or theme....
About This Shortcut This shortcut will check and uncheck checkboxes in a dialog box. First select the checkbox using tab or shift tab. About This Shortcut This shortcut will close the dialog box without applying any changes.
Explanation In this example the goal is to count products (rows) where sales have increased and sales have decreased, where previous sales are in column C (Previous) and current sales are in column D (Current). In this case, we can’t use COUNTIFS directly, because COUNTIFS is a range-based function and it won’t accept a calculation for a range argument. One option is to add a helper column that subtracts Previous sales from Current sales and use COUNTIF to count results less than or greater than zero....
Explanation In the generic version of the formula, rng represents the range to check, and txt is the word or substring to count. In the example shown, B5:B8 is the range to check, and C2 contains the text (word or substring) to count. For each cell in the range, SUBSTITUTE removes the substring from the original text and LEN calculates the length of the text without the substring. This number is then subtracted from the length of the original text....
Purpose Return value Syntax =COUPDAYBS(settlement,maturity,frequency,[basis]) settlement - Settlement date of the security. maturity - Maturity date of the security. frequency - Number of coupon payments per year (annual = 1, semi-annual = 2, quarterly = 4). basis - [optional] Day count basis (see below, default =0). Using the COUPDAYBS function Historically, bonds were printed on paper with detachable coupons. The coupons were presented to the bond issuer by the bondholder to collect periodic interest payments....
Explanation In this example, the goal is to get monthly currency exchange rates for a given currency pair (i.e., USD > EUR, USD > GBP, CAD > JPY, etc.). Currency abbreviations are entered in cells F5 and F6, and the start and end dates are entered in cells F7 and F8. If any of these four inputs are changed, the results seen in columns B and C will update dynamically. This can be done with the STOCKHISTORY function , whose purpose is to retrieve historical information for a financial instrument over time....
About This Shortcut Display the Control menu for Main Excel window. No Mac equivalent that we know of.
