Posts

Purpose Return value Syntax =STDEV.P(number1,[number2],...) number1 - First number or reference in the sample. number2 - [optional] Second number or reference. Using the STDEV.P function The STDEV.P function calculates the standard deviation for a sample set of data. 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 STDEV.P function is meant to estimate standard deviation for an entire population....

Explanation In this example, the goal is to sum the amounts in column C by week, using the dates in the range E5:E10 which are all Mondays. All data is in an Excel Table named data in the range B5:C16. This problem can be solved in a straightforward way with the SUMIFS function . In the current version of Excel, which supports dynamic array formulas , you can also create an all-in-one formula that builds the entire summary table in one step....

Explanation In this example, the goal is to calculate a conditional sum with the SUMPRODUCT function to match the criteria shown in G5:G7. One way to do this is to use the IF function directly inside of SUMPRODUCT. Another more common alternative is to use Boolean logic to apply criteria. Both approaches are explained below. Basic SUMPRODUCT The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products....

About This Shortcut New in Windows Excel 2016, this shortcut toggles a full screen mode that hides both the ribbon and status bar . When the formula bar is visible, this provides space to view 8 more rows. When the formula bar is hidden, this shortcut shows 10 more rows in a worksheet. We don’t know an equivalent shortcut on the Mac. However, Control + Command + F will toggle full screen mode for almost any window although it won’t toggle the ribbon or status bar in Excel....

Purpose Return value Syntax =TRUNC(number,[num_digits]) number - The number to truncate. num_digits - [optional] The precision of the truncation (default is 0). Using the TRUNC function The TRUNC function returns a truncated number based on an (optional) number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified. The TRUNC function takes two arguments : number and num_digits ....

Explanation In this example, the goal is to use VLOOKUP to find and retrieve price information for a given product stored in an external Excel workbook. The workbook exists in the same directory and the data in the file looks like this: Note the data itself is in the range B5:E13. VLOOKUP formula The formula used to solve the problem in C5, copied down, is: =VLOOKUP(B5,'[product data.xlsx]Sheet1'!$B$5:$E$13,4,0) This is a standard use of the VLOOKUP function to retrieve data from the 4th column in a table:...

Purpose Return value Syntax =WEIBULL(x,alpha,beta,cumulative) x - The value at which to evaluate the distribution (must be ≥ 0). alpha - The shape parameter of the distribution (must be > 0). beta - The scale parameter of the distribution (must be > 0). 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 WEIBULL function The WEIBULL function calculates values for the Weibull distribution, which is a continuous probability distribution commonly used to model the time until failure of a component or system....

Purpose Return value Syntax =WORKDAY(start_date,days,[holidays]) start_date - The date from which to start. days - Working days before or after start_date. holidays - [optional] A list of dates that are non-working days. Using the WORKDAY function The WORKDAY function calculates a date that is a given number of working days from a specified start date, automatically excluding weekends and, optionally, holidays. You can use the WORKDAY function to calculate project start dates, delivery dates, due dates, and other dates that must consider both working and non-working days....

About This Shortcut This shortcut zooms in on the current worksheet, making items larger and easier to read. Note: you can use Control + mouse scroll wheel to zoom in and out on both Windows and Mac. On Windows, the shortcut appears to be new with Excel 2016, and changes increases the zoom level shown in the lower right of the worksheet by 15% each time Control + is used. Use Control - to zoom out by 15%....

One thing you might have noticed about pivot tables is that almost all the examples you see are based on sales data. This makes sense in a way: sales is where the money is, and companies always have sales data in one form or another. However, pivot tables can handle a lot more than just sales. Any time you need to work with data, you should be thinking about pivot tables....

Explanation In this example, the goal is to calculate a workday n days in the future while excluding weekends and optionally holidays. For convenience, start (B5), days (B8), and holidays (B11:B13) are named ranges . The dates in columns D and E are dynamically generated based on the start date in B5. Conditional formatting is used to shade excluded days in gray and to highlight the final calculated dates in yellow....

Explanation The ADDRESS function creates a reference based on a given a row and column number. In this case, we want to get the last row and the last column used by the named range data (B5:D14). To get the last row used, we use the ROW function together with the MAX function like this: MAX(ROW(data)) Because data contains more than one row, ROW returns an array of row numbers:...

Explanation The goal of this example is to test each cell in B5:B14 to see if it contains any of the strings in the named range things (E5:E7). These strings can appear anywhere in the cell, so this is a literal “contains” problem. The formula in C5, copied down, is: =SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0 This formula is based on another formula that checks a cell for a single substring. If the cell contains the substring, the formula returns TRUE....

Explanation The value in G5 is hard-coded. The formula picks up the value in G5, then subtracts the value (if any) in E6 and adds the value (if any) in F6. When the credit or debit values are empty, they behave like zero and have no effect on the result. When this formula is copied down column G, it will continue to calculate a running balance in each row. Dealing with blank values To display nothing in the balance column when the credit and debit columns are empty, you can use the IF function with AND and ISBLANK like this:...

Purpose Return value Syntax =COMBIN(number,number_chosen) number - The total number of items. number_chosen - The number of items in each combination. Using the COMBIN function The COMBIN function returns the number of combinations for a given number of items. A combination is a group of items where order does not matter. The COMBIN function does not allow repetitions. To count combinations that allow repetitions, use the COMBINA function . To count permutations (combinations where order does matter) see the PERMUT function ....

If you use Excel, but don’t know how to use Pivot Tables, you’re missing out…wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this very powerful tool. With a small investment, Pivot Tables will pay you back again and again.

Explanation In this example, the goal is to create a count of each color in column B. The simplest way to solve this problem is with the COUNTIFS function. COUNTIFS function The COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. To configure COUNTIFS, conditions are supplied in range/criteria pairs — each pair contains one range and the associated criteria for that range:...

Purpose Return value Syntax =ENCODEURL(text) text - The text to be encoded. Using the ENCODEURL function The ENCODEURL function returns a URL-encoded string composed of US-ASCII characters. URL encoding, sometimes called “percent encoding” is a method of encoding characters in a URL using only legal US-ASCII characters. Some characters cannot be part of a URL and are “reserved”. Only characters that are reserved are encoded by ENCODEURL; other characters are left untouched....

Explanation In this example, the goal is to generate monthly totals using the GROUPBY function. This is a tricky problem in Excel because typically, source data contains a regular date field and not a separate field with month names. In addition, the GROUPBY function will, by default, sort everything in alphabetical order. This means when we add month names, they will sort A-Z and end up in the wrong order. Part of the challenge is figuring out the best way to sort the month names as part of the GROUPBY formula....

Every day, thousands of questions about Excel are posted on the internet. Many go unanswered because they are unclear, or too complicated to understand. Below are six tips to write a better question that will attract good answers quickly. 1. Search for existing solutions Try a search first to see if you can find an existing solution you can use or adapt. You can save a lot of time by finding a path others have already travelled....