Using Degrees To supply an angle to SEC in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians. For example, to get the COT of 60 degrees, you can use either formula below: Explanation The graph of SEC, shown above, visualizes the output of the function for angles from 0 to a full rotation. The function has two vertical asymptotes at π/2 and 3π/2 respectively where the output of the function diverges to infinity....

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.

Using Degrees To supply an angle to SIN in degrees, multiply the angle by PI()/180 or use the RADIANS function to convert to radians. For example, to get the SIN of 30 degrees, you can use either formula below: Explanation The graph of sine, shown above, visualizes the output of the function for all angles from 0 to a full rotation. The function is periodic, so after a full rotation, the output of the function repeats....

Examples In the worksheet shown, the formula in H5, copied down, is: Notice that SUMSQ automatically ignores empty cells and text values. Notes Arguments can be a mix of constants, names, arrays, or references that contain numbers. Empty cells, logical values, and text values are ignored when they appear in arrays or references. The logical values TRUE and FALSE are evaluated as 1 and 0 respectively, but only when they are hardcoded as arguments....

TEXTSPLIT takes five arguments, only two of which are required. The first argument, text, is the text string to split. The second argument, col_delimiter is the delimiter to use for splitting text into separate columns. The third argument, row_delimiter, is the delimiter to use for splitting text into separate rows. Either col_delimiter or row_delimiter must be supplied along with text. The fourth argument, ignore_empty, controls TEXTSPLIT’s behavior with empty values (i....

The TIMEVALUE function takes just one argument, called time_text. If time_text is a cell address, the value in the cell must be text. If time_text is entered directly into the formula it must be enclosed in double quotes (""). Time_text should be supplied in a text format that Excel can recognize, for example, “6:45 PM” or “18:45”. TIMEVALUE ignores dates if present in a text string. The TIMEVALUE function creates a time in serial number format from a date and/or time in an Excel text format....

So, to perform this calculation in Excel, we need to divide the amount of the expense in column C by the percentage that expense represents in column D like this: In the example, the active cell E6, copied down, is: Excel simply divides the value in cell C6 by the percentage value in cell D6: The result is the number 1945 , which is the total of all expenses in this case....

where data (C5:C16) and bins (F5:F8) are named ranges. This formula is entered as a multi-cell array formula in the range G5:G8. The FREQUENCY function returns a frequency distribution, which is a summary table that shows the count of each value in a range by “bin”. FREQUENCY is a bit tricky to use, because must be entered as an array formula. On the other hand, once you set up your bins correctly, FREQUENCY will give you all counts at once!...

Slicers provide a powerful way to filter data interactively, and they are a key building block of many dashboards. To filter a chart with a slicer, you can add a slicer to either the chart or pivot table. Once you do that, the slicer will control both the pivot table and the pivot chart. To add a slicer, select either the pivot table or the pivot chart. You’ll find the Insert Slicer button on the Analyze tab for both....

Here we have a list of salespeople with monthly sales figures. What we want to do is add a formula in Q6 that looks up and retrieves a sales number based on the name and month above. To do this, we’ll use the INDEX and MATCH functions. First, I’ll name some ranges to make the formulas easier to read. I’ll name the entire table “data,” and then use “names” for the list of salespeople....

Let’s take a look. The most basic way to enter cell references in a formula is just to type in the references as you need them. For example, we can type the formula “=B7+D6” directly. Notice that you don’t need to worry about case. When Excel sees a valid reference, it will automatically convert the reference to upper case. Excel has a nice way of showing you what cells are being referenced in a formula....

One of the nice things about the RANDARRAY function is that it makes it easy to generate a list of random dates. In this worksheet, let’s generate 20 random dates between May 1 and May 30, 2020. Now, to use the RANDARRAY function for this, we’re going to need a max and min value. These values correspond to start and end dates, and I’ve already this set up in cells C4 and C5....

Setting up a running total in an Excel table is a little tricky because it’s not obvious how to use structured references. This is because structured references provide a notation for current row, but not for first row in a column. Let’s explore a few options, starting with formulas based on regular references. One common approach is to simply add the row above to the value in the current row… But this will throw an error because the column header contains text....

Let’s take a look. Let’s start off by copying a set of numbers in General format across our table. Then let’s apply Accounting format to the columns C through H. The easiest way to apply Accounting is to use the Number Format menu on the ribbon. Now let’s select cells in column D and check the options available for Accounting in the Format Cells dialog box. Like the Currency format, the Accounting format provides options for decimal places and a currency symbol, and it automatically uses a comma to separate thousandths....

Let’s take a look. Here we have a simple feature summary table. We can use orientation to spice things up a bit. Before we do that, however, let’s look at what types of orientation are available. Orientation options are in a menu in the Alignment group on the home tab of the ribbon. There are two basic types of orientation. One type stacks letters on top of one another. This is called Vertical Text in the orientation menu....

This formula returns “x” if the color in B5 is either “red” or “green”, and the quantity in C5 is greater than 10. Otherwise, the formula returns an empty string (""). Note that the OR function appears inside the AND function. In other words, the OR function is logical1 inside the AND function while C5>10 is logical2. This snippet will return TRUE only when the color in B5 is either “red” OR “green” AND the quantity in C5 is greater than 10....

where name (B5:B16), group (C5:C16), and score (D5:D16) are named ranges. The formula returns the name associated with the 1st, 2nd, and 3rd highest values in Group A. Note: This is an array formula that must be entered with control + shift + enter, except in Excel 365. In this example, we can use the LARGE function to get a highest score, then use the score like a “key” to retrieve the associated name with INDEX and MATCH....

where dates (B5:B15), rooms (C4:G4), and rates (C5:G15) are named ranges. With check-in on December 3 and check-out on December 7 (4 nights) in a King room, the formula returns a total of $460. Working from the inside out, this formula uses boolean logic to “filter” the rate data. The filter is constructed with three expressions, provided as the first argument to SUMPRODUCT: Each of these expressions returns an array of TRUE FALSE values: The math operation of multiplying the arrays together coerces the TRUE FALSE values to 1s and 0s, so we can visualize the operation more simply like this: The result is a two-dimensional array like this: This is the array that does the filtering in this example....

Fields The pivot table shown is based on two fields: Color and Amount. The Color field is configured as a row field, and the Amount field is a value field, as seen below: The Amount field is configured to Sum: You are free to rename “Sum of Name” as you like. Steps Notes Author 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....

Fields The source data contains three fields: Date, Region, and Sales. Note the field list does not include the calculated item. The calculated item was created by selecting “Insert Calculated Item” in the “Fields, Items, and Sets” menu on the ribbon: The calculated field is named “Southeast” and defined with the formula “=South + East” as seen below: Note: Field names with spaces must be wrapped in single quotes (’). Excel will add these automatically when you click the Insert Field button, or double-click a field in the list....