Windows 7 Wireless - Poke the exclamation point

I spent a good two hours today learning how to make Windows 7 wireless network connectivity behave more like I expected.

The problem:

Windows shows a yellow exclamation when my internet connection is interrupted, but it remains there even after I'm sure my connection should be back up.  A Windows XP computer and and an iPhone both survive the interruption, but my Windows 7 notebook is confused and keeps my web browser from connecting.  It is a serious interruption in my day to restart my machine completely.  The goal was to force my network connection status to be up-to-date, so I can continue working without restarting.

The background:

I have an internet connection that is a little flaky and will fail for 2 min about 3x week.  The root cause is probably the service provider or my Linksys router, haven't sorted out which it is yet.  

Solved:

The culprit seems to be the "DHCP Client" service not being correctly controlled by the "Network Store Interface Service".   Restart "DHCP Client", and Windows 7 will be forced to figure out the actual status of my network connection, including the yellow exclamation point in my systray.

Instructions:

1. Go to Start->Run, then type "services.msc" (remove the quotes) in the run box.  

2. In the list that appears, right click on "DHCP Client".  

3. Click restart.  

4. Click on "Yes" on the warning box that appears.

  

Wait 30 seconds, and you should see the yellow exclamation icon disappear in your tray.  If your internet connection is truly working, your Windows 7 machine should now be able to search the internet.

Solved in detail:

I found that Windows 7 contains the list of services, below, that all seem to be directly related to my wireless connection. I know a few of these from Windows XP, but several are new, and have confusingly overlapping descriptive names.  I could make educated guesses to which did what by reading the service descriptions, but it took 2 hours of trial and error to find which services actually affected my connectivity.  More importantly, windows seemed to block on the yellow exclamation icon (bang symbol) until the connectivity returned.

Windows Services related to this issue:

1. Network Store Interface Service -- This seems to be the actual controller for all network connection activities.  Many services depend on it, including DHCP client.  Wrinkle 1: When I restart this service, it will stop the DHCP service.  But, when the starts are fired, it will not start the DHCP service.  This causes the internet to stop working on my computer.  See (9) below for more.  Wrinkle 2: When restarting this service, (6) below falls into a defunct state, which throws the error "Error 1051: A stop control has been sent to a service that other running services are dependent on.".  This makes a mess of my Windows 7 connection state.  See (6) below for more information.

2. HomeGroup Provider - depends on (1) above, but doesn't interrupt internet connectivity.  Restarts with (1).

3. Network List Service - depends on (1), but doesn't interrupt internet connectivity. Restarts with (1).

4. Network Location Awareness - depends on (1), but doesn't interrupt internet connectivity. Restarts with (1).

5. Network Connections - depends on (1), but doesn't interrupt internet connectivity.  Restarts with (1).

6. Computer Browser - depends on (1), but doesn't interrupt internet connectivity.  When restarting, this service will fail to stop with a Error 1061: "The service cannot accept control messages at this time." message.  I found that if I used the Sysinternals Process Explorer tool as an elevated user, then found the process that contains this service, then killed the containing process, I could then restart any of the services 1-9 above without issue.

7. Workstation - depends on (1), but doesn't interrupt internet connectivity.

8. IP Helper - depends on (1), but doesn't interrupt internet connectivity.

9. DNS Client - depends on (1), but doesn't interrupt internet connectivity.

10. DHCP Client  -- This was the issue.  When not started, a bang (exclamation) shows on my network bars icon; when started, bang immediately dissappears, and my internet connection works perfectly.  Stops with (1), but does not restart with (1), even though (1) appears to start successfully.

PS:

As background, I based all my troubleshooting on an Acer Aspire One, 1GB RAM, with Windows 7 Starter Edition.  I expect that the problems above are Windows 7 specific, though, and not necessarily related to the Acer or Starter Edition brands.

Save time in complex spreadsheets - Evaluate Formula

You've probably been there.  You have a spreadsheet formula that is complex, and it is not giving you the answer you expect.  You have looked at this spreadsheet for longer than you want to, and you don't know where to look next to find the problem.

1. First, congratulations.  You realized it was the wrong number before you sent the results out to the rest of your team.

2. Second, you still have a problem.  You can't find the error in the middle of all the parentheses, functions, and other clutter.

