Monday, November 19, 2012

ABC: I do not Excel at this.

For the past three weeks (well, two since I really got at it) I have been working on a consulting project, updating a report I wrote 10 years ago.  It concerns the agricultural situation in a country, narrowed down to one specific province and  a couple of counties.  What has changed in the past ten years and why and what are the implications for the future?

In one sense it is much easier, with Google.  Also the country has an excellent free statistics site with massive amounts of data that can be queried 16 ways to breakfast.  I have an in-country contact to help me find stuff I can't Google.  Google Translate does a fair enough job for my needs.  What could be simpler?

Did you know that European and North American numbering formats are different?  Exact opposites in fact?  I knew that but was not prepoared for how much fun it was going to cause.  My computer is set to NA default.  Thousands are separated by commas, decimals by periods.  ALL the data I am downloading is European, with thousands separated by periods and decimals delineated by commas. Guess what my computer thinks?

I was not going to change the default on my computer as I have no idea how much problems that will cause with the millions of spreadsheets I have on file.  I tried changing it for each spreadsheet.  You can do that if you are entering the numbers yourself but if you are downloading spreadsheets it doesn't work.  I Googled for help and learned a few tricks, some of which actually worked.  Using Remove (the "."/Replace (with a blank i.e. nothing) eliminates the thousands separator.  BUT if the number is 19.000 or 2.130 Excel has already lopped off the extraneous 0s so you have to do those by hand IF you catch them.

Now I have had lots of fun learning how to make new charts.  Tables of thousands of numbers are useless.  Pictures show information.  Organizing numbers to make pretty pictures is one thing.  Making Excel draw the charts I want is another.  Google to the rescue again.  I can now make two kinds of charts which are not on the Wizard. Column charts with primary and secondary Y axis.  Stacking column charts with several columns over one point on the X axis.

Be impressed, OK.

This suggests some things I need to check further

This told me nothing new and I had to make a different chart before I learned something - cows milked and milk production are NOT real data, they are formula based.  Damn.

7 comments:

  1. Thanks for reminding me that I don't really miss consulting as much as I thought I did. I became far too intimate with Excel. If he were a man I could have reported him for partner abuse. Your charts will wow them....but most likely after admiring your work, maybe even reading your report(!), they'll probably put it all into a drawer until it's time for the next 10-year review. (Am I too jaded, do you think?)

    ReplyDelete
    Replies
    1. I've worked with these folks off and on for over 12 years. The CEO reads my reports with a red pen in his hand and no matter how carefully I have proofed it, it always comes back. It is his way of concentrating on the details. I know they re read and used in decision making. These are GOOD clients.

      Delete
  2. I'm sure you excel with international figures, as soon as you figure it out.

    ReplyDelete
  3. For a second there I was delighted to see such a rise in cattle and production until I read your caption. There used to be something like 137 cattle farms in the Puget Sound area. We are now down to just 23. :-(

    ReplyDelete
    Replies
    1. Cattle numbers and milk production are increasing. The country is chronically short of milk and the government is actively encouraging importing of purebred cattle. Management is the problem as milk production per cow is about half what it could be.
      Numbers of farms with beef cattle in Canada are decreasing constantly but herd sizes are increasing in proportion. It takes as much work to run 300 cows as it does 30.

      Delete
  4. I am very impressed with your ability. You could give lessons. This sort of things takes me eons to adopt but there's a sense of satisfaction when it works and a sense of "who needs it" when it doesn't. Good job!

    ReplyDelete