![]() ![]() The COUNTIF function checks if the value in the cell C3 appears for the first time in the list. The RANK.EQ function result is number 3, the rank of the cell C3 in column C. Get the random schedule with RAND, INDEX, RANK.EQ and COUNTIF functions Insert the formula: =INDEX(Task,RANK.EQ(C3,Random_Number)+COUNTIF($C$3:C3,C3)-1)įigure 6.Finally, the parameter range in the COUNTIF function is the cell range $C$3:C3 while the criteria is the cell C3. An order is omitted because we want to rank in descending order. ![]() In the RANK.EQ function, the number is a cell C3 while the parameter ref is the named range Random_Number. The parameter array in the INDEX function is a named range Task while the row_num is the formula RANK.EQ(C3,Random_Number)+COUNTIF($C$3:C3,C3)-1. Now we can place the formula in the cell F3 to get the weekly random task list Using the RAND formula to get a random value between 0 and 1 Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.įigure 5.To apply the formula, we need to follow these steps: The randomized values are placed in column C and will help us to get the random schedule in column F. The RAND function gets the random value from 0 to 1. Creating a named range Random_Number for column “Random Number” Creating a named range Task for column “Task list”įigure 4. Write the name for the cell range and press enterįigure 3.Select the cell range that should be named.To create a named range we should follow the steps: In order to make the formula more clear, we will create a named range Task for cell range B3:B7 and a named range Random_Number for the cell range C3:C7. We want to get the random task from column B and to place the result in column F. Data for the random schedule Create a Random Schedule with RAND, INDEX, RANK.EQ and COUNTIF Functions The idea is to randomly fill column F with the tasks from column B.įigure 2. The second table consists of 2 columns: “Week” (column E) and “Task” (column F). Our first table consists of 2 columns: “Task list” (column B) and “Random Number” (column C). Setting up Our Data to Create a Random Schedule in Excel criteria – the criteria for counting the cells in the range.range – the range from which we want to count the non-blank cells.The parameters of the COUNTIF function are: The generic formula for the COUNTIF function is: – a type of ranking data in the ascending or the descending order (0 or omitted value).ref – the array where we want to rank the number.number – a number for which we want to find the rank in the array.The parameters of the RANK.EQ function are: The generic formula for the RANK.EQ function is: column_num – a column in the array which returns a value.row_num – a number of a row in the array for which we want to get a value.array – a range of cells where we want to get a data.The parameters of the INDEX function are: The generic formula for the INDEX function is: The function returns a random decimal number between 0 and 1 and has no parameters. The generic formula for the RAND function is: Create a random schedule in Excel Syntax of the RAND Formula This step by step tutorial will assist all levels of Excel users to get the random weekly task list.įigure 1. How Do You Create a Random Schedule in Excel?Įxcel allows us to create a random schedule using the RAND, INDEX, RANK.EQ and COUNTIF functions. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |