Posts

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....

The best part about using a Pivot Table is that even if you don’t know anything in Excel, you can still do pretty awesome things with it with a very basic understanding of it. Let’s get started. Click here to download the sample data and follow along. What is a Pivot Table and Why Should You Care? A Pivot Table is a tool in Microsoft Excel that allows you to quickly summarize huge datasets (with a few clicks)....

Download Example File Watch Video – Using Solver in Excel One Variable Data Table in Excel. Two Variable Data Table in Excel. Scenario Manager in Excel. Goal Seek in Excel. Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek. Solver add-in is disabled in Excel by default....

Suppose you have a template as shown below, and you have to enter data in excel in all the cells highlighted in red, in the order specified by the arrows. While this seems easy enough, it could be nerve-wracking when you have many such non-contiguous cells and you are burning your eyes by spotting and filing these cells one by one. Quick Way to Entering Data in Excel in a Specific Order: Select all the cells where you need to enter the data (press control and then select one by one)....

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.