In comes "Evaluate Formula" to help solve the problem.  Evaluate Formula is used to "step through" the portions of your formula.  "Step through" is a common programming term that means to do something one step at a time, so you can find the problem in the middle of a multi-step process.  

Usage:

Each time you push Evaluate, one single portion of the Excel formula in your current cell is executed.  

Press Evaluate, then look to see if that portion of the formula pulled in the numbers in Excel that you expected.  Excel highlights in blue in the upper box which portion of the formula it just ran, then shows the result in the lower box.  If that intermediate result was correct, press evaluate again to see the next portion.

Repeat until you find the number Excel is using that you didn't expect.  Correct that portion of the formula, and then Evaluate again to make sure that new cell formula gives the correct result.  This process can take time, but it also gives you a step-by-step task list of items to check.

How to find it:

Evaluate Formula is found under Tools->Formula Auditing in Excel 2003.  Search for "Evaluate Formula" in Excel help to find it in newer versions of Excel.

More information:

Additional helpful details can be found found at Mr Excel (a straightforward, reliable source of help) and on Microsoft's website.

Under-appreciated REPT() - Quick Lightweight Excel Data Analysis

One of the most frequent tasks I face is to start forming a point of view on 50-1000 rows of information.  Common data sets are a large list of projects, a list of sales figures, an interesting bit of log file data, my own list of credit card transactions, or pricing patterns in a large corporation.  

I almost always use a variation of REPT() to get started.  I haven't seen anyone else use this method in the corporate world, oddly.  The common solution I see many people use is to sort the data, scan it visually, attempt to coalesce the data, then form an Excel chart around it.  Excel charts are difficult to place inline with other data, though, and Excel charts only show about 200 different bars before they become unwieldy.  

REPT("|",100) will draw a bar 100 units tall very simply.  For me, this solves 80% of my charting needs when I'm looking at new data.

Below is a bar chart of the 700 major IT projects the Federal Government has pursued in 2010, from data dot gov.

It becomes straightforward to see the relative size of 700 projects.  I simply added the portions in green to the raw data set in about 5 minutes, then sorted.  The full descriptions are available in the xls version of this data below, and they contain some interesting descriptions of government initiatives.

I am an enthusiastic reader of Edward Tufte's work.  He formed the idea of a sparkline -- intense, simple, word-sized graphics.  A Frenchman who was writing macros around the sparkline first turned me on to the REPT() function in Excel as a way to visualize data in a bar chart fashion.  I've lost that link, but now Juice Analytics has done a very complete piece on the idea.

Excel 2007, 2010 now have a sparklines like concept, called "Data Bars" in Excel 2007.  For me, I've found that plain old REPT() is still the quickest, most flexible way to move my analysis forward.

MVPS

Microsoft has a long list of existing MVPS.  For inspiration, it's worthwhile to take an hour or two and read through what others are doing to be creative in Excel:

http://www.mvps.org/links.html#Excel

Excel HowTo

J-Walk is considered one the of the best.  After doing a lot of googling, I found he was constantly referenced by others.  I'd recommend reading through his blog for a while.  Some Excel sites you find are riddled with ads, and occasionally incorrect, so I'd start here.

Here's is John Walkenback's "for dummies" book.  Several of these are probably at Barnes and Noble if you wanted to go read up one evening to get an idea which one you like.

Many offices will still have Excel 2003, which is just fine for 90% of tasks. 

http://spreadsheetpage.com/index.php/book/C5/

The later versions of Excel make things easier and better looking.  If you have Excel 2007 or Excel 2010, John has written a book about that, too.

http://spreadsheetpage.com/index.php/book/C45/

Why Real World Excel?

A relative of mine has become the go-to guy at his work for Excel questions recently.  I was asking about his work, and we started talking about Excel.  He has made a name for himself by showing useful patterns in warehouse data at his work.  He did such a nice job that the company created a new position for him to do data analysis full time.  He is being bombarded by Excel books and "Is this possible?" questions at work, and he was asking me how I learned Excel.

I'm a software engineer by training.  As I have worked, I have found that no matter the problem, Excel usually becomes a tool in some part of delivering a piece of work.  I've seen that virtually everyone around me uses Excel extensively as well -- project managers, marketing analysts, salespeople, investors, engineers, administrators.  I expect many other professionals will also go through the advanced Excel learning process, and so I'm putting my work online with the expectation that it will save someone else time one day.