Pulling a name out of a hat – or out of an Excel workbook!

A client contacted me to say they wanted to draw a prize winner out of an Excel database.

The RANDBETWEEN function allows you to find a random whole number between two numbers you specify, so combining this with the INDEX function would allow us to do this.

If you had 2000 names in the list, the function = RANDBETWEEN(1,2000) will give a whole number between 1 and 2000. This can be the row with the prize winner on, so we than have to find the name of the person on that row using the INDEX function, which returns the value that is in the cell at the intersection of the row and column you specify.

The syntax of the INDEX function is =INDEX(array, row number,column number), where the array is the data area you are searching.

So if we are searching column A for a name, we could use

=INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1) where the COUNTA($A:$A) counts how many names are actually in the column.

Now you have your prizewinner.

Find more details about Excel training Sydney, Australia

Find more details about Excel training London and surrounding areas, UK or our Excel training Dorset, Wiltshire, Hampshire, etc.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s