Two topics: hurricane weather and the need to double check your Excel results.

This post is going to be very short because I have a headache, probably from lack of sleep over worry about this storm. We're supposed to have tremendous amount of flooding over the weekend - between 15 -20 inches.  The weather is coming in via hurricane and it is supposed to stall in our area for three or four days. Being stationary for that length of time means a potential for catastrophic flooding. The weather warning descriptions made the flooding sound like it was going to wash everything away. But then later I read an article about how our city once had 40 inches of rain and I don't even remember that. So maybe we won't float away but it will probably still be bad. Some homes will most likely be flooded.

Okay, last week I said I would try to finish up notes on this book The Second Machine Age but instead, I will post a quickie post, and then spend the remainder of this weekend finishing up my notes and maybe do a picture. I'm hoping that by doing other stuffs will take my mind off the weather news. If I do finish my notes and I still have electricity, then maybe I will do a second post.

So, this week I'm going to talk about double checking your work in Excel. Earlier in the week, my HR VP needed some numbers pulled together to enable her to do a comparison between what we do today and what they are thinking of doing in the future. One set of data will be coming from 26+ files and another set be from a sheet and in a completely different format.

Below, on the right you will find a copy of the spreadsheet with some notes on the left. I'm trying to compare A against B and note that B is very different from A. Both numbers are hard coded in this spreadsheet which was not the case in the real problem. Note that in order to make A be in the same data type as B, I had to divide it by 100, as done in cell H8 (to see H, you will have to scroll over to the right). I ran out the number of decimal places and you will see why later on, but in cell H8, dividing A by 100 will give you a straight .1988. So that's not a problem.

In cell H10, I'm subtracting B from A/100 and I get a straight zero. Again not a problem.

But I wanted to show both a negative number and a positive number as positive, just to make it easier to read the numbers, so I did absolute value of the difference in cell H12. It gave me a non-zero number. When I was double checking the numbers, I saw a whole bunch of these variances when I knew they were zero. And it would have been obvious if I had sent the data like that.

Below H12, you will see various efforts to try different to see which gave a clean result. Look at cell H23! I'm doing almost the same math as cell H21 but I'm adding a minus in front of the calculation.

Cells H25, 28 and 29 show what to do to get around this problem: mainly use a rounding function in the right spot, in this case, whenever I was doing division.

Okay, I think I'm done for this post. I need to get some sleep; my headache is bad.