Substitutions

My oldest daughter is working in an electronic orders fulfillment center until she can find a job in her field. She does not enjoy it but it does help her pay her bills. Our evening conversation today revolved around substitutions. Someone will order one product that she is unable to find in the warehouse and so the computer suggests an alternative. Sometimes those substitutions don't make sense and my daughter will note it but most of the time they work.

I thought about how I use substitutions in my daily job working with data. Sometimes I work with a small amount of data in a spreadsheet. Other times I work with large amounts of data in a data warehouse. Can I do the same operations in both tools? After doing this for many years, I can answer that question with a truthful, "Yes."

A few weeks ago I received a spreadsheet with 190,000 or so rows. I immediately needed to do some aggregate functions. That means I needed to count how many rows met certain criteria for text columns or come up with a sum or average for number columns. I had the choice of using either a database or a spreadsheet. To me it didn't make a difference which tool I used. Ultimately I inserted the rows into a database and performed my analysis there.

Working with a computer there are always a choice of tools you can use. While you may have grown used to Microsoft's Excel, you might not have that option with a new personal computer. If you have a Mac, the spreadsheet program Numbers is included in the operating system for free. You also have the choice of using Google spreadsheets. They all have their pros and cons but you should never feel locked into using a specific tool.

This is true for more complex software as well. My youngest son is a mechanical engineer and has access to a very expensive computer aided design (CAD) program called SolidWorks. It can do a lot. There are also a number of less capable products that can tackle any personal projects he decides to start without the exorbitant cost. When I needed to create very specific candle holders for one of my wife's Christmas decorations, I used TinkerCAD. I learned it easy and quickly had the design I needed for my 3D printer. There are a number of even more feature-rich options should you need them. The trick is knowing how to find them.

With the ubiquity of open source software and vast array of commercial software choices, you should never feel there is only one way to get something done on your computer. There are always multiple. The trick is learning how to use Internet search engines and sift through the bad suggestions to find good ones. Perhaps that is a post for another day.

Excel Trick: The UNIQUE Function

Yesterday I found myself spending more time in my Excel sheet with 32,000 rows and 44 columns. One of the columns listed video games. As there are a finite number of video games that is much less than 32,000, I wanted to get a list of them.

I did a quick search on the Internet and came across a large number of ways to list out all the unique values in a column. Some seemed overly complex. I typed one formula into the spreadsheet cell only to have it do nothing. Eventually I tried Microsoft's site and discovered the UNIQUE function. It is as simple as it sounds. You put it in a cell with the range of cells that contain your values and it creates a list with each unique value filling that column. It is as simple as using the following fromula:

=UNIQUE(B2:B32000)

If I put the formula in cell C2, all of the unique video game names will populate the C-column. It worked pretty well and I then had a list of all 800+ video games in the spreadsheet.

Excel Trick: Computing an Average of Filtered Data

As everyone in the office seems to be out early on Christmas break, I am using all this time without meetings to do some analysis work. Yesterday I did a lot in Excel and came across yet another trick that I want to remember and so I thought I would put it here. Hopefully it helps somebody else out there.

The spreadsheet I am playing with has over 32,000 rows and 44 columns. Some of those columns don't have any data and so I have been filtering the rows to see just the data I am interested in analyzing. Yesterday I created a filter to see all the rows that had values in one column and reduced the number of rows down to 16,000. That cut my data in half but is more data than I can casually scroll through.

I removed my first filter and filtered on another column and got the number of rows down to 4,000. That is approaching an acceptable amount of data to review. I wanted to reduce it even further and so combined the 2 filters and reduced the amount of data to 1,500 rows. These rows turned out to be key for my analysis. Now I needed to average the values in a 3rd column.

I tend to use Excel a lot but don't consider myself a power user. Well at least not yet. I used the AVG function and got a number. Then I removed my filters and discovered that average did not change. That means that the AVG function works on cells even if they are hidden by a filter. Instead I needed to use the SUBTOTAL function. It only works on viewable data and can be used for a number of different calculations.

The first parameter in Excel's SUBTOTAL function is a number that signifies the mathematical calculation you want to perform. The numbers correspond to the following:

