At 16:19 08/09/2013 -0400, Fred James wrote:
Brian Barker wrote:
Er, there is no "typo", just some confusion on your part. The
original formula works. You have changed the test from X = Y to Y
= X and the product X x Y into Y x X. As any elementary
mathematician knows, equality and multiplication are commutative,
so these are the same thing: x = y and y = x are the same test; and
2 x 3 and 3 x 2 are the same value!
Sorry ... yes: x=y is the same as y=x, but when testing the original
function the results were incorrect.
We cannot know how, of course - but you must have "tested" it
incorrectly! (I tested it, too.)
Switching the b's and c's made the function return the correct results.
But you haven't just done that: the comparison is still between the
Bs and the E (though reversed) and the product is still between the
B/E switch and the Cs (though also reversed).
Assumption (on my part):
the first part (C$2:C$5) is the column of values to be summed
the second part (E1=) is the value we want to match in the third part
the third part (B$2:B$5) is the column/range of values to be
matched by the second part
Yes: this also works - but there is no need to modify the original
formula. Your chosen order of parts is not necessary for the formula
to work. I'm not finding fault with your formula, but you are -
incorrectly - with mine!
So if we used column B for the first part we would be trying to sum
labels/text, and if we used C in the third part, we would be trying
to match labels/text to numbers?
No! In my original formula, the E value was still compared with the
Bs and the product made between the resulting switch and the Cs.
Or did I miss something?
Obviously yes: please go back and read my version again!
In any case, I must thank you for pointing to this solution ... it
is great ... I really enjoyed this.
Good-oh! I have to say that SUMPRODUCT() had not been the first
thing I'd think of in situations such as this - but I picked up the
powerful technique from experts on this and similar lists.
Brian Barker
--
To unsubscribe e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
