Silicon Thumb

Since the 1980s, I've worked with computers, watched them grow, shrink, change and improve. I've worked with a lot of users and solved a lot of problems in that time too, so I thought this would be a good place to share some of the random things I've found and solved. If you have some odd problem, email me. If I can figure it out I'll post the answer here.

My Photo
Name:
Location: Mansfield, Texas, United States

I am a veteran computer geek, but I prefer the term 'Hired Gun', since that gives the (misleading) impression that I know what I'm talking about. I have worked on all sizes of system as an engineer, developer, technical support and operations, and at all levels from Operator to CIO.
I have some certifications, but what they are depends on what Microsoft is calling them this week.

If you have a question, and don't mind the answer being posted, email me here, removing the spam stopper.

Friday, November 03, 2006

Excel's Blank Look

Hello everyone,

This blog is going to be a collection of odd, (what I hope will be) useful and sometimes funny stuff I've found (and continue to find) when dealing with computers and their users. Whether this log ends up being mainly informative or more for entertainment is anyone's guess, but your feedback will help.

To give an example of the kind of things I will be writing here, I will start with something that happened just the other day.

The cash manager at work called me as I walk past his office to show me his horrible nightmare. An Excel workbook with 30 consolidated spreadsheets that had a #VALUE in one cell for no good reason at all.

He had a statement which basically did a simple formula pulling a cell from each of the sheets and adding them up:

Sheet1!L41+Sheet2!L41+Sheet3!L41... etc

What made it interesting was that all of the cells referred to in the formula appeared to be blank, so why wasn't the consolidated sum showing zero?

Now the first thing to remember about EXCEL is that things are not always what they seem. the concept "What You See is What You Get" may be accurate to an extent, but with Excel you should probably add "...But What You See Ain't Necessarily What You Think It Is."

In this particular instance, the first assumption - that the cells were blank - was wrong. Well that's not true. They were blank, just not empty. In fact most of them were empty, but one actually had a blank (ASCII 32 or Space Bar depending upon your level of geekiness) in it.

Now, for reasons known only to Microsoft, Excel handles space characters differently depending on how you add numbers together:

If you use the sum() function, it will ignore them or treat them as zero.
If you use arithmetic operators (+,- etc) it balks at them and treats them as a character.

Here is an example of what I'm talking about:


Click to enlarge


As you can see in the above example, Excel will not do a successful addition if one of the cells being added is a Space, unless you use the sum() function.

In the cash manager's case, using sum() wasn't an option as he was going across multiple worksheets, so we had to go through each worksheet and remove the blank wherever we found it. Fun stuff, but when we got to the consolidated worksheet, sure enough it worked!

Ok, well I guess that'll do for this first entry. Leave a comment if you feel like it. I will post more next week. Have a great weekend, TGIF!

0 Comments:

Post a Comment

<< Home