In Boolean algebra, there are only two possible results for a math operation: 1 or 0, which, as we know, correspond to the logical values TRUE and FALSE. AND logic corresponds to multiplication. Anything multiplied by 0 is 0, and anything multiplied by 1 remains unchanged. If I multiply A times B, you can see how this works. To get a result of 1, both A and B must be 1....
rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: per - the period we want to work with. Supplied as 1 since we are interested in the the principal amount of the first payment. pv - The present value, or total value of all payments now. In the case of a loan, this is input as a negative value by adding a negative sign in front of C5 to supply -5000....
Both of these functions let you join (concatenate) text in different cells together. TEXTJOIN lets you join values with a delimiter of your choice, and has an option to ignore empty values. CONCAT simply mashes all values together without options. What’s nice about both of these functions is that they can handle cell ranges. That means you can do things like this: Maybe you’re old enough to recognize that number?...
Which converts the text value in B5 “29/02/16” into a proper Excel date. The year value is extracted with with the RIGHT function: RIGHT gets the right-most 2 characters from the original value. The number 2000 added to the result to create a valid year. This number goes into DATE as the year argument. The month value is extracted with: MID retrieves characters 4-5. The result goes into DATE as the month argument....
COUNTIF returns 9, since there are nine cells in D5:D16 that are not equal to “red”. Not equal to In Excel, the operator for not equal to is “<>”. For example: COUNTIF function The COUNTIF function counts the number of cells in a range that meet supplied criteria: To use the not equal to operator (<>) in COUNTIF, it must be enclosed in double quotes like this: This is a requirement of COUNTIF, which is in a group of eight functions that share this syntax....
where “keywords” is the named range E5:E9. The core of this formula is the ISNUMBER + SEARCH approach to finding text in a cell, which is explained in more detail here. In this case, we are looking in each cell for all words in the named range “keywords” (E5:E9). We do this by passing the range into SEARCH as the find_text argument. Because we pass in an array of 5 items: we get an array of 5 items back as a result: Numbers correspond to matches, and the #VALUE!...
In the example shown, the formula in D5 is: First, the LEFT function is used to get the first letter from the first name in column C. Usually, the LEFT function gets a “num_chars” value for the second argument, but the argument is optional and defaults to 1 if omitted. The first letter of the first name is then joined to the last name using the concatenation operator (&), and the result is wrapped in the LOWER function, which forces all text to lower case....
To work correctly, many (but not all) array formulas need to be entered with control + shift + enter. When you enter with control + shift + enter, you’ll see the formula wrapped in curly braces {} in the formula bar. Do not enter curly braces manually, or the formula won’t work. What is an Array? An array is a collection of more than one item. Arrays in Excel appear inside curly brackets....
For example, the screen above shows two groups that contain five values each. Three values match and two are different. The formula used to count matching values is: Working from the inside-out, the B5:B9 is compared to C5:C9 in a simple expression that creates an array of five TRUE FALSE values: We want to count matches (TRUE values) but the SUMPRODUCT function will ignore non-numeric values, so we use a double unary to change the TRUE FALSE values to ones and zeros....
Where “data” is a dynamic named range corresponding to B5:B13. The formula used to create this dynamic named range is based on the OFFSET function: See detailed explanation here. The primary advantage of a dynamic named range is that the reference responds to changes. If values are removed from the bottom of the list, the range contracts. If values are added to the bottom of the list, the range expands....
INDIRECT takes two arguments, ref_text and a1. Ref_text is the text string to evaluate as a reference. A1 indicates the reference style for the incoming text value. When a1 is TRUE (the default value), the style is “A1”. When a1 is FALSE, the style is “R1C1”. For example: The purpose of INDIRECT may at first seem baffling (i.e. Why use text when you can just provide a proper reference?) but there are many situations where the ability to create a reference from text is useful, including:...
On the Mac, this shortcut works directly on the worksheet, and adds a top border to each cell in the selection. 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.
No Mac equivalent that we know of. 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.
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.
Note: STDEV.P replaces the STDEVP function, with identical behavior. Although STDEVP still exists for backwards compatibility, Microsoft recommends that people use the newer STDEV.P function instead. Standard Deviation functions in Excel The table below summarizes the standard deviation functions provided by Excel. Notes: STDEV.P calculates standard deviation using the “n” method, ignoring logical values and text. STDEV.P assumes data is an entire population. When data is a sample only, use the STDEV....
The WORKDAY.INTL function takes four arguments: start_date, days, weekend, and holidays. Start_date must be a valid Excel date. The days argument is the number of days in the future or past to move from start_date, taking into account non-working days. Use a positive number for days to move forward in time, and a negative number to move back. The weekend argument is optional and controls which day(s) of the week should be considered weekends, and therefore non-working days....
Which returns records with dates between January 15 and March 15, inclusive. The expression on the left checks if dates are greater than or equal to the “From” date in F5. This is an example of boolean logic. The expression on the right checks if dates are less than or equal to the “To” date in G5. The two expressions are joined with a multiplication operator, which creates an AND relationship....
where data is an Excel Table in the range B5:E16, and date (H2) and days (J2) are named ranges. The result is the five rows in the table with an expiration date within the next 15 days: All data is in an Excel Table named data in the range B5:E16 and the dates to check are in the “Expires” column. In addition, the current date is in the named range date (H2) and the number of days to use when deciding if a date is expiring soon is in the named range days (J2)....
