Posts

Abstract Transcript In this video, we’ll introduce the SEQUENCE function. One of the new functions that comes with the dynamic array version of Excel is SEQUENCE. The SEQUENCE function takes fourarguments. The first argument,rowscontrols how many rows SEQUENCE returns. This argument is required. The second argument,columns, controls the number of columns returned by SEQUENCE.Columnsis optional and defaults to 1. The third argument is calledstart. This is the starting point for the numbers returned by SEQUENCE, and can be any valid number....

Abstract Transcript In this video, we’ll introduce theUNIQUE function. One of the new functions that comes with the dynamic array version of Excel is UNIQUE. The UNIQUE function lets you extract unique values in a variety of ways. The UNIQUE function takes three arguments. The first argument,arrayis the source data you are working with. The second argument,by_colmeans by column. Use this when data should be compared across columns instead of rows....

Explanation This example shows a workbook designed to apply discounts based on seven pricing tiers. The total quantity of items is entered as a variable in cell C4. The discount is applied via the unit costs in E7:E13, which decrease as the quantity increases. The first 200 items have an undiscounted price of $1.00. The next 300 items have a discounted unit price of $0.90. The next 250 items have a unit price of $0....

That means 12 hours = 0.5, 6 hours = 0.25, and so on. Hours come from column C, minutes from column D, and seconds are hardcoded as zero. However, after 31 days, Excel will reset days to zero. This does not affect hours, which will continue to display properly with the number format [h]. Unfortunately, a custom number format like [d] is not supported. TheINT functionsimply returns the integer portion of the number (days)....

Like all Excel time, the result is anumber that represents a fractional day. To see results formatted as time, apply a time-basednumber format. When the total time exceeds 24 hours, the TIME function will “reset” to zero. Notice TIME returns 0 for 24 hours and 48 hours above. Excel will display 72:00:00. First, TIME will only accept whole numbers for hours, minutes, and seconds. If you supply decimal values, the decimal portion of the number will be discarded....

Explanation In the worksheet shown, we have race results for an 800m race. The goal is to display time in minutes, seconds, and hundredths of a second (centiseconds). Dealing with times that include fractional seconds can be tricky in Excel. However, several quirks in Excel make working with fractional seconds more difficult than you would expect. This article explains these quirks and provides tips and formulas to make the process easier....

This works becauseExcel times are fractional values of days. The formula in D5, copied down, is: This works becauseExcel dates are just serial numbers. These values are only for reference, and are not used in any subsequent calculations. The IFS function is new in Excel 2019. Use the IFS function to evaluate multiple conditions without multiple nested IF statements. More than one condition can be tested by nesting IF functions.

Explanation Note: it’s important to understand thatExcel deals with time as fractions of a day. If you oughta calculate elapsed time that crosses midnight,see this page for options and general explanation. To calculate regular time, F5 contains: This isan example of using MIN instead of IF to simplify. The result is the smaller of two options: 8 hours, or regular time as calculated above. To calculate OT (overtime), G5 contains:...

The TIMEVALUE function is meant to parse a time that appears as atext valueinto a valid Excel time. The TIMEVALUE function takes just one argument, calledtime_text. Iftime_textis a cell address, the value in the cell must be text. TIMEVALUE ignores dates if present in a text string. TIMEVALUE will return a decimal number between 0 and 0.99988426, representing 12:00:00 AM to 11:59:59 PM. Alternative formula Notice that the TIMEVALUE formula in C15 fails with a #VALUE!...

By default, TOCOL will scan values by row, left to right. However, TOCOL can also be configured to scan the array by column, top to bottom. TOCOL also provides options for skipping empty cells and errors. Theignoreargument controls what values TOCOL will optionally ignore. By default,scan_by_columnis FALSE and TOCOL will read values “by row” from left to right. To read values instead by column, setscan_by_columnto TRUE or 1. Use the TOCOL function to transform an array into a singlecolumnand theTOROW functionto transform an array into a singlerow....

Use F9 to force the worksheet to recalculate and update the value. The value returned by the TODAY function is a standardExcel date. To display the result as a date, apply a datenumber format. Optionallycustomize the number formatas you like. If you want the current datewitha time value, use theNOW function. Examples The TODAY function can be used on its own, or combined with other functions. Formatting results The result of TODAY is a serial number representing a validExcel date....

Related videos The videos below demonstrate this shortcut.

Related videos The videos below demonstrate this shortcut. Shortcuts for selecting cells