It seems that Microsoft Excel* doesn’t know how to average. Or am I missing something? I showed to the lab postdoc and we both we were wide-mouthed. How can I be confident that simple Excel commands are producing the correct results? This is pretty important since I am composing a table of mean abundances (per collection) for 55 species for nine factor levels for publication! The rest seemed OK, but I’ll need to spot-check on Monday to remain confident. How did I figure out there was a mistake? One cell had a mean that was an order of magnitude higher than should have been possible with my a priori knowledge. See the graphic below and tell me that it is just me and I’m flipping out for no reason. Tell me I’m wrong and I have nothing to worry about. I do consider myself pretty fluent in MS Office products.
* Microsoft Office 2004 for Mac OSX, with all necessary updates.
Excel is working as it’s supposed to. A blank space is not a zero.
Yes, Microsoft could have decided to treat blank spaces as zeros, but they didn’t. Either did Origin or Google Docs.
Yeah, blanks are not considered in the function. You have to put a zero there if you want it “read” as a zero in computed in the average. That’s my experience working with it, anyway.
It’s actually really important to be able to distinguish between missing data and zeros, and this is probably the most sensible way to do it. Some stats programs use a special symbol of some kind to indicate a missing value to avoid confusion (like !NA), but it wouldn’t work well for a spreadsheet to have a blank sheet full of odd characters until you fill them in. OpenOffice.org uses the same convention.
Quick way to put zeros into those cells is to enter one zero, copy it, select the whole range of data, then “paste special” and select “add”. It’s much faster than entering them by hand.
I’ve used blank spaces for zeros for years with the correct result (I hope!). Which is why I was confused. I usually use Stata, which will automatically assume a blank is zero because I check a box that tells it to when I import data.
I also use OpenOffice for my windows partition in bootcamp, but can’t recall having this problem before. Like I said, I’ve done this for years with the correct result. Is it an option or setting? And I don’t want to hear my analyses and tables previously published are wrong, because they are not! But then, I might not have been using blanks, I don’t know. But if its true, then it makes sense why I got the answer I did. All the other cells have sensible answers. Gah!
This blank-not-zero is a feature if you like putting extra rows into your spreadsheet for readability, or if you’ve got missing data. I often have holes in my spreadsheet when I’m starting to look at trends, and they most certainly do not represent zeroes.
Back in the late eighties, I had the brief glory of being first to report a bug in some very early release of Excel: when (and only when) formatted in Helvetica, all the figures in a tightly-narrowed column would display as even numbers.
During that same project, after months of precise map-work, I discovered that my numbers were foo because two supposedly professional-grade rulers did not match up. It was a very epistemologically revealing year.
I notice that you’ve got a little green corner flag on the results for the average formula. When you click or hover over the little green flag, what does Excel ask you? It may ask you something akin to “Treat blanks as zero values?”. You have the same little green corner flag on the result two rows down.
Seriously. Who is being insolent here…
Fluent with power point???
A simple script will make all your needed blanks zeros with conditionals.
This is why I encourage everyone to never use excel for anything other than data entry. Everything else I use R (www.r-project.org) for and haven’t run into problems yet.
Gorilla, I don’t get your point about fluent with PP.
Kevin, I started using R for data analysis last year, but stopped until I finish my thesis. It was just too much with trying to figure a new program out while I was trying to make thesis progress. I hate MS Office but my dept. got it for the grad students for free so i use it. I should just switch totally to OpenOffice. Its better anyways, at least the word processor and spreadsheet are.
OK, did some research and it appears that it is just the “AVERAGE” function that has this issue. So in the words of Carlos Mencia, I’m a little dee-de-dee. I just replaced with zeroes this time.
Does anyone use Numbers? If so do you prefer it over Excel, or is the Open Office Calc program better or just as good as both? Opinions?
@Attack Gorilla: Yeah, geez, Kevin, what are you, some kind of moron? I mean, everyone knows that “a simple script” would fix all that.
@Kevin Z: Forget about zeroes vs. blanks (and definitely forget about Attack Gorilla’s, well, attack), how did you do that cool zoom-in graphic thingie?
LOL karen, a little Adobe illustrator and some screenshots.
Excel is more buggy than an Amish community. You’d think after all the years it’s been out they would have fixed it up by now, but they’re more interested in adding every single bell and whistle known to humanity than they are in addressing usability and bugs. Why would they be – they effectively don’t have to compete with anyone any more.
Openoffice does not accept blanks as spaces either. And it shouldn’t this is a feature, you should always be explicit with ALL your data, don’t take short cuts.