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.

Spelling Checkers Don’t Work if You Ignore Them

Reminding my brain not to block out the red underlines that indicate potential spelling problems.

Check Spelling as You Type has been a built-in feature of many word processors for years. It’s now in most applications I use — including my Web browsers, for Pete’s sake! — and the red squiggly or dotted underlines are an integral part of my writing life.

If you’re not sure what I’m talking about — do you live in a cave? — I’m referring to the feature that indicates when a word you’ve typed may have been spelled wrong. This is supposed to flag the word so you can check and, if necessary, correct it.

A Blessing…

I vaguely remember when this feature first appeared in Microsoft Word years ago. It was a blessing — and a curse.

I initially loved the feature because it often identified my typos. I’m a touch typist and can get up to 80 words per minute when I’m tuned in. But those aren’t always error-free words. Check Spelling as You Type was a great feature for finding typos as I worked, eliminating the need to run a spell check periodically or at the end of document creation.

As you might expect, it also found spelling errors. My spelling was always pretty good, so it usually found more typos than actual spelling mistakes. But that’s okay. An error is an error and I want to remove all of them from my work, whenever I can.

…and a Curse

But over the years, I’ve found some problems with the Check Spelling as You Type feature — and spelling checkers in general.

The feature does not identify all typos or spelling errors as errors. For example, suppose you type bit but you really meant but. A spelling checker doesn’t see any problem with that, so it won’t flag it. That means you can’t depend on a spelling checker to proofread your work. (And yes, in case you’re wondering, a grammar checker would likely identify this as a problem. As well as the sentence you’re reading right now, because it isn’t really a proper sentence. And this one, too.)

So you’ve got a feature that makes you lazy by doing about 90% of the proofreading work for you, as you type. If you neglect to do the other 10% of the proofreading work, you could be very embarrassed — especially if you write professionally and editors expect your work to be error-free.

The unflagged error that zaps me most often? Typing it’s instead of its. At least I know what it’s supposed to be.

It’s worse, however, for people who don’t know the correct word. How many times have you seen people use then instead of than? There instead of they’re or their?

The feature has degraded my spelling skills. In the old days, before spelling checkers, I simply knew how to spell. If I wasn’t sure of the spelling of a word I needed, I looked it up in — can you imagine? — a dictionary. It made it worthwhile for me to actually learn how to spell words. Knowing the proper spelling saved me time in the long run.

But now, I simply type the word as I think it might be spelled and wait to see if it’s flagged. If it is, I use a context menu — Control-click or right click the word — to choose the word I meant to type. Yes, it’s convenient. But I seem to be doing it an awful lot more than I used to use a dictionary.

(Perhaps it’s also expanding my vocabulary by making it easier to use words I’m not as familiar with? There’s something there.)

The feature identifies any word it does not know as a potential spelling error. That means that if your document is filled with jargon, technical terms, place names, or other words that do not appear in a dictionary, those words will be flagged as possible errors. The word unflagged, which appeared earlier in this post, was also flagged. Is it an error? Or does my spelling checker simply not recognize it? Seems like a word to me, so I let it go.

And herein lies my biggest problem: I’m so accustomed to seeing words flagged in my documents that I’ve managed to tune out the red underline. (It’s kind of like the way we all tune out advertisements on Web pages these days.) This happened to me just the other day. I typed the word emmerse in a blog post. My offline editing tool flagged it with a red dotted underline — as it just did here. But for some reason, I didn’t see it. I published the post with the error in it. A friend of mine, who referred to himself as a “spelling Nazi,” e-mailed me to point out my error. I meant immerse, of course. He knew that. Readers likely knew that. But I got it wrong and I shouldn’t have. How embarrassing!

Spelling Checker

Here’s a look at the spelling check feature in ecto, my offline blog composition tool of choice. It works just like any other spelling checker. (And yes, I do compose in HTML mode.)

The correct way to go about this is to look for every single possible spelling error and resolve it so those red lines go away. That means learning or adding the unknown word so it’s never flagged again or ignoring it so it doesn’t bother you in this document. All of this should be done with the appropriate menu command. Simply telling your brain to ignore a problem just sets you up to be blind to it when it occurs. That’s not how the software was designed to work.

The Point

This post has a point — most of mine do — and here it is: spelling checkers, including any Check Spelling As You Type feature, are only as good as allow them to be. Use them, but don’t depend on them. Follow up on any flagged words and resolve them using the software so the red underlines go away.

Spelling checkers are just a tool. Like any other tool, it won’t help you if you don’t use it correctly.