RAND Function
The Excel RAND function returns a random number between 0 and 1. For example, =RAND() will generate a number like 0.422245717. RAND recalculates when a worksheet is opened or changed.Read more
The Excel RAND function returns a random number between 0 and 1. For example, =RAND() will generate a number like 0.422245717. RAND recalculates when a worksheet is opened or changed.Read more
The goal is to display a blank cell based on a specific condition. In the worksheet shown, we want to return the value from column C, but only when the value in column B is "A". If the value in column B is anything else, we want to display nothing. The easiest way to solve this problem is with...Read more
In this example, the goal is to create a data validation rule that will only accept numbers that are a specific multiple of another number. In the worksheet shown, the multiple is 100. Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns...Read more
In this example, the goal is to sum the Amounts in C5:C16 when the Lead in D5:D16 is not blank (i.e. not empty). A good way to solve this problem is to use the SUMIFS function. However, you can also use the ...Read more
Note: in more recent versions of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more
This formula uses 4 named ranges, defined as follows:
width=K6
height=K7
widths=B6:B11
heights=C5:H5
Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, which is cell...Read more
In older versions of Excel (before Excel 2016?), the character used for line breaks is different depending on whether Excel is running on a Mac or Windows computer: On Windows Excel, the line break character is ASCII 10. In older versions of Excel on a Mac, the line break character is ASCII 13....Read more
The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EOMONTH to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for months to move forward in time, and a negative number...Read more
In this example, the goal is to highlight differences in two ranges, B2:B11 and C2:C11, using conditional formatting. To do this, we need to create a new conditional formatting rule, triggered by a formula, like this:
The Excel SUBSTITUTE function replaces text in a given string by matching. For example =SUBSTITUTE("952-455-7865","-","") returns "9524557865"; the dash is stripped. SUBSTITUTE is case-sensitive and does not support wildcards....Read more
This formula demonstrates a very simple inventory concept where current inventory is simply the result of all incoming stock minus all outgoing stock. In the example, colors are treated as unique item identifiers – imagine a product available in one size only in just three colors: red, blue, or...Read more
In this example, the goal is to create a formula that performs a dynamic two-way average of all age and gender combinations in the range B5:D16 . The solution shown requires four general steps:
Although Excel has a RANK function, there is no RANKIF function to perform a conditional rank. However, you can easily create a conditional RANK with the COUNTIFS function.
The COUNTIFS function can perform a conditional count using two or more...Read more
In this example, the goal is to subtotal (count and sum) values based on cell color. This is a tricky problem, because there is no Excel function that will let you count cells by color directly. There are several different approaches, as explained below.
...Read more
In this example, the goal is to calculate a weighted average of scores for each name in the table using the weights that appear in the named range weights (I5:K5) and the scores in columns C through E. A weighted average (also called a weighted mean)...Read more
In this article, we explain how to use concatenation to display friendly and useful messages in your spreadsheets. The messages are dynamic and respond instantly to changes, so the effect is polished and professional.Read more
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...Read more
The Excel EDATE function returns a date on the same day of the month, n months before or after a start date. You can use EDATE to calculate expiration dates, maturity dates, and other due dates derived from a start date....Read more
The Excel TIME function is a built-in function that allows you to create a time with individual hour, minute, and second components. The TIME function is useful when you want to assemble a proper time inside another formula.Read more
In this example, the goal is to calculate the average hourly rate per day based on the data shown. All data is in Excel Table named data in the range B5:F16. Note that we want to calculate a weighted average in this case. A weighted average hourly rate is the...Read more
The Excel REPT function repeats characters a given number of times. For example, =REPT("x",5) returns "xxxxx".Read more
Note the purpose of this example is to how one way to "normalize" data when the order of values is random. There are many ways to approach this problem.
The formula in G6 relies on the COUNTIF function to count the presence of a given class (i.e...Read more
In this example, the goal is to create initials or acronyms with a formula using the data in column B as the source text. The formula should parse the text in column B, build a list of capital letters used to start words and join the capital letters together in a single text string. The article...Read more
The goal is to list the working days between a start date and an end date. In the simplest form, this means we want to list dates that are Monday, Tuesday, Wednesday, Thursday, or Friday, but exclude dates that are Saturday or Sunday. In addition, we need an option to exclude a list of...Read more
In this example, the goal is to generate a list of random numbers without duplicates. This involves jumping through a few hoops because although the RANDARRAY function can easily generate a list of random integers, there is no guarantee that the numbers will be unique. In the explanation below,...Read more
In this example, the goal is to sum values in matching columns and rows. Specifically, we want to sum values in data (C5:G14) where the column code is "A" and the day is "Wed". One way to solve this problem is with the SUMPRODUCT function...Read more
In this example, the goal is to calculate a quiz score average for each person listed in column D using the four scores in columns C, D, E, and F. The standard way to solve this problem in Excel is to use the AVERAGE function.