Explanation Note: this example was created before the VSTACK function and HSTACK function were introduced to Excel. VSTACK combines ranges vertically and HSTACK combines ranges horizontally. These functions are a much easier way to append ranges, but this example is still useful as a way to understand how to approach more complicated problems with dynamic array formulas . You can use the LAMBDA function to create a custom function to combine ranges....
Explanation In this example, the goal is to lookup the first negative value in a set of data. In addition, we also want to get the corresponding date. All data is in an Excel Table called data, in the range B5:C16. This information represents the low temperature in Fahrenheit (F) for the dates as shown. There are several ways to solve this problem, as explained below. XLOOKUP function A simple solution is to use the XLOOKUP function with Boolean logic ....
A pivot table is an easy way to count blank values in a data set. In the example shown, the source data is a list of 50 employees, and some employees are not assigned to a department. The Pivot Table is configured to group out data by department, and automatically creates a category called “(blank)” for employees without a department value. Fields The pivot table shown is based on three fields: First, Last, and Department....
A pivot table is a handy tool for estimating projects. As long as the data is well structured, a pivot table can easily handle hundreds or even thousands of line items. You can group these items by category, by phase, by contractor, and so on. Once the pivot table is created, you can easily adjust filters to show only parts of the project as needed. In the example shown, the source data is extremely basic, and a pivot table is used to group by area and display a sum plus a percentage of the total....
Purpose Return value Syntax =REGEXTEST(text,pattern,[case_sensitivity]) text - The text value to test. pattern - The pattern to test for. case_sensitivity - [optional] 0 = Case sensitive, 1= Case-insensitive. Default is 0. Using the REGEXTEST function The REGEXTEST function provides a way to test for specific text defined by a “regex” pattern. The result from REGEXTEST is TRUE or FALSE. You can think of REGEXTEST as a major upgrade to simpler functions like FIND and SEARCH, which can be used to test a cell for specific text ....
About This Shortcut This shortcut decreases the indent in a cell by one step each time it is used. On Windows, you can sometimes use Ctrl+Alt+Tab to indent and Ctrl+Alt+Shift+Tab to un-indent. However, The application switcher in Windows 7 seems to conflict with these shortcuts. On the Mac, Ctrl+M and Cmd+Shift+Tab also work. Cmd+M should work, but it conflicts with the Finder minimize window shortcut (see http://apple.stackexchange.com/questions/73886/disable-command-m-to-minimize-window) About This Shortcut This shortcut will increase the font size of current selection but one “step”....
The Select All button sits at the upper left of all worksheets, at the origin of row and column labels. You can use the Select All button to quickly select all cells in a worksheet. This can be a convenient way to apply or remove global formatting from a worksheet. When entering a formula, you can click the Select All button to enter a reference that refers to all cells in a worksheet....
Explanation Working from the inside out, we first “normalize” row numbers to begin with 1 using the ROW function and an offset: ROW()-offset In this case, the first row of data is in row 5, so we use an offset of 4: ROW()-4 // 1 in row 5 ROW()-4 // 2 in row 6 ROW()-4 // 3 in row 7 etc. The result goes into the CEILING function, which rounds incoming values up to a given multiple of n....
A stacked bar chart is a basic Excel chart type meant to allow comparison of components across categories. Data is plotted using horizontal bars stacked from left to right. Stacked bar make it easy to compare total bar lengths. However, except for the first series of data (next to the axis) it’s more difficult to compare the relative size of the components that make up each bar. Also, as categories or data series are added, stacked column charts quickly become visually complex....
Explanation In this example, the goal is to calculate a total for each year that appears in column F. All data is in an Excel Table called data in the range B5:D16. The main challenge is that we have dates in column B, but we don’t have separate year values to work with. The simplest way to solve this problem is with the SUMPRODUCT function using the formula seen in the worksheet above....
Explanation At the core, this formula relies on the SUMPRODUCT function to sum values in matching columns in the named range data C5:G14. If all data were provided to SUMPRODUCT in a single range, the result would be the sum of all values in the range: =SUMPRODUCT(data) // all data, returns 387 To apply a filter by matching column headers – columns with headers that begin with “A” – we use the LEFT function like this:...
Explanation The TRANSPOSE function automatically transposes values in a horizontal orientation to vertical orientation and vice versa. However, if a source cell is blank (empty) TRANSPOSE will output a zero. To fix that problem, this formula contains an IF function that checks first to see if a cell is blank or not. When a cell is blank, the IF function supplied an empty string ("") to transpose. If not, IF supplies the value normally....
Explanation In geometry, the formula for calculating the volume of a rectangular prism is: =lwh This formula can be represented in Excel with the multiplication operator (*) like this =l*w*h In the example shown, the formula in E5 is: =B5*C5*D5 // returns 0.13 As the formula is copied down the column, it calculates a new volume in each row, using the length in column B (l), the width in column C (w) and the height in column D (h)....
Function Version Purpose Arguments AND Excel 2003 Test multiple conditions at the same time logical1 logical2 … FALSE Excel 2003 Generate the logical value FALSE IF Excel 2003 Test for a specific condition logical_test value_if_true value_if_false IFERROR Excel 2007 Trap and handle errors value value_if_error IFNA Excel 2013 Trap and handle #N/A errors value value_if_na IFS Excel 2019 Test multiple conditions, return first true test1 value1 test2, value2 … NOT Excel 2003 Reverse arguments or results logical OR Excel 2003 Test multiple conditions at the same time logical1 logical2 … SWITCH Excel 2019 Match multiple values, return first match expression val1/result1 val2/result2 … default TRUE Excel 2003 Generate the logical value TRUE XOR Excel 2013 Perform exclusive OR logical1 logical2 … Function Version Purpose Arguments DATE Excel 2003 Create a date with year, month, and day year month day DATEDIF Excel 2003 Get days, months, or years between two dates start_date end_date unit DATEVALUE Excel 2003 Convert a date in text format to a valid date date_text DAY Excel 2003 Get the day of month from a date date DAYS Excel 2013 Count days between dates end_date start_date DAYS360 Excel 2003 Get days between 2 dates in a 360-day year start_date end_date method EDATE Excel 2003 Get date n months in future or past start_date months EOMONTH Excel 2003 Get last day of month n months in future or past start_date months HOUR Excel 2003 Get the hour as a number (0-23) from a Time serial_number ISOWEEKNUM Excel 2013 Get ISO week number for a given date date MINUTE Excel 2003 Get minute as a number (0-59) from time serial_number MONTH Excel 2003 Get month as a number (1-12) from a date date NETWORKDAYS Excel 2003 Get the number of working days between two dates start_date end_date holidays NETWORKDAYS....
Explanation In this example, the goal is to add a given number of months to a date. If the number of months is positive, the date should move forward. If the number of months is negative, the date should move backward. The standard solution for this problem in Excel is to use the EDATE function although in certain cases you may want to use the EOMONTH function instead. Both approaches are explained below....
Transcript In this video, we’ll take a look at the axes you’ll find in Excel charts. A chart axis works like a reference line or scale for data plotted in a chart. Excel has two primary types of chart axes. The first type is called a value axis, which is used to plot numeric data. Often, the vertical axis in a chart is a value axis. The other primary axis type is called a category axis, which often appears as a horizontal axis....
About This Shortcut This shortcut will apply a Number format with two decimal places, thousands separator, and minus sign (-) for negative values. Excel offers many types of number formatting . About This Shortcut This shortcut will add a border around the outside edge of the current selection.
Explanation Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term), and the payment amount per period. One use of the PV function is to calculate the original loan amount, when given the other 3 components. For this example, we want to find the original amount of a loan with a 4.5% interest rate, and a payment of $93.22, and a term of 60 months....
Explanation In this example, the goal is to combine ranges. With the introduction of the VSTACK function and the HSTACK function, this is quite a simple task. To combine ranges vertically, stacking one range on top of another, you can use the VSTACK function like this: =VSTACK(range1,range2) To combine ranges horizontally, you can use the HSTACK function like this: =HSTACK(range1,range2) In both formulas above, range1 (B5:B8) and range2 (D5:D9) are named ranges ....
Purpose Return value Syntax =COVARIANCE.P(array1,array2) array1 - The first set of data values. array2 - The second set of data values. Using the COVARIANCE.P function The COVARIANCE.P function calculates the population covariance between two data sets. It measures the degree to which two variables vary together, providing insight into their linear relationship. The return value from the function is a single number that can be positive, negative, or zero , depending on the relationship between the variables....