• 1 = Average
• 2 = Count of all values even if they are blank
  
• 3 = Count of all values that are not blank
  
• 4 = Maximum
• 5 = Minimum
• 6 = Product
• 7 = Standard Deviation if the data represents a sample
  
• 8 = Standard Deviation if the data represents the entire population
  
• 9 = Sum

There are more, but you get the idea. Personally if I was the creator of Excel I would have associated sum with the number 1 as I think a subtotal is a sum and not an average of values. I guess Microsoft never won any awards for being intuitive.

Once I used the SUBTOTAL function the math worked out and my hypothesis about the data turned out to be true. Furthermore I can go above and beyond the simple statement that customers in group A are more valuable than group B. I can say they spend 20% more and that is a significant amount.

A Neat Trick in Excel: VLOOKUP

Lately I have been doing a lot of data analysis. One of the tools I have been using is Microsoft Excel and I recently learned a new trick using VLOOKUP. I thought I would put the information here in case I ever need it again. Oh and it may help you as well.

Most of the time I work with data, I use a relational database. That allows me to join various tables to replace cryptic values with more descriptive ones. A classic example is that of an EMPLOYEE table that uses a department number instead of the name. The database will then have a lookup table that converts the number into the name. This saves a certain amount of space and makes for much more efficient queries.

You can do the same thing in Excel using the VLOOKUP function. In my spreadsheet I have 2 tabs with the first being the employee information and the second being the department. Let's assume the employee tab has the following information:

	A	B	C
1	Firstname	Lastname	Deptno
2	Matthew	Bennett	10
3	Mike	Smith	20
4	Sara	King	30

 Now let's assume that those department numbers translate to something much more readable like:

	A	B
1	Deptno	Name
2	10	Research
3	20	Operations
4	30	Finance

You can use the VLOOKUP function in the 4th column of the table to include values from the department tab. It has the following syntax:

VLOOKUP(LookupCell, TabName!StartCell:EndCell, PasteColumn, Match)

So the LookupCell is that of the number that we want to convert to descriptive text. In the example above, we would use the column C values. 

The next values to fill in are that of the lookup table. While we could have them on the same page of the workbook, I find it easier to list them on a different one and call it the "department" tab. There are only 3 values and so we would start in column A on the second row. We would then run to B4.

The PasteColumn corresponds to the column number on the lookup tab that contains the value we want to show. Our descriptive text is in the 2nd column and so we would use the value "2".

The Match parameter indicates if we want to use an approximate or exact match. This example uses an exact match and so it should be set to "FALSE". I haven't tried doing an approximate match and so I am not sure of a use case for setting it to TRUE. Perhaps I can play with it for another blog entry.

Now let's put it all together. In cell D2, I would use the following value:

=VLOOKUP(C2, department!$A$2:$B$4, 2, FALSE)

I would then copy that formula and paste it into cells D3 and D4.

If those dollar signs confuse you, that is to hold the values constant regardless of what cell you paste the formula into. Otherwise Excel will adjust the reference. In the above example, the C2 reference doesn't have the dollar sign and will be converted into C3 when you paste it in the C3 cell.

This really helped me with some analysis I did and so I hope it helps you too.

Apple's Numbers vs. Microsoft's Excel

For the past several months I have been using Apple's Numbers spreadsheet program since I had to give up my work laptop. On my work laptop, I used Microsoft's Excel and even after almost half a year I have to say that I prefer Excel. I'm sure part of that is because I have years of experience with one versus a few months with the other.

I would have to say that my biggest complaint with Numbers is that a new document with a fixed-size spreadsheet. The default is a table with only 7 columns and 22 rows. Sure you can add columns or rows but each addition requires the click of an on-screen button. There also is a button that allows you to grab the lower right corner of the spreadsheet and drag it to as large as you think you will need. I actually prefer Excel's way of allowing you to scroll right or down and having new columns and rows appear.

Another thing that takes a bit of getting used to in Numbers is the right-side format bar. I feel like this should give me greater control of how the spreadsheet is formatted. Unfortunately it feels more like wasted space. I like how Excel puts all the format control at the top of the spreadsheet allowing for more visible columns.

Where the two spreadsheet products are similar is with a lot of the calculations and functions. The formula "=sum(B2:B14)" works equally well in both programs and that is important. I would hate to have to learn a new way of entering formulas.

I moved a lot of my personal spreadsheets from my work laptop to my personal computer and Numbers does a pretty good job of reading them. There are some missing fonts that I keep getting errors about but I can deal with that problem easily. The error I get is "This spreadsheet has missing fonts." I think a bigger problem would be functions that are not supported and I haven't seen any of those.

I will continue to use Numbers as I like the price: FREE. It is included with the MacOS and updated regularly. Perhaps I will learn to embrace the differences in the future but right now I see them as annoyances.

Alternatives to Microsoft Word, Excel, and PowerPoint

Ever since I gave back my work laptop I have been having to work without Microsoft Word, Excel, and PowerPoint. This has forced me to look at alternatives to those 3 pillar applications. Fortunately there are a number of other solutions you can use. While you may have to fix a few issues due to missing features, these other solutions work well for most tasks.

My home desktop computer is actually a Mac Mini. The Mac comes with 3 alternatives to Microsoft's suite of office products. Pages is the replacement for MS Word, Numbers is the replacement for Excel, and Keynote is the replacement for PowerPoint. I have to be honest and say that Pages does a pretty good job of replacing Word but I prefer Word. I also have to say that I prefer Excel to Numbers. I have used both of the Microsoft products for years and am probably just used to them. However there are some differences that make me wish I still had access to MS Word and Excel. That being said, Keynote is much better than PowerPoint. This is one area where I prefer the Apple product. It has a much cleaner interface and just seems to do things better.

Don't worry, you don't need to have a Mac to replace Microsoft's Office software. There are a number of other alternatives. My favorite is probably LibreOffice. It used to be called OpenOffice and was owned by Sun Microsystems. OpenOffice was originally developed in Germany as a replacement for Microsoft Office when Sun purchased it. Sun then open sourced the software and it gathered quite the following. When Oracle bought Sun, the open-source contributors were afraid Oracle would ruin the product and so they created a fork of the project that became LibreOffice. Both are actually great replacements and work well.

I have done a lot of work helping both the LibreOffice and OpenOffice communities. I think they are very full-featured products and don't hesitate to recommend them as alternatives to Microsoft Office. Unfortunately I have been too lazy to load either of them on my Mac as the Apple alternatives also work and allow me to do anything I need related to my current job search. Perhaps when I have access to the Microsoft suite of products, I will continue to use the alternatives and save my next company some money.

Don't Sell Yourself Short

This morning I had to get up at the crack of dawn for a conference call that involved people from around the world. For the past couple of years I have been involved with a company that created software to quantify how salespeople are successful. Once you know why, you can then coach your other salespeople to be more like them. This morning's call was with our management and the management of another large company interested in buying us.

I sat in my office watching a Webex demo of our software thinking how glad I was that it was someone else giving the demo. I have watched plenty of software demos using Webex or GotoMeeting but have never had the pleasure of starting the demo. I'm sure I could figure it out, I just have never done it.

The whole experience made me think back to a spreadsheet I once saw at a friend's house. I thought I was a big Microsoft Excel user until I saw what my buddy was using to keep track of his daily bicycle rides. The spreadsheet was a work of art. There were lots of different colors with graphs showing day-over-day performance increases. I knew Excel could do all of that but had never seen anyone use all of those features.

Amazingly the person giving this morning's demo and my friend with the elaborate spreadsheet don't consider themselves as knowledgable computer users. Given the choice of rating themselves as novice, intermediate, or expert, they would both choose novice. I think they deserve better.

Anyone that uses a computer daily deserves more than a novice self score for computer proficiency. You may only use your computer for e-mail or writing a BLOG, but that gives you some experience. So if you see yourself as a computer novice but are a frequent user, then don't sell yourself short. The next time someone asks you about your computer skills, confidently reply, "I'm a BLOG reading expert."