where wordtable is the named range F5:G11. If the number in column B is greater than 1, the result is a “pluralized” noun. If the number is one, the result is the original (singular) word. Ingredients In the example shown, the formula uses these ingredients: IF function – to check the number of items VLOOKUP function – to lookup irregular plural forms IFNA function – to take action when there is no irregular form Concatenation – to glue text together...
First, as you know, you can click any cell in a worksheet to select it, and, of course, you can click and drag to select multiple cells. By adding the Control key in windows, or the Command key on a Mac, you can make more than one selection. These selections do not need to be next to one another. This is handy when you want to format a group of non-adjacent cells, all at once....
The formula returns all three dimensions to cell D5, and the results spill into the range D5:F5. Note: in older versions of Excel without TEXTSPLIT, you can use more complicated formulas based on several functions. Both approaches are explained below. Also, if you don’t want or need to use a formula, you can use Excel’s Text-to-Columns feature. There are two basic approaches to solving this problem. If you are using Excel 365, the easiest solution is to use the TEXTSPLIT function as shown in the worksheet above....
where amount (C5:C16) and date (B5:B16) are named ranges. The result in F5 is 225, the sum of amounts in January for both 2022 and 2023. As the formula is copied down, we get a sum of amount by month that ignores years. Basic concept The basic concept in this formula is to extract just the month number from all dates and test this number against the month number of interest....
Pros Can show trends over periods of time Can show component contribution changes over time Cons Non-intuitive presentation may be unfamiliar to many Hard to read as data series are added 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. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts....
Pros Simple presentation; easy to read and create Good for showing trends over periods of time Can handle positive and negative values Cons Don’t usually work well for multiple data series Line can imply more data than actually available (compared to bar or column chart) 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....
The count absent days, the worksheet uses COUNTIF again, configured to count empty cells: 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.
Here are have the chocolate sales data we looked at earlier, and a basic pivot table and chart set up on a separate sheet. When you create a pivot chart, you’ll see something called “field buttons” sitting on top of the chart area. In the upper left, you’ll see buttons for value fields, which correspond to values being plotted in the chart. You’ll also see a button to filter axis or category values....
As this formula is copied down the column, it calculates a running total on each row — a cumulative sum of all amounts up to that point. Note: if you want to create a running total in an Excel Table, you’ll want to take a different approach from the formulas below. See this example. SUM with expanding range An easy way to create a running total in Excel is to use the SUM function with what is called an “expanding reference” — a special kind of reference that includes both absolute and relative parts....
This chart type automatically plots the full range of values as a single line, with indicates high and low in a given time interval. On top of the line is a bar which indicates open and close values. Dark bars indicate close price is less than open price (a loss), light bars indicate a close price greater than open price (a gain). Excel plots this chart in blank and white only by default....
Convert Result to Degrees ATAN returns the angle in radians. To convert the result from radians to degrees, multiply the result by 180/PI() or use the DEGREES function. For example, to convert the result of ATAN(1) to degrees, you can use either formula below: Difference Between ATAN and ATAN2 The ATAN2 function is useful for getting the angle corresponding to a point in the Cartesian Coordinate System which forms the shape of a right-triangle....
The number of tools Excel that provides for working with charts can be a little overwhelming. But the tools get easier with practice. Let’s take a quick tour to get you acquainted. First, the ribbon. To create a chart, you can click Recommended Charts on the Insert tab of the ribbon. Excel will show you recommended charts based on the data you’ve selected. Alternately, if you know the chart type you want, you can use one of the small chart icons to the right....
With the character “a” in cell A1, the formula below returns the same result: The CODE function takes a single argument, text, which is normally a text value. If text contains more than one character, the CODE function returns a numeric code for the first character: The CODE function will handle numeric input for the numbers 0-9: Generally speaking, the number returned by CODE is the code for a character in ASCII decimal notation....
Data validation can simply display a message to a user telling them what is allowed as shown below: Data validation can also stop invalid user input. For example, if a product code fails validation, you can display a message like this: In addition, data validation can be used to present the user with a predefined choice in a dropdown menu: This can be a convenient way to give a user exactly the values that meet requirements....
The FIXED function takes three arguments, number, decimals, and no_commas. Number is the number to convert. Decimals is the number of digits to which number will be rounded on the right of the decimal point. If decimals is negative, number will be rounded to the left of the decimal point. Decimals is optional and defaults to 2. The no_commas argument is a Boolean that controls whether commas will be added to the result....
The FORECAST.ETS.STAT function is (optionally) used to output forecast statistics as part of the “Forecast sheet” feature in Excel. These are statistics relevant to the forecast created by the feature, which relies on the FORECAST.ETS function. In the example shown above, the FORECAST.ETS.STAT function has been inserted manually to output all eight available forecast statistics based on the historical data and timeline shown. The statistic_type values come from column F....
Note: the Mac shortcut does not seem to be working on current versions of OS X and Excel 2011. One reason might be that Option + F1 activates Display Preferences. However, this shortcut does not appear under System Preferences > Keyboard > Keyboard Shortcuts so it’s not clear how to disable it and also not certain that disabling it would solve the problem. Do you know a workaround? Let us know....
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: Older Excel versions on the Mac may use Command + Delete instead of Control + Delete. 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 SIGN function takes one argument, number, which must be a numeric value. If number is not numeric, SIGN returns a #VALUE! error. Examples SIGN can be used to change negative numbers into positive values like this. For example, with -3 in cell A1, the formula below returns 3: The formula above has no effect on positive numbers, since SIGN will return 1. However, the ABS function provides a simpler solution:...
