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. A category axis is used to group dates or text....
Context Let’s say you have a list of text strings in the range B5:B11, and you want to test each cell against another list of things in range E5:E9. In other words, for each cell in B5:B11, you want to know: does this cell equal any of the things in E5:E9? You could start build a big formula based on nested IF statements, but an array formula based on SUMPRODUCT is a simpler, cleaner approach....
Where data is the named range B5:B15. The result is a case-sensitive count of each substring listed in column D. The FIND function is always case-sensitive and takes three arguments: find_text, within_text, and start_num. Find_text is the text we want to look for, and within_text is the text we are looking inside of. Start_num is the position at which to start looking in find_text. Start_num defaults to 1, so we aren’t providing a value in this case, because we always want FIND to start at the first character....
where sheets is the named range B11:B13. The result is 16 since there are sixteen occurrences of “Steven” in Sheet1, Sheet2, and Sheet3. Note: In newer versions of Excel, you can also use the VSTACK function as explained below. Note that the range (B4:F203) must be adjusted to suit your data. COUNTIF function The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?...
where helper is the named range E5:E16. Note: this formula assumes items don’t repeat in a given combination (i.e. AAB, EFE are not valid combinations). A simple workaround is to join all items together in a single cell in a helper column, then use COUNTIFS with a wildcard to count items. We do that with a helper column (E) that joins items in columns B, C, and D using the CONCAT function....
The table in B3:F11 is a log of course data where dates in columns D:F indicate course completion. The summary table in H3:K5 summarizes course completion by group instead of user. In this example, group represents the additional criteria. For Course A, completion by group is calculated with COUNTIFS like this: The first range/criteria pair counts only data from the red group by using the group value in H4. The second range/criteria pair counts non-blank entries in column D....
In fall 2018, Microsoft announced new functionality in Excel called “dynamic array formulas”. Dynamic array formulas have the ability to natively work with multiple values and can “spill” these results onto the worksheet. The new functionality is provided in two parts: This is a big upgrade and a radical change to the way Excel behaves in some circumstances, and it is only available in Excel 365 and Excel 2021 for now....
EVEN takes just one argument, number, which should be a numeric value. With positive numbers, EVEN rounds number up to the next even integer. With negative values, EVEN rounds number down away from zero to the next negative integer. With zero (0) and numbers that are already even integers, number is unchanged. Examples The EVEN function rounds positive numbers up to the next even integer: Negative numbers are rounded away from zero to the next even integer: Zero and numbers that are already even integers are unaffected: To round numbers up to the next odd integer, see the ODD function....
Note most arguments are required, but some are optional. In Excel, optional arguments are denoted with square brackets. For example, the fourth argument in VLOOKUP function, range_lookup, is optional and appears in square brackets as shown above. Finally, many Excel functions accept multiple optional arguments, which are denoted with an ellipses (…) For example, the COUNTIFS function accepts multiple and optional range and criteria pairs, which can be represented like this: This means you can optionally add additional arguments in pairs: range3/criteria3, range4/criteria4, etc....
The MODE.SNGL function takes multiple arguments in the form number1, number2, number3, etc. Arguments can be a hardcoded constant, a cell reference, or a range, in any combination. MODE.SNGL ignores empty cells, text values, and the logical values TRUE and FALSE. The MODE function will accept up to 254 separate arguments. Examples In the example shown, the formula in D5 is: MODE.SNGL returns the most frequently occurring number in supplied data....
For example, a survey question that asks for a favorite beverage, with choices of coffee, tea, water, or milk will generate nominal data in 4 categories as in the above bar chart. When you hear “nominal”, think “named” as in “named categories”. Other examples of nominal data would be male/female, eye color, make of car, etc. Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....
Example Assume we need to calculate the price per $100 face value of a bond with a last interest date of 15-Oct-2017 and a settlement date of 5-Feb-2018. The bond matures on 15-Jun-2018, and pays a coupon rate of 5% with a required return of 6%. Payments are semi-annual, the day count basis is US 30/360, and the redemption value is $100. In the example shown, the formula in F5 is: With these inputs, the ODDLPRICE function returns 99....
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.
Excel offers many types of number formatting. 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.
On a Mac, you can use Command + Q to quit almost any application. On Windows, you can use Alt + F4 for Excel. Also, you can press ALT + F followed by the X key. 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.
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.
On Windows, Ctrl 2 also toggles bolding. 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.
