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!

Picture Charts!

There is no doubt that Excel provides a considerable array of Chart Types to choose from. There are times, however, when you may want to Add a Bit of Pizazz to your charts. One of the coolest ways to do this is by Replacing the Series Element (Columns or Bars work the best) with a Graphic.

You can easily Add Impact to your charts by doing the following:

1. Copy a Graphic (simple images work the best) to your clipboard
2. Create your Chart using columns or bars
3. Select the Chart Column or Bar Series
4. Go to your Home Tab and Paste the image
5. Bonus: Format the Data Series by going to Series Options and choosing 0% Gap Width

Since I live in southern California, I chose a Surfboard Graphic for my Surfing Days Chart above. To demonstrate the difference this can make, I did a Side-by-Side illustration below. I chose a Bag of Money to replace the Boring column to depict the sales by month.

Picture Charts. Another way to Add Interest and Impact to your Excel worksheets.

Surf’s Up!

Excel on the Cloud

As any technophile knows, The Cloud is the Major Buzz these days. It seems that everyone is scrambling to say, "Me Too" in their quest to capture your traffic and business via cloud-based apps or, as with Microsoft's Office 2013, span both traditional approaches and the cutting edge.

My current favorite Cloud Solution is indeed supplied by the venerable Microsoft. By placing your Excel workbooks on SkyDrive, they can be shared (at your discretion) with the world. All you need to do is go to your SkyDrive, right-click the document, and then click Share. Type the email address of the person you want to share the workbook with, and Bamm! Done!

But Wait! What makes it Really Cool in this instance is that the other parties with whom you are sharing Do Not even have to have Excel on their computer! That Totally Rocks!

Other favorites include the iPad Numbers app. Workbooks created or edited in Numbers can be converted to the Excel format, and shared on iTunes, DropBox, and other cloud-based facilities.

Google Docs is also hugely adaptive and beneficial for anyone (or any organization) who is on a budget, and still wants reasonably high functionality and ease of collaboration.

So, what about me, am I personally Truly embracing the mobile world and the cloud? Well, as an evidenced by this week's blog which I have written entirely on an iPad while awaiting an appointment, yeah, I guess you could say that I am!

Happy Thanksgiving, All!

What’s the Dif, Man?

Back in July of 2010, we looked at the obscure, and curiously undocumented function, DATEDIF. Microsoft, in all of its wisdom, (small amount of gentle sarcasm here), has chosen not to include documented information on this Essential Function in Excel. Over the past 5 years of this blog, this topic has been one of the readers’ favorite.

As any longtime user knows, the way Excel handles dates can be a bit puzzling at times. Finding the Difference between two Dates, for instance, is not readily intuitive. Here is where DATEDIF shines!

The Syntax of the Function is as Follows:

=DateDif(First Date, Second Date, Time Interval)

Where the Time Interval is expressed as follows:

d (Days) = Number of days between the dates
m (Months) = Complete calendar months between the dates
y (Years) = Complete calendar years between the dates

Important Note #1 (you don’t need this craziness): The Second Date must be greater than the First Date, or you will get a Number Error.

A novel application of this function is to nest the NOW() function into it and calculate a Person’s Age. The NOW() function returns the Current Date and Time, and when used with DATEDIF, it can produce an Excel calculator that many find amusing. (Note: the “BirthDate” can refer to an easily changed Cell Value):

=DateDif(BirthDate, NOW(), “y”)

Important Note #2: If you put the Time Interval in the function directly, be sure to put “quotation marks” around it (e.g. “m”). If you put it into the formula via a cell reference, do not use the quotation marks (e.g. the cell should contain m, not “m”).

You may well find a great many ways to use the DATEDIF function, which may lead you to wonder, Why is this Terrific Tool not documented? Ah, well, who am I to question the great Microsoft…