Oddly Mod…

Back a long, long time ago when I was attending Sheridan Elementary School, there was a popular gambling game among us boys that involved guessing “Odds” or “Evens” regarding the number of marbles the other boy held in his closed fist. Judging by how many times I spent my lunch money on buying new supplies of marbles to gamble with, I apparently wasn’t very good at the game.

Knowing whether entries are Odd or Even can also be helpful at times when working with your databases or tables.  Such instances can include street addresses, employee ID numbers, statistical studies, and several other pursuits.

So is there a Good Formula for identifying an Odd or Even number? As with so many things in Excel, there are several ways to do this, but the following are probably the most intuitive. Assuming your data is in Column A, you could use this formula and copy down your range:

1. =IF(MOD(A1,2)=0,"Even","Odd") This IF formula incorporates the MOD function to generate the results. MOD is an unusual function (It is so Mod, Man!) that finds the Remainder when you divide one figure by another.

~ Or ~

2. =IF((ISEVEN(A1) = TRUE), "Even", "Odd") This IF formula incorporates the (also a bit obscure) ISEVEN function to generate the desired results.

Odds and Evens: Knowing this information can be Just What You Need at times to ease your data analysis.  Now, anyone what to make a guess about the marbles in my fist?

Texting

Well, perhaps not exactly “Texting” but in this case of our topic today, TEXT. Yes, using Text in Excel.

Obviously, Excel is all about working with numbers. However, there are times when you want to be able to manipulate your data into specially formatted Text. One excellent solution is the TEXT function! By using specially formatted strings, TEXT allows you to specify how you display the numerical value in text with format of your choosing.

The TEXT syntax is: TEXT(value, format_string) 

Let’s look at an observable example: Inserting the format_string $#,##0 as the second part of the TEXT function provides a monetary formatting with a dollar sign and comma providing immediate recognition. 

TEXT can also be used with Concatenation (which is one of my favorite somewhat-obscure functions) to return user-friendly reports. In the illustration below, the following formula (note the space after “is”) was used in the merged cells of A6 and B6:

="The Net Profit is "&TEXT(B4," $#,##0")

 The Bottom-Line Statement in the merged cells is cleanly formatted and immediately understandable to the resulting profit. This ultimately well-defined declaration of the data may not be necessary for the tech-savvy few, but I can assure you that it will be well-received by those non-techy types!

Named Range Shortcuts

As I have noted on several occasions, Naming Your Ranges is most assuredly a Best Practice when creating or maintaining a database in Excel. Employing Named Ranges is not very difficult, but using some Shortcuts is, of course, well, quicker and easier. Let’s look at Five Powerful Shortcuts that will be sure to elevate your reputation as The Excel Guru:

1. Name a Single Range
Select the range, and then click in the Name Box (far left on the formula bar) and type a One-Word Name. Piece of cake…

2. Name All Your Ranges at Once
Select your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. Easy…

3. Navigate to a Named Range
Click F5 (Great shortcut for selecting Named Ranges!)

4. Edit a Named Range
To delete or edit a named range click Ctrl+F3. Couldn’t be simpler…

5. Display the Paste Names Dialog Box for Use in Formulas
Click F3 and use your arrow keypad to select (Extremely helpful when creating complex formulas)

Utilizing Named Ranges is an effortless way to expand your capabilities as an Excel user – Using Shortcuts with Named Ranges makes it easier.

More on Dates…

You’ll notice that the title of this week’s blog is “More on Dates”, not “Moron Dates” (a phrase I have overheard one of my more colorful colleagues use on an occasion…).

The fact is that what may Look Like a Date, may not “Play Nice in the Sandbox” with other dates that you have in your workbook. 

Example: Let’s say, for instance, that you have inherited an Excel workbook created by some Genius who no longer works in your company (I’m sure that his high IQ has led him to bigger and better things – cough, cough…).  Now you (a true Genius) wants to perform some Analysis that save the company countless hours and expense. The problem that this now-absent bright fellow has not documented his work, and unless you can Rely on the consistency of the way Excel will be handling the “Dates” in the worksheets, you Cannot Rely on the efficacy of your results.

So what is a well-meaning Excel Guru to do? DATEVALUE to the Rescue! Yes, indeed, my friends, DATEVALUE will calm your upset stomach, relieve that itch on your back that you can’t quite reach, cure that nagging doubt that you are being watched (you are, you know…), and make any Dates in your worksheet work in consistency with all of the dates therein. (Well, maybe I’m being a Snake Oil Salesman with some of the attributes, but it will make your dates play nice with each other…). DATEVALUE will instantly convert anything that looks-like-a-date into the standard Excel serial number, and you can then format it as you wish.  Formatting is, of course, rudimentary…

How Totally Cool is that! Mismatched dates can cause you a world of grief, but this simple DATEVALUE trick can save the day

One Note of Caution to Apple Users: For you MacBook Users out there, (I use Excel on one occasionally myself), Microsoft Excel for the Macintosh uses a different date system as its default (don’t ask me why…). 

Don’t Be Duped by Duplicates

The old adage, GIGO (Garbage In, Garbage Out) is a common premise to Good Database Management. Along with GIGO, it is also important to minimize or eliminate Duplicate Data in your records, as it can cause havoc when extracting information.

A Duplicates Issue can arise in many instances, and it is almost never intentional: Combining of departmental databases, updating data, common entry errors, and others.  Ever since Excel 2007, there are ways to Quickly Remove all of you duplicate data.

How to Remove Duplicates:

These days, it is amusingly easy to remove your duplicate records, and to do so with a Bit of Discretion:  

1.  Select the entire database (Caution: Do not select any self-generating key fields)
2.  Go to the Data Tools group on the Data tab, and click Remove Duplicates
3.  Select your database or table and click OK
4.  A Remove Duplicate information box will pop up (as shown below) giving you options as to how you want Excel to identify “Duplicates”
5.  Using our example as a reference, you can then choose to eliminate all duplicates by Month, Rep, and/or Sales. 

 When you first consider the task of Removing Duplicates to maintain the Integrity of your data, it may seem quite challenging. As with nearly all things Excel, however, once you know how to do it, it seems so Simple, you may actually Laugh…