Perhaps you weren’t aware, but you can harness the capabilities of this popular spreadsheet software included in the Office package to create a list of names or values and draw them randomly: all within a few clicks. Are you curious to learn how? Then focus on this tutorial dedicated to how to raffle with Excel.
In the following sections, we will explore how to perform this operation using Excel on your computer (both in the classic versions for Windows and macOS as well as the web version), as well as on smartphones and tablets using the Excel app for Android and iOS/iPadOS. But for now, let’s get started. I wish you a pleasant read and the best of luck!
Table of Contents
How to Raffle on Excel
If you want to raffle on Excel, what you need to do is use specific formulas designed specifically for this purpose. Not sure what Excel formulas are? You can learn all about this topic by consulting this tutorial.
In this first part of the tutorial, I will demonstrate the operation of three functions that you can use to raffle on Excel, each with a specific purpose. The functions in question are RAND, RANDBETWEEN, and ARRAYFORMULA. Let’s see how to use them to raffle only numbers.
The RAND function generates a random decimal number between 0 and 1. So, in a cell of your spreadsheet, simply type the formula =RAND() and press Enter on your keyboard.
Of course, the limitation of the range between 0 and 1 may seem a bit useless in practice, but you can solve this issue simply by multiplying the function by the limit value of the data raffle. For example, you can use the formula =RAND()*100 if you want to obtain a decimal number between 0 and 100.
If the minimum number is not zero but a specific value, use the formula =RAND()*(max_value-min_value)+min_value. For instance, if you want the number to be between 10 and 100, the formula you would use is =RAND()*(100-10)+10.
If decimal numbers are not to your liking, you can always request whole numbers instead. In this case, the function to include is INT() and the general formula will thus be =INT(RAND()). Referring back to one of the previous examples, with the formula =INT(RAND()*100), you will obtain a whole number between 0 and 100.
The RANDBETWEEN function generates a random integer between two specified values. The formula will therefore be as follows.
=RANDBETWEEN(min_value,max_value)
For example, if you want to raffle an integer between 10 and 100, you simply need to use the formula =RANDBETWEEN(10,100).
The conveniences of the RANDBETWEEN function, compared to RAND, are that you can directly specify the minimum and maximum value without using additional mathematical operators and without needing to use the INT function to return whole numbers.
Finally, the ARRAYFORMULA function allows you to generate an array (that is, multiple random numbers at once) in a range of cells. You can specify the number of rows and columns, as well as the range of generated numbers. The relevant function is as follows.
=ARRAYFORMULA(rows,columns,min_value,max_value,whole).
The arguments rows and columns specify the number of rows and columns to fill, while min_value and max_value are the numerical range for which the raffle is to be conducted. The whole argument simply requires you to indicate TRUE to generate whole numbers or FALSE for decimal numbers.
For example, if you want to create a raffle table that is 5 rows and 4 columns with whole values between 10 and 100, in a cell of your spreadsheet, you would indicate the formula =ARRAYFORMULA(5,4,10,100,TRUE).
Every time you refresh the Excel sheet by entering new values or pressing F9, the number is recalculated and a new raffle will take place. To lock in the value, reach the cell with the formula, highlight it and press F9 to convert it to the resulting value.
How to Raffle Texts on Excel
In the previous chapter, we have seen some useful functions that return numeric values; however, one of these can also be used to raffle names with Excel. The function in question is RANDBETWEEN, but, of course, it cannot be used alone. It must be combined with the INDEX function. The latter allows you to return a specific value based on the intersection of row and column in a given range.
The generic formula to use is as follows.
=INDEX(range;RANDBETWEEN(1,max_value))
As for the range argument, you should specify the range of cells from which the textual raffle will be made; for max_value, you should provide the maximum number corresponding to the range of cells. For example, if you have 12 cells in the range, you would specify the number 12. The minimum value is always 1, as the raffle must start from the first cell in the range.
Let’s move to a practical example to understand how to use this combination of functions correctly. In column A, you have a data set with texts starting from cell A2 and going up to cell A13. Therefore, the range will be A2:A13. The data extraction must begin from the first cell of the range, so you will specify the value 1 in the RANDBETWEEN function, and its maximum value will be 12, as there are a total of 12 cells in the range.
In any cell of your choice, you will thus type the formula =INDEX(A2:A13;RANDBETWEEN(1;12)) which will first generate an integer between 1 and 12 and then associate this value with the corresponding data from the range according to the cell number.
How to Raffle on Excel Without Repeats
All the raffle formulas I have shown you so far generate random numbers with the possibility that some values may repeat. So how can you raffle on Excel without repeats? In this case, you will need to use a combination of multiple functions to achieve value extraction.
To raffle numbers on Excel without repeats, you need to use a combination of several functions: INDEX, UNIQUE, ARRAYFORMULA, and SEQUENCE. The general formula to use is as follows.
=INDEX(UNIQUE(ARRAYFORMULA(number_of_raffles^2;1;min_value;max_value;TRUE));SEQUENCE(number_of_raffles)).
Let me explain in detail how it works, as it may seem complex at first glance. The ARRAYFORMULA function generates a list of random integer numbers (TRUE) between a minimum value (min_value) and a maximum value (max_value). Since the function itself generates random numbers that may repeat, you assign the value number_of_raffles^2, where number_of_raffles is the number of unique elements requested raised to the power of two, thus increasing the chances of obtaining distinct values.
The UNIQUE function removes duplicates from the list generated by ARRAYFORMULA, leaving only unique values. The SEQUENCE function generates a list from 1 up to the maximum raffle value, instructing INDEX to select the unique numbers generated.
For example, if you want to generate a list of 10 numbers that do not repeat from a pool of numbers ranging from 10 to 100, the formula you would use is =INDEX(UNIQUE(ARRAYFORMULA(10^2;1;10;100;TRUE));SEQUENCE(10)).