The purpose of the AND function is to evaluate more than one logical test at the same time and return TRUE only if all results are TRUE. For example, if A1 contains the number 50, then: The AND function will evaluate all values supplied and return TRUE only if all values evaluate to TRUE. If any value evaluates to FALSE, the AND function will return FALSE. Note: Excel will evaluate any number except zero (0) as TRUE....

Note: Excel won’t help you fill out the arguments for DATEDIF like other functions, but it will work when configured correctly. Time units The DATEDIF function can calculate the time between a start_date and an end_date in years, months, or days. The time unit is specified with the unit argument, which is supplied as text. The table below summarizes available unit values and the result for each. Time units can be given in upper or lower case (i....

where pd = total depreciation in all prior periods. The factor argument is optional and defaults to 2, which specifies the double-declining balance method. You can change factor to another value to influence the rate of depreciation. This is why DDB is sometimes defined as “double-declining method” or “other method”. In the example shown, the formula in D7 copied down, is: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

The database argument is a range of cells that includes field headers, field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in database. Using the example above, you can get the variance of heights for the group “Fox” with either of these formulas: The variance for the entire data set, shown in F5, is calculated with the VAR....

Example In the example shown the formula in D4 is: As this formula is copied down column D, it changes as follows: At each new row, the range is expanded to include one new row. As a result, the SUM function calculates an accurate running total. 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....

For example, a test score greater than or equal to 80% of all test scores is said to be at the 80th percentile. In this case PERCENTRANK.INC will assign a rank of .80 to the score. In the example shown, the formula in C5 is: where “data” is the named range C5:C12. Note: The PERCENTRANK.INC function replaces PERCENTRANK which is now classified as a “compatibility function”. Interpolation When x does not exist within the array, the function interpolates a value between data points....

When entering a formula, you can can click the Select All button to enter a reference an all cells in a worksheet. For example, if you create a formula based on the COUNTA function, and click Select All in Sheet2 when entering the first argument, you’ll get: The reference Sheet2!1:1048576 is a range that includes every row in Sheet2, automatically entered by Excel when you click Select All. Author...

Note: In Excel 365, the original “comment” was renamed to “note” and a new “threaded comment” was added to allow back and forth comments by reviewers. 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.

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.

The Solution Exceljet’s Excel Shortcut Book brings together Excel’s most important shortcuts in one easy to use PDF. All shortcuts are organized into logically grouped sections, and color-coded for easy reference for both PC and Mac. With a professional page layout and crisp PDF rendering both on screen and on paper, this is one shortcut reference you don’t want to be without. With 14 pages of shortcuts, this book is BIG....

The STOCKHISTORY function accepts five primary arguments, and six additional property arguments to retrieve additional information. Stock and start_date are the only required arguments. Each argument is described in detail below. Additional properties are described in the table here. Note: The safest way to provide the start_date and end_date, is as a reference to a cell that contains a valid date, or with the DATE function. This is because dates input as text values can sometimes be misinterpreted....

TRIMMEAN works by first excluding values from the top and bottom of a data set, then calculating mean. The number of data points is provided as a percentage. The percentage can be input either in decimal format or percent format: It’s important to note that TRIMMEAN rounds excluded data points down to the nearest multiple of 2. For example, with 50 data points, 10% equals 5 values. In this case, TRIMMEAN will round 5 down to 4, then exclude two values from the top, of the data set, and two values from the bottom of the data set....

In the generic formula above, “N” represents the “nth line”. Note: In older versions of Excel on a Mac, use CHAR(13) instead of CHAR(10). The CHAR function returns a character based on it’s numeric code. The number of spaces used to replace the line delimiter is based on the total length the text in the cell. The formula then uses the MID function to extract the desired line. The starting point is worked out with: Where “N” stands for “nth line”, which is picked up from row 4 with the D$4 reference....

Alternative with EOMONTH The EOMONTH function returns the last day of in the month of a given date. This means you can get the first day of the current month with a formula like this: This formula “rolls back” a date in A1 to the last of the previous month, then adds 1. The result is the first day of the “current” month (i.e. first day of the month given by the date in A1)....

Where “date” is the named range D5:D14. Note: this is an array formula and must be entered with Control + Shift + Enter. The logical test generates an array of TRUE / FALSE values, where TRUE corresponds to dates greater than or equal to today: When a result is TRUE, the date is passed into array returned by IF. When a result is FALSE, the date is replaced by the boolean FALSE....

The result is the close price for each symbol in the table on July 1, 2021. Note that if there is no close date on the date given, STOCKHISTORY will return a #VALUE! error. For a more general overview of the STOCKHISTORY function, see this page. STOCKHISTORY retrieves historical stock price information based on a given symbol and date range. For example, to return the closing price for Apple, Inc....

For example, if you have dates in the range B4:G11, and want to highlight dates that have the same month and year as the date June 1, 2015, select the range B4:G11 and create a new CF rule that uses this formula: Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Once you save the rule, you’ll see all dates that occur in June 2015 highlighted....

A 100% stacked column chart is a sub type of a regular stacked column chart. The difference is that the 100% stacked version is meant to show the proportional breakdown of multiple data series in stacked columns, where the total height is always 100%. In this worksheet, we have quarterly sales data broken down by region and quarter. To start off, I’ll create a normal stacked column chart. I’ll flip the chart with the Switch Column/Rows button to group sales by quarter instead of by region....

Let’s take a look. When you first add Row or Column labels to a pivot table, you won’t see any subtotals. For example, in this pivot table, let’s add Category as a row label, and Region as a column label field, and Total Sales as a value. Notice we see grand totals, but no subtotals. However, if we add a second field to the Row labels area, Product, we’ll see a subtotal for each category appear as a row in the table....

Let’s take a look. Here we have a simple pivot table and pivot chart already set up. As always, any change to the pivot table is reflected in the pivot chart, and vice versa. Let’s say we want to add a second chart using the same data. We can easily copy and paste the existing chart to another location to create another copy. And, if we just want to change something like chart type, we can do that without affecting the other chart....