Creating a Random Number in Excel

• 2 mins read

I have been creating a few basic games for my grandson to play in Excel – no internet required. As part of that I needed to generate a random number to select a word but I don’t want the word to change.

CoPilot came up with a novel approach to generate a random number to extract a word based on my list of words.

My list of words is in column A of a sheet called Words.

In my input sheet there is a cell to enter a number. The cell is named GameNumber.

The idea is you start with 1 and increment by 1. But you can enter any number to select a word.

I created two other range names to capture the word.

The first is CountWords.

=COUNTA(Words!$A:$A)

This counts the number of words in my list.

The second range name extracts the word. Its name is CurrentWord.

=INDEX(Words!$A:$A,MOD(GameNumber*9973,CountWords)+1)

The GameNumber is multiplied by 9973 (this number is arbitrary). The result is divided by the number of words, and the remainder is returned by the MOD function. We add one to make sure we don’t end up with a zero remainder which would select the whole of column A.

The INDEX function extracts the word from the list using the number generated by the MOD function. The MOD ensures the number is not more than the number of words.

While the game number stays the same, the word stays the same.