Purpose Return value Syntax =ABS(number) number - The number to get the absolute value of. Using the ABS function The ABS function returns the absolute value of a number. You can think about absolute value as a number’s distance from zero on a number line. ABS converts negative numbers to positive numbers. Positive numbers and zero (0) are unaffected. The ABS function takes just one argument , number , which must be a numeric value....
About This Shortcut This shortcut aligns text in selected cells to the right. Note: As of February 2022, the Mac shortcut for Align Right appears to be overridden by the Fill from left shortcut. About This Shortcut This shortcut increases 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....
Purpose Return value Syntax =AREAS(reference) reference - Reference(s) to a cell or range of cells. Using the AREAS function The AREAS function returns the number of areas in a given reference as a number. In this context, areas mean separate contiguous ranges. AREAS takes just one argument , called reference . Reference can include more than one reference, but you must separate multiple references with a comma and wrap them in an extra set of parentheses....
Explanation In this example, the goal is to calculate various percentages of the number in cell B5. This is a straightforward calculation in Excel. The main task is to correctly enter the numbers in column D as percentages. Once that is done, you can multiply the percentage by the number. The first step is to format the cells in the range D5:D12 with the percentage number format . You can use the keyboard shortcut Control + Shift + % ....
About This Shortcut This shortcut will close the dialog box without applying any changes. About This Shortcut This shortcut checks spelling on the current worksheet. The Spelling dialog box will be displayed if a problem is found.
Explanation The CONVERT function requires three arguments: number, from_unit, and to_unit. As long as the units specified are in the same category (weight, distance, temperature, etc.) , CONVERT will automatically perform a conversion and return a numeric result in the specified “to unit”. In the example shown, the top table is set up to convert from inches to centimeters, and the bottom table is set up to convert from centimeters to inches....
Purpose Return value Syntax =CLEAN(text) text - The text to clean. Using the CLEAN function The CLEAN function accepts a text string and returns text that has been “cleaned” of line breaks and other non-printable characters. You can use CLEAN to strip non-printing characters and strip line breaks from text. For example, to clean text in cell A1: =CLEAN(A1) // clean text in A1 The CLEAN function accepts just one argument, text, which can be a text string or number....
Purpose Return value Syntax =CONCATENATE(text1,text2,[text3],...) text1 - The first text value to join together. text2 - The second text value to join together. text3 - [optional] The third text value to join together. Using the CONCATENATE function The CONCATENATE function concatenates (joins) join up to 30 values together and returns the result as text. In Excel 2019 and later, the CONCAT function and TEXTJOIN function are better, more flexible alternatives....
Explanation The Unix time stamp tracks time as a running count of seconds. The count begins at the “Unix Epoch” on January 1st, 1970, so a Unix timestamp is simply the total seconds between any given date and the Unix Epoch. Since a day contains 86400 seconds (24 hours x 60 minutes x 60 seconds), conversion to Excel time can be done by dividing days by 86400 and adding the date value for January 1st, 1970....
Weak Excel muscles can impair your performance, sap your energy, and even hinder your career. In a world flooded with data, you need strong Excel skills to survive and thrive. In just one course, Core Excel will teach you how to use a spreadsheet with ease and confidence, and transform your flabby Excel skills into a sturdy foundation you can build on.
Explanation You would think you could use the COUNTIF function to count birthdays, but the trouble is COUNTIF only works with ranges , and won’t let you use something like MONTH to extract just the month number from dates. So, we use the SUMPRODUCT function with custom logic instead. Inside SUMPRODUCT, we have this expression: MONTH(birthdays)=MONTH(D5&1) On the right, the MONTH function extracts the month for each date in the named range “birthdays”....
Explanation In this example, the goal is to count the maximum number of consecutive monthly orders. That is, we want to count consecutive monthly orders greater than zero. This is a tricky formula to understand, so buckle up! They key to the formula is knowing that the FREQUENCY function gathers numbers into “bins” in a particular way. Each bin represents an upper limit, and contains a count of all numbers in the data set that are less than or equal to the upper limit, and greater than the previous bin number....
Explanation Data validation rules are triggered when a user adds or changes a cell value. The TODAY function returns today’s date (recalculated an on on-going basis). The AND function takes multiple logical expressions and returns TRUE only when all expressions return TRUE. In this case, we need to test two conditions: The first condition checks that the input is greater than today: C5>TODAY() The second condition checks that the input is less than today + 30:...
Purpose Return value Syntax =DAY(date) date - A valid Excel date. Using the DAY function The DAY function extracts the day component from any valid Excel date. For example, DAY returns 15 from the date January 15, 2024. This is useful when you need to work with the day of a date separately, for calculations, comparisons, or building new dates. The DAY function returns a number . If you need a day’s name , use the TEXT function as shown below ....
Explanation This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE and IF functions. The result is the top 3 scores in group B. The FILTER function applies criteria with the include argument. In this example, criteria are constructed with boolean logic like this: (score>=LARGE(IF(group="b",score),3))*(group="b") The left side of the expression targets scores greater than or equal to the 3rd highest score in group B:...
Purpose Return value Syntax =GAMMADIST(x,alpha,beta,cumulative) x - The value at which to evaluate the distribution. alpha - The shape parameter of the distribution. beta - The scale parameter of the distribution. cumulative - A logical value that determines the form of the function. If TRUE, returns the cumulative distribution function; if FALSE, returns the probability density function. Using the GAMMADIST function The GAMMADIST function calculates values for the gamma distribution, which is a continuous probability distribution commonly used in statistical analysis....
Explanation The MONTH function takes just one argument, the date from which to extract the month. In the example shown, the formula is: =MONTH(B4) where B4 contains the date January 5, 2016. The MONTH function returns the number 1 representing the month( January) of the date. Note that you can use MONTH to extract the month from a day entered as text: =MONTH("1/5/2016") However, using text for dates can produce unpredictable results on computers using different regional date settings....
Explanation The first COLUMN function generates an array of 7 numbers like this: {2,3,4,5,6,7,8} The second COLUMN function generates an array with just one item like this: {2} which is then subtracted from the first array to yield: {0,1,2,3,4,5,6} Finally, 1 is added to get: {1,2,3,4,5,6,7} With a named range You can adapt this formula to use with a named range. For example, in the above example, if you created a named range “data” for B4:H4, you can use this formula to generate column numbers:...
Explanation COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule. When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the range we are using in COUNTIF is locked with an absolute address, but B4 is fully relative....
Transcript One thing you’ll frequently do in Excel is check or debug formulas. In this video, we’ll look at how to use the F9 key to quickly break a formula down into pieces that you can understand. Here we have a simple list of names. In addition to names, we have a column for birthdate, a column for age, and a column for legal status. We’ve covered this before, but whenever you inherit a new worksheet, make sure you understand first where the formulas are....
