Why does this formula work?
=SUM(B2:B11:D2:D6)
it’s equivalent to
=SUM(B2:D11)
The creator of this formula wanted
=SUM(B2:B11,D2:D6)
It’s funny how formulas can trick you. The first formula entered was
=SUM(A2:A11:C2:C6)
and since B2:B11 were all zeros, it appeared to give the right answer. To be fair, it did give the right answer, but it appeared to be summing the correct cells, which it was not. I thought he had discovered a new union operator or something. Then the formula was copied one cell over. With columns B and D being all zeros, it was perplexing how he was getting a non-zero result.
Anyway, it seems like that first formula shouldn’t even parse. One colon per argument should be the rule.
On another note, should I say “B2:B11 were all zeros” or “B2:B11 was all zeros”?
You asked: should I say “B2:B11 were all zeros” or “B2:B11 was all zeros”?
Either is correct in this case. You can read the shorthand notation “B2:B11? as “cells B2 through B11?, in which case you use “were” (multiple cells, so plural verb conjugation), or read it as “the cell range B2 to B11?, in which case you use “was” (one range, so singular conjugation).
Curt
“B2:B11 were all zeros” or “B2:B11 was all zeros”?
My take is that “were” is the correct usage because you are using the plural “zeros”. In my mind, “zeros” implies multiple cells, hence “were” is preferred.
Either “B2:B1l were all zeros” or “B2:B11 was zero” would be correct.
David
“The creator of this formula wanted
=SUM(B2:B11,D2:D6)”
In Excel 2007 B2TR, the formula “=SUM(B2:B11:D2:D6)” does in fact sum the discrete ranges B2:B11 and D2:D6, not the entire range B2:D11.
Rob
How about we ask MS to build a utility to detect when formulas such as SUM(B2:B11:D2:D6) are used? They could it the COLONoscopy function.
(Sorry – couldn’t resist)
This doesn’t look illegal to me…
Ok, it does look awkward, but my first impression was that “B2:B11:D2:D6? should be evaluated as if it were “(B2:B11):(D2:D6)”. And I’m pretty sure that this is what is happening. Try the following in VBA and they both have the same effect:
[B2:B11:D2:D6].Select
[(B2:B11):(D2:D6)].Select
When it comes so human readability, the idea of stringing colons together like this definately looks odd. But I can see why the parser does accept this.
Mike
I think it is worth reminding people that the Space operator can be used if the desired outcome was an intersection. (clearly not in this case as the columns were entirely different.)
E.g. =SUM(B2:B10 A4:E4) another reason to avoid spaces in any formulas
I would disagree with Curt’s interpretation in the second case:
“the cell range B2 to B11?, in which case you use “was” (one range, so singular conjugation)”
Although the cell range is singular, we have multiple instances of zero within this, and thus multiple instances of zero would apply – therefore we have plural zeros, and the plural form of the verb (ie “were”) would be appropriate. A singular would apply if the inference in the above was regards the total (e.g. “the sum of the cell range B2 to B11 was zero”).
If you were to refer to each entry individually, then using the singular form of zero, and thus the verb, would be permissable. This would depend upon the sentence structure…
e.g.
“the entries in the range B2 to B11 were zeros”
“B2 to B11 were all zeros”
“each entry in the range B2 to B11 was zero”
Yes, I am the sort of person who goes into stores to complain about their adverts for a:
“WON DAY ONLEE SAIL!!” *shame*
should I say “B2:B11 were all zeros” or “B2:B11 was all zeros”?
How about [“B2:B11” was zero] or [B2 to B11 were zeros]?
Getting back to the first question, it just comes down to user understanding of what they key – I have never seen anyone enter “=SUM(B2:B11:D2:D6)”, I have seen “=SUM(B2:B11,D2:D6)” (which is how I would enter it if asked to sum these two ranges).
More often than not, I see other people do this “=SUM(B2:B11)+SUM(D2:D6)” which I am sure is not as efficient (using 2 functions rather than 1) but does avoid any accidents!
I would expect the first example “=SUM(B2:B11:D2:D6)” to cause an error as it has more than one colon in the first number argument, I can’t think of any examples as to when you would do this…
In Excel worksheet formulas colon, :, is an operator. It makes expressions like
INDEX(A,i,j):Z100
possible. At least through Excel 2003, A:B:C:D resolves to the smallest single area range containing ranges A, B, C and D. If Excel 2007 works differently, that’ll be good for some very nasty compatibility bugs.
Would be nice if Microsoft provided complete documentation, wouldn’t it? Even when it doesn’t, there’s always the newsgroups: this has been known and mentioned in the newsgroups for years. FWIW, all of Excel’s operators may be applied multiple times in the same expression, so multiple : operators is no big deal syntactically. BTW, : is effectively commutative, and associativity doesn’t matter (similar to &).
“B2:B11 was all zeros” is the correct sentence. It boils down to subject-verb agreement. And since we all probably agree that B2:B11 is short for saying “the range B2:B11?, it is singular and uses the form “was”.
The fact that they were “all zeros” is irrelevant, since that is the predicate of the sentence. That’s like saying, “Bob has five cameras” should use “have” since he owns many cameras.
OK, I’m a grammar terrorist. On one of my first walks with my girlfriend (a language professor), I pulled out a permanent marker and corrected an egregious punctuation error on a park sign. Then she showed me the marker she always carries for the same purpose – we have been inseparable since.
“B2:B11 were all zeros” or “B2:B11 was all zeros”?
I agree pretty much with Curt. I’m a linguist (not a polyglot), so my answer is partially based on my studies of linguistics, but I also realize that his answer is not the only possible answer. Because English is a creolized language (Angle and Saxon, then with Normandy, then …), several systems are all available to us.
Dave, while wrong, does have a point: the use of the word zeros does imply multiple usages. Yes, zeros is in the predicate, but the word does help us realize whether the originator (Dick K) was more likely to be thinking B2:B11 as ‘the cells from B2 through B11’ as vs ‘the range B2:B11.’ Mike points this out. And Simon also distinguishes B2:B11 and B2 to [or through, as I read it] B11. Diddy should be correct about the fact that zeros is in the predicate, except for the implication of how the originator might have been thinking [or should have].
However, when Diddy claims “since we all probably agree that B2:B11 is short for saying ‘the range B2:B11,’ I disgree. I read it as ‘(the cells from) B2 through B11.’
Therefore, I vote for “B2:B11 were all zeros,” but would agree that a better way might be “B2:B11 were all zero.” I would do it the first way myself. Maybe that’s why I say ‘handfuls’ rather than ‘handsful,’ which grates on my nerves.
Before I was a linguist I was a good proofreader. After 30 years as a field linguist, I’m no longer a good proofreader.
Mike R: I tried your examples in ’03 VBA & didn’t get the same results. Was Rob F correct about ’07 being different? Which version did you use, Mike? Or could you show a mre complete example?