Purpose Return value Syntax =SUMX2PY2(array_x,array_y) array_x - The first range or array containing numeric values. array_y - The second range or array containing numeric values. Using the SUMX2PY2 function The SUMX2PY2 function returns the sum of the sum of squares of corresponding values in two arrays. The “p” in the function name stands for “plus”, as in “sum x 2 plus y 2 “. SUMX2PY2 takes two arguments , array_x and array_y ....

Transcript What’s a relative reference? A relative cell reference points to an address that is relative to the location of the reference. When a formula is copied to a new location, relative references are automatically updated. Relative cell references are standard in Excel. When you want to perform similar calculations in different cells, they are exactly what you want and will save you a lot of time entering and maintaining formulas....

Transcript Hi, Dave here from Exceljet So, today we’re going to tackle the question Why Pivot Tables? And this question comes up a lot when people first run into Pivot Tables, because they’re wondering…why should they care? And you’re trying to explain that pivot tables are really fast, that you can summarize data interactively in different ways…blah, blah blah And you can kind of see their eyes just glaze over because, until you’ve actually used a pivot table yourself, it’s really hard to understand why they would be useful to you....

Explanation In this example, the goal is to calculate the average call time (duration in minutes) for each month listed in column G using the dates in column B and the durations in column E. The article below explains two approaches. The first formula is based on the AVERAGEIFS function , which is designed to calculate averages using multiple criteria. The second formula is based on the FILTER function and the AVERAGE function ....

One of the many cool things about Excel is that you can use formulas to display useful, dynamic messages directly on the worksheet. Dynamic messages give your spreadsheets a nice polish. Because they respond instantly to user input, the effect is friendly and professional: A key tool in building friendly messages is concatenation . Concatenation sounds complicated, but it’s a really simple and useful formula technique you can learn in a few minutes....

Explanation One of the most important skills to learn with Excel formulas is the concept of nesting. Put simply, nesting just means putting one function inside another. Nesting is super useful, but it does take some practice. You have to learn to read a formula from the inside out. The formulas below are good examples of nesting. Practice reading the formulas starting with the innermost functions. In this example, the goal is to capitalize the first letter in a text string with a formula in Excel....

Purpose Return value Syntax =CEILING(number,significance) number - The number that should be rounded. significance - The multiple to use when rounding. Using the CEILING function The Excel CEILING function rounds a number up to a given multiple. The multiple to use for rounding is provided as the significance argument. If the number is already an exact multiple, no rounding occurs and the original number is returned. The CEILING function takes two arguments , number, and significance ....

Purpose Return value Syntax =COUPPCD(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 COUPPCD function Historically, bonds were printed with an elaborate design on paper that included detachable coupons. The coupons were presented to the bond issuer by the bondholder to collect periodic interest payments....

Purpose Return value Syntax =CSC(number) number - The angle provided in radians. Using the CSC function The CSC function returns the cosecant of an angle provided in radians. In geometric terms, the cosecant of an angle is equal to the ratio of a right triangle’s hypotenuse divided by its opposite side. For example, the cosecant of PI()/6 or 30° returns the ratio 2.0. =CSC(PI()/6) // Returns 2.0 Using Degrees The CSC function expects radians....

Transcript In this video, we’ll talk about using Excel shortcuts on the Mac. In general, Excel shortcuts on the Mac are more similar than different from Windows shortcuts. But it may not feel that way, especially if you come from a Windows background. Here are the 5 key differences you need to be aware of. First, many shortcut keys on the Mac are often abbreviated as symbols. Here’s a list of those symbols and the keys they represent....

Transcript In this video, we’ll look at the options available for Excel Tables. Excel provides four settings for Tables you should be aware of. The first option controls whether a table range will automatically adjust as data changes. By default, this option is enabled, since this feature is a key benefit of tables. For example, if I enter a new row in this table, the table expands to include it....

Explanation Note: this is a great example of a formula that is hard to understand because the cell references are hard to interpret. The gist of the logic used is this: if the time in row 4 is between the start and end times, the formula should return TRUE and trigger the blue fill via conditional formatting. The actual implementation is a little more complicated because the formula below also takes into account the possibility that the start and end times cross midnight....

Explanation To use the GETPIVOTDATA function, the field you want to query must be a value field in the pivot table, subtotaled at the right level. In this case, we want the grand total of the “sales” field, so we simply provide the name the field in the first argument, and supply a reference to the pivot table in the second: =GETPIVOTDATA("Sales",$B$4) The pivot_table reference can be any cell in the pivot table, but by convention we use the upper left cell....

Explanation In this example, the goal is to get monthly closing stock price over the past n months (i.e. last 6 months, last 12 months, last 24 months, etc.) for the list of symbols that appear in column B. In addition, we want a rolling time period, that stays in sync with the current date. This can be done with the STOCKHISTORY function , whose purpose is to retrieve historical information for a financial instrument over time....

Transcript When you’re working with filtered lists, you might want to know how many items are in the list and how many items are currently visible. In this video, we’ll show you how to add a message at the top of a filtered list that displays this information. Here we have a list of properties. If we enable “filtering,” and filter the list, Excel will display the current and total record count in the status bar below....

Transcript Excel provides special options for filtering date fields that have been added as a row or a column label. Let’s take a look. Here we have a pivot table that shows Total Sales only. Let’s add Customer as a row label, and Region as a column label. Both of these fields contain text, so we see a standard set of filter options for labels. This includes things like Equals, Contains, Begins With, and so on....

Transcript Another useful conditional formatting option is the ability to highlight cells that contain certain text. If you regularly need to search a big list for different values, this conditional formatting option can be a great way to build a custom highlighting feature in your worksheet. Let’s take a look. Here we have a list of names in a table. Let’s use this table to explore how to use conditional formatting to highlight text values....

Transcript In this lesson we’ll take a look at the Currency format. The Currency format is designed to display a currency symbol along with numbers. It has options for the currency symbol itself, the number of decimal places, and negative number handling. Let’s take a look. Let’s start off by copying a set of numbers in General format across our table, and then apply the default currency format to the columns C through H....

Transcript Data bars are one of the key types of conditional formatting in Excel. They’re great way to show the relative magnitude of values in a set of data. Let’s take a look. Here we have a simple table that tracks the progress of a group of people towards a fundraising goal. Column C shows that each person has a goal of $3,000. And Column D shows the current funds raised per person....

Transcript Excel formulas can contain a variety of operators. Operators tell Excel what mathematical function to perform, or what logical expression to evaluate. You’ll often combine multiple operators in a single formula. Let’s take a look. Here we have a list of operators, their names, and a sample formula for each. Let’s take a quick run through the list and enter the formulas shown in column E. Addition and subtraction use standard plus and minus symbols....