Roman Numerals

Happy Boxing Day, All! I hope you are enjoying the holidays!

The old adage, “All work and no play makes Jack a dull boy”, is as true today as it was when it was first published in 1659. Therefore, this week’s blog is being devoted to a bit of Geeky Fun. Although of no real practical use, it can be interesting, (once again, in a geeky sort of way), to play around with Roman Numerals.

Interestingly, there is a Roman Numeral function that is built-in to Excel. Why that Microsoft has done this is not entirely clear from a practical standpoint.

Practicality, of course, can be overrated, and it is readily apparent that Pro Football, Hollywood, and the Olympics have all used Roman numerals on a regular basis. If you also wish to do this sometime in Excel, (You can even use it for your next quarterly report to your boss, i individual has a really good sense of humor), you can use the ROMAN function.

Try it out! For a Classic Numeral, (other formats are available, but who needs them…), simply enter a value in cell A1 and type, “=ROMAN(A1)” in cell B1. Hit Enter and Presto, a Roman numeral of the A1 number! Now you will be all set if the NFL needs someone to come up with the name for the next Super Bowl!

The RAND Function & Excel Slot Machine

It’s time for a little review and some Fun! Let’s look at the fascinating RAND function. This intriguing and useful function returns a Random Number that is Greater Than or Equal to 0 and Less Than 1. Each time your worksheet recalculates, (by reopening or forced recalculation by pressing F9), the RAND function returns a New Random Number.

It should be noted that some hard core statisticians have voiced concerns about the true Randomness of the RAND function (it is prone to sequential correlations if large runs of numbers are taken), but it suffices for nearly all but the most demanding statistical applications, and if fine for us mere mortals.

The Syntax for the Rand function is simply:

RAND( )

If you want to create a random number between two numbers, (where a is the smallest number and b is the largest number), you can use the following:

=RAND()*(b-a)+a

If you want only Whole Numbers you can use:

RANDBETWEEN()

For example, =RANDBETWEEN(1, 500) will produce a Random Whole Number between 1 and 500.

There are countless statistical applications, of course, but you can also use it in some entertaining applications. For example, some Excel Enthusiasts have used it to create Tetris-style and Dice-Rolling games in Excel.

I have used the RAND function in conjunction with other functions and graphics to create a Slot Machine in Excel (send me a request at excelenthusiast@gmail.com if you would like a copy of the Slot Machine spreadsheet). I will be happy to send a copy to you.

The RAND function. Great for use in statistical applications, building games, slot machines, and other Fun Stuff!

Cheers!

Navigating the Seas of Excel

It’s Back to Basics Week, so today we are going to review the topic of navigating in Excel. Sailing the Seas of your Excel worksheets can be drudgery for any Excel sailor, so we all need a few simple tricks. Imagine that you wish to go to your last entry at the bottom of a list that contains 20 records, scrolling to where you wish to go is simple and effective. When you have a list containing 20,000 records, however, it can be more than dreary.

As any Slick Excel Seafarer knows, keyboard shortcuts rule when it comes to saving time sailing from one location to another on your spreadsheet.

Here are a few Navigation Tricks you can make without ever touching a mouse (or getting sent to the brig):

1. Control / Down Arrow: Goes to last cell in column with data

2. Control / Right Arrow: Goes to last cell in row with data

3. Control / End: Goes to last row, column and cell

4. Control / Home: Returns to cell A1

Mouse Tricks (Every ship has Mice)

Another way to navigate to the end of your data (whether in a column or row) is to precisely hover your pointer on the adjacent Border of Cell in your range and double-click. If you wish to navigate to the last cell in a column of data that starts with cell C1, for example, you can select C1 and double-click on the Bottom Border of the cell.

Another Alternative (It's always good to have Choices)

When you know the Exact Address of some remote cell, simply enter the address (e.g. G2100) in the Name Box and Avast Me Hearties; you are swiftly transported directly to that location.

So, put on your Pirate Patch and try a different way or two of Navigating on the Excel Seas.  You can sail to where you want to be in The Blink of an Eye, Laddie!

Custom Lists in Excel

"Have it your way" does not only apply to old Burger King ads, you can also create Custom Lists in

Excel that meet your special requirements. Excel provides many familiar Built-in Lists, such as Sunday-Saturday, January- December, etc. You may find your own, user-defined lists very practical, however, as they can be used for sorting or Auto-Filling your data.

• Let's say, for instance, that you want to want to make a Special Sorting Order for the Sales Personnel in your company: Vice President of Sales, National Sales Manager, Regional Sales Manager, Department Sales Manager, and Sales Representative.
• Perhaps you want a Custom List by region: Northeast, Central, North, South, Northwest, Southwest. It is all quite easy.

Assuming your custom list is not too lengthy, you can type the values directly in the dialog box (if your list is long, you can import it from a range of cells.)

Using Excel 2007 for this Demonstration, Here is the Easiest Way to Do This:

1. Click the Microsoft Office Button, (go to File/Advanced tab in later versions ), and then click the Excel Options button
2. Click the Popular category, and then under Top options for working with Excel, click the Edit Custom Lists button
3. In the Custom Lists box, click NEW LIST, and then type the entries in the List entries slot
4. When the list is complete, click Add
5. Click OK twice

 

Presto! The items in the list that you selected are added to the Custom Lists box.

Cool Feature: The Custom List is added to your computer's registry, so it is available for use in other Excel workbooks.

Try it out! It takes only a minute or two to make your own, reusable Custom List!