Random Number Generator for Excel, Revisited

Another, simpler way to do it.

Nearly five years ago, I wrote a short article about creating a simple spreadsheet to generate random numbers in Excel. Lately, it’s been the most popular post on this site.

Trouble is, it’s a bit outdated. There’s actually an easier way to do the same thing: with the RANDBETWEEN function.

The RANDBETWEEN(bottom,top) function “calculates” a random number between the bottom number and the top number. Just provide those two numbers and Excel does the rest.

The benefit of this function is that you don’t need to go through a complex calculation to tell Excel you want a whole number within a range. RANDBETWEEN returns whole numbers automatically. It can also output the top and bottom values in the range. And it might actually be more random than my original solution because you’re not dependent on rounding rules to get the final number.

ScreenshotSo which would you prefer? The old way, with a formula like this:

=ROUND(RAND()*B6-B5)+B5,0)

or the new way, with a formula like this:

=RANDBETWEEN(B5,B6)

I know which one I prefer.

Download the revised sample worksheet here.

Sorting Excel Data: The Basics & Beyond

A definitive guide to sorting data managed in Microsoft Excel.

Sorting Excel Data cover

This guide takes the mystery and confusion out of Excel’s sorting features. It starts by covering the basics of simple, one-column sorts. It then builds on that information to explain multi-column sorting, setting up and using custom sort orders, sorting based on cell colors or icons, performing case-sensitive sorts, and sorting by rows instead of columns. Step-by-step, fully illustrated instructions make it clear what you need to do. Sample files make it easy to repeat exercises so you can see the same results.

Although this book concentrates on Microsoft Excel 2010 for Windows and Microsoft Excel 2011 for Mac OS, it also provides useful tips and instructions for previous versions of Excel.

Buy Kindle Edition
Buy iBooks Edition
Buy NOOK Edition

I’m really pleased to announce that the second book in the Maria’s Guides series — Sorting Excel Data: The Basics & Beyond — is now out and available in three ebook formats, with a print edition on the way.

About the Book

It all started as a question asked by a friend in Facebook. An experienced computer professional, she didn’t know how to perform a four-column sort in Microsoft Excel. I thought back to my computer applications training days and remembered how my students struggled with Excel’s sorting features. I decided it would make a good topic for a Maria’s Guide book.

While researching and writing the book, I realized just how much Excel’s sorting feature has changed since I wrote my last Excel book several years ago. While it was obviously important for me to cover the most recent Windows and Mac OS versions of Excel, I also wanted to explain complex sorting to folks who haven’t yet upgraded. I think the book does a great job of completely covering how to sort data managed in Excel.

Inside, you’ll find the following chapters:

Table of Contents
Before We Begin: Introduction
Chapter 1: Sorting Basics
Chapter 2: Quick Sorts
Chapter 3: Multiple-Column Sorts
Chapter 4: Sorting by Color & Icon
Chapter 5: Using Custom Sort Orders
Chapter 6: Exploring Sort Options
Chapter 7: Sorting with Filters & Tables
Conclusion: That’s Everything

The printed version of the book runs 114 pages, including front matter, table of contents, and index.

The book uses several example worksheets, all of which are contained in a single workbook file. Readers are encouraged to download the sample file and follow along. This ensures understanding, since readers get the same results that appear in the book.

Buy the Book

The book is currently available as an ebook from three sources (so far):

The print edition is currently going through the proofing process. Once approved, it will be available on Amazon.com and BN.com, as well as by special order through your favorite bookstore.

Additional Material, Feedback, and Support

You can find additional material about Excel on this site. Just follow the Excel topic link.

You can also post questions and read questions and answers on the book’s support page. That’s also where you can find the sample workbook file used throughout the book.

Category Feeds Being Removed

As part of the site revision process, I’ve decided to do away with the category-specific feeds. These feeds, which cover Excel, Mac OS, Word, and WordPress content, are being utilized by less than 100 people. If you’re reading this message in your feed reader, YOU might be one of them.

Within a month or so, these feeds will simply not work. Delete them from your reader.

If you want to continue receiving content from this site via RSS, please subscribe to the main feed, using one of the following URLs:

Creating a Time-Lapse Calculator with Excel

A quick way to perform movie-making calculations.

One of my hobbies is photography and I dabble occasionally with time-lapse. In time-lapse photography, you set up a camera on a tripod to take a photo at a set interval, like every 15 seconds, over a long period of time, like hours. When you’re finished, you take the resulting images and compile them into a movie using each photo as a movie frame. The length of your movie is dependent on the number of shots and the number of frames per second (fps) at which they are compiled.

I wanted to be able to easily calculate various values for a time-lapse movie project based on certain values I provide, which I call “assumptions.” For example, how many seconds between shots if I want 1200 shots over 3-1/2 hours? How long would a movie be if I took shots over 10 hours with 15 seconds between shots and compiled them at 30 fps?

This is basic math, but with a twist. I wanted to be able to solve for any one of three source photo values given the other two values:

  • Time period, in hours
  • Seconds between shots
  • Number of shots

Given that information, I also wanted to be able to solve for either of two resulting movie values:

  • Frames per second
  • Movie length, in seconds

Time-Lapse CalculatorThe resulting Time-Lapse Calculator shown here does the job.

The formulas I put in the green cells are shown below. The IF function tests to see if cells are empty and uses the test result to determine whether it needs to perform and display a calculation. For example, in cell D6, it checks to see if B6 is empty; if it is, it calculates the result based on B7 and B8. Because the last two formulas require data from either cell B8 or D8, they also test to see which one contains data. The result is a nestled IF statement.


D6=IF(B6="",B8*B7/60/60,"")
D7=IF(B7="",60/(B8/B6/60),"")
D8=IF(B8="",B6*60*60/B7,"")

D11=IF(B11="",IF(B8<>"",B8,D8)/B12,"")
D12=IF(B12="",IF(B8<>"",B8,D8)/B11,"")

You can download a password-protected copy of the worksheet here. (The password is not available for distribution.)

After completing this worksheet and beginning to write about it, I realized that it’s not everything I envisioned. What I really wanted was to calculate one of the following based on the other three:

  • Time period, in hours
  • Seconds between shots
  • Frames per second
  • Movie length, in seconds

I’ll likely work on this in the future. If I finish it, it’ll appear here on Maria’s Guides.

Want to learn more about Excel?

Check out my most recent Excel books and video training materials:

And be sure to use the Excel link in the sidebar to track down other Excel articles like this one on Maria’s Guides.

Office 2008 Installer Needs Rosetta? Duh-oh!

Just something idiotic I wanted to share.

I rolled off a book project with a tight deadline right into a video project with an even tighter deadline, so I don’t really have time to blog, share new articles here, or even tweet. But I did run across this the other day while I was installing Microsoft Office 2008 on my 13-inch iMac running Snow Leopard:

Office Installer Needs Rosetta

Yes, the Office 2008 installer requires Rosetta to run. Office 2008 doesn’t need Rosetta. Just the installer does.

Hello? Microsoft? You want to make your installer compatible with current hardware and software?