Explanation The RAND function generates a decimal number between zero and 1. So, you might get output like this from RAND() in three cells: 0.54739314 0.919767722 0.633760119 Dates in Excel are defined as simple numbers, where 1 = 1 day. This means you can simply divide 1 by the decimal value of time to get a value that corresponds to time as Excel sees it, for example: 1/12 = 12 hours = ....

Explanation Standard deviation in Excel Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers. To calculate standard deviation in Excel, you can use one of two primary functions, depending on the data set. If the data represents the entire population, you can use the STDEV.P function . IF the data is just a sample, and you want to extrapolate to the entire population, you can use the STDEV....

Purpose Return value Syntax =STDEVP(number1,[number2],...) number1 - First number or reference in the sample. number2 - [optional] Second number or reference. Using the STDEVP function The STDEVP function calculates the standard deviation for an entire population. Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers. The STDEVP function is meant to estimate standard deviation for an entire population....

Explanation In this example, the goal is to sum the Quantity in C5:C16 when the Item in B5:B16 ends with “small”. To solve this problem, you can use either the SUMIFS function or the SUMIF function with the asterisk (*) wildcard, as explained below. Wildcards Certain Excel functions like SUMIFS and SUMIF support the wildcard characters “?” (any one character) and “*” (zero or more characters), which can be used in criteria....

Explanation Excel times are numbers and can be summed like other numeric values. In this example, F4:G7 is a summary table, showing the total time logged in each of three states: Standby, Run, and Offline. These values are hardcoded in the range F5:F7. To sum time conditionally by each state, we are using the SUMIFS function in G5: =SUMIFS(times,states,F5) The sum_range is the named range times (C5:C15), entered in hh:mm format Criteria_range1 is the named range states (D5:D15) Criteria1 is entered as F5 The reference to F5 is relative ....

Transcript In this video, we’ll look at how to create a two-way summary table with dynamic array formulas that works like a pivot table. This worksheet contains several hundred rows of sample order data in an Excel Table called “data”. I’ll first build a pivot table to summarize this data by Color and Region. I’ll keep the pivot table on the same sheet. Then I’ll add Color as a row label, Region as a column label, and Total as a value field....

About This Shortcut This shortcut will ungroup selected pivot table items. If you want to ungroup a field, you only need to select one item in the field before using this shortcut. About This Shortcut This shortcut is the same as filtering a pivot table field to exclude an item in the field. Check the filter afterwards to confirm how the shortcut works.

Explanation This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove any blank values from the data: FILTER(B5:B16,B5:B16<>"") The <> symbol is a logical operator that means “does not equal”. For more examples of operators in formula criteria see this page . FILTER returns an array of values, excluding empty strings : {"red";"amber";"green";"green";"blue";"pink";"red";"blue";"amber"} This array is returned directly to the UNIQUE function as the array argument....

Purpose Return value Syntax =VLOOKUP(lookup_value,table_array,column_index_num,[range_lookup]) lookup_value - The value to look for in the first column of a table. table_array - The table from which to retrieve a value. column_index_num - The column in the table from which to retrieve a value. range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match. Using the VLOOKUP function The Excel VLOOKUP function scans the first column in a table, finds a match, and returns a result from the same row....

Transcript What is a number format? Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. The most important thing to understand about number formats is that they only affect how a number looks—they have no effect on the actual value stored by Excel. Let’s take a look....

Below is a brief overview of about 100 important Excel functions you should know, with links to detailed examples. We also have a large list of example formulas , a more complete list of Excel functions , and video training . If you are new to Excel formulas, see this introduction . Note: Excel now includes Dynamic Array formulas , and almost 50 new functions . Download: 101 Excel Functions PDF...

About This Shortcut This shortcut will accept a function suggested by autocomplete. Start typing the first few letters of a function, then type Tab to accept the top option in the list that appears. You can also use up or down arrow keys to navigate the autocomplete list before pressing Tab. About This Shortcut This shortcut will display the Insert dialog box. If an entire row or entire column is selected, this shortcut will insert a new row or new column....

About This Shortcut On Windows, this shortcut only works within the Format Cells dialog box, on the Borders tab. If you use this shortcut in Format Cells, you’ll see a interior vertical border added or removed from the border preview area. There is no equivalent shortcut on the Mac About This Shortcut On Windows, this shortcut works in the Font tab of the Format Cells dialog box and removes all cell borders except diagonal borders....

Dynamic Arrays are one of the biggest changes ever to Excel’s formula engine. With 6 brand new functions that directly leverage dynamic arrays, they solve hard problems in Excel that have vexed even power users for decades. However, Dynamic Arrays are only available in Office 365 , they are not available in Excel 2016 or 2019, and won’t work in any older version. For those not using Office 365, this page provides some alternative formulas that work in older versions of Excel....

Purpose Return value Syntax =ATAN(number) number - The value to get the inverse tangent of. Using the ATAN function The Excel ATAN function returns the inverse tangent or arc-tangent of a number. In geometric terms, the function returns the angle of a right-triangle given the ratio of its opposite side over its adjacent side. The ATAN function is the inverse of the TAN function. For example, if the length of a right-triangle’s adjacent side is 3 and the length of its opposite side is 3 to find the angle of the triangle the formula is:...

Explanation In this example, the goal is to count numbers that fall within specific ranges. The lower value comes from the “Start” column, and the upper value comes from the “End” column. For each range, we want to include both the lower value and the upper value. For convenience, the numbers being counted are in the named range data (C5:C16). This problem can be solved with both the COUNTIFS function and the SUMPRODUCT function, as explained below....

Explanation In this example, the goal is to count the number of occurrences of a character in a cell or text string. Strangely, Excel does not have a function dedicated to counting characters, so we need to use a formula that computes a count manually. The typical way to do this is to use a formula based on the SUBSTITUTE function and the LEN function . LEN Function The LEN function calculates the number of characters in a text string....

About This Shortcut Extends the selection to the cell at the intersection of the last column (to the right) that contains data and the last row that contains data. The last cell in a worksheet may or may not contain data. About This Shortcut With extend selection enabled, you don’t need to press Shift to extend the current selection.

Purpose Return value Syntax =FORECAST.ETS.SEASONALITY(values,timeline,[data_completion],[aggregation]) values - Existing or historical values (y values). timeline - Numeric timeline values (x values). data_completion - [optional] Missing data treatment (0 = treat as zero, 1 = average). Default is 1. aggregation - [optional] Aggregation behavior. Default is 1 (AVERAGE). See other options below. Using the FORECAST.ETS.SEASONALITY function The FORECAST.ETS.SEASONALITY function returns the length in time of a seasonal pattern based on existing values and a timeline....

Explanation The DAY function takes just one argument, the date from which you want to extract the day. In the example, the formula is: =DAY(B5) B5 contains a date value for January 5, 2016. The DAY function returns the number 5 representing the day component of the date. Note that you can use DAY to extract the day from a day entered as text: =DAY("1/5/2016") But this can produce unpredictable results on computers using different regional date settings....