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.

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.

90 Days on a Wednesday

An Excel solution.

A while back, someone I know here in Wickenburg called me with an Excel question. He needed to calculate the date of the Wednesday least 90 days from an entered date.

So, for example, if he entered 2/14/2009 in a worksheet cell, he didn’t want 5/15/2009 (a Friday), which was 90 days later. He wanted 5/20/2009, which is the Wednesday at least 90 days after the date he entered.

The solution was to use nested IF statements to evaluate whether the day of the week 90 days after the entered date is a Wednesday. If it is, it uses that date. if it isn’t, it does the same evaluation for 91 days after the entered date. And then 92 days. And so on.

90OnWednesdayThe spreadsheet I came up with can be downloaded here. This screenshot shows what it looks like. It has a lot of other stuff in it that I used to test the formula. Notes in the worksheet explain. I saved it just to share it here. You might find it useful if you have a similar need.

Just watch those parentheses.

Excel Splitting Digits Example

Another quick spreadsheet to show off the capabilities of Excel.

Site visitor Jamie left the following comment on my recent post, “Random Number Generator for Excel“:

maria dont spose u know how to split a four digit number like 2365 into 4 different numbers 2 3 6 5 in all different cells in excel without having to do it manually just wandering please tell me if you know a way thanks

I had an idea of how to do this with the MID function, which is a text function. I just didn’t know if I had to convert the starting number to text before extracting the digits.

So I whipped up the attached worksheet and popped in the formulas. The answer: no, you don’t need to convert the number to text first.

Split Digits worksheet

The MID function has 3 arguments:

  • text is the text you want to extract characters from. For this example, it’s the four-digit number.
  • start_num is the number of the first character in the string you want to extract. For this example, it’s the digit number (1 though 4).
  • num_chars is the number of characters you want to extract. For this example, it’s 1.

You can examine the formulas in the spreadsheet for yourself to learn more. Download it here.

Hope this helps, Jamie!

Printing an Excel Function Reference Sheet

A how-to for Excel users.

My Visual QuickStart Guide books for Excel — from the edition for Excel 95 for Windows through the edition for Excel X for Macintosh — have always included an Excel function reference. In an effort to make pages available for other content, however, we’ve cut that appendix from the Excel 2007 edition of the book. I don’t feel badly about the cut, since this information is readily available in Excel Help and on the Web.

Here’s how you can find a function reference for your version of Excel and print it for your own hard-copy reference guide.

In Excel 2007 for Windows

  1. Click the Help button in the upper-right corner of the Excel window to display the Excel Help window.
  2. Enter function list in the search box and press Enter.
  3. Excel Help search resultsAmong the search results that appear, you should see an item titled “List of worksheet functions (by category).” Click its link.
  4. Function List for Excel 2007A help document titled “List of worksheet functions (by category) appears in the help window. It includes a complete list with brief descriptions of all Excel 2007 functions. You can read through this document and click links within it to learn more about specific functions.
  5. To print the reference sheet, click the Print button in the Excel Help window’s toolbar. Use the Print dialog that appears to set printing options and click the Print button.

Note that you may need a connection to the Internet to access the Function List from within Excel Help. And remember that you can always resize the Excel Help window to better read what’s inside it.

Get this Information Online

This reference information is also available online for some versions of Excel. Click this one of these links:

These pages contain clickable links to details about specific functions. They can also be printed from within your Web browser; use the Print command.