Excel's Blank Look
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