Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMPRODUCT question
Where does one find out in detail about this and other Excel functions
outside Excels own help for example, I often see formulas such as =SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10)) quoted, and one guy helped me out with this on a sheet of mine, but I have no idea where the two "-" signs came from, how they work or how you find out about these. Is this array arithmetic? If so, where is that detailed in the help, or on the web? thanks in advance LH |
#2
|
|||
|
|||
SUMPRODUCT question
"Lee Harris" wrote in message
... Where does one find out in detail about this and other Excel functions outside Excels own help for example, I often see formulas such as =SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10)) quoted, and one guy helped me out with this on a sheet of mine, but I have no idea where the two "-" signs came from, how they work or how you find out about these. Is this array arithmetic? If so, where is that detailed in the help, or on the web? thanks in advance LH See this site: www.xldynamic.com/source/xld.SUMPRODUCT.html Bruno |
#3
|
|||
|
|||
SUMPRODUCT question
Lee Harris wrote:
Where does one find out in detail about this and other Excel functions outside Excels own help for example, I often see formulas such as =SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10)) [....] where is that detailed in the help, or on the web? I am so-o glad you asked this question. I have been meaning to post a similar question for a long time, but I keep forgetting. Someone posted an answer for SUMPRODUCT per se. That is great. Thanks. But I would like to know the answer to the more general question: where to find all such tricks, which seem to be undocumented? At least, I do not see them on the standard Help page. For example, I undertstand the tricks used in the SUMPRODUCT example above. But I cannot apply the same trick (an operation on an entire range) in some other functions, although I believe I have seen the tricks used by others in some other functions. Bottom line: how do we know when such tricks will and will not work? Are there syntactic rules that govern this? Or are they discovered by trial and error? For instance (strawman): Do such tricks work only with functions that are designed normally to do element-by-element operations on ranges, for example as SUMPRODUCT is, but SUM is not? |
#4
|
|||
|
|||
SUMPRODUCT question
mvps.org has a number of links to some good websites - some have discussion
on Sumproduct function and array formulas. Sum can also be used in an array formula (as can many other functions). See this links for more info. http://xldynamic.com/source/xld.SUMPRODUCT.html http://www.cpearson.com/excel/array.htm " wrote: Lee Harris wrote: Where does one find out in detail about this and other Excel functions outside Excels own help for example, I often see formulas such as =SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10)) [....] where is that detailed in the help, or on the web? I am so-o glad you asked this question. I have been meaning to post a similar question for a long time, but I keep forgetting. Someone posted an answer for SUMPRODUCT per se. That is great. Thanks. But I would like to know the answer to the more general question: where to find all such tricks, which seem to be undocumented? At least, I do not see them on the standard Help page. For example, I undertstand the tricks used in the SUMPRODUCT example above. But I cannot apply the same trick (an operation on an entire range) in some other functions, although I believe I have seen the tricks used by others in some other functions. Bottom line: how do we know when such tricks will and will not work? Are there syntactic rules that govern this? Or are they discovered by trial and error? For instance (strawman): Do such tricks work only with functions that are designed normally to do element-by-element operations on ranges, for example as SUMPRODUCT is, but SUM is not? |
#5
|
|||
|
|||
SUMPRODUCT question
Also, John Walkenbach has a book (Excel 2003 formulas -a lot of the info is
still applicable to previous versions of Excel) w/chapters devoted to array formulas. " wrote: Lee Harris wrote: Where does one find out in detail about this and other Excel functions outside Excels own help for example, I often see formulas such as =SUMPRODUCT(--(A1:A10=B1:B10),(C1:C10)) [....] where is that detailed in the help, or on the web? I am so-o glad you asked this question. I have been meaning to post a similar question for a long time, but I keep forgetting. Someone posted an answer for SUMPRODUCT per se. That is great. Thanks. But I would like to know the answer to the more general question: where to find all such tricks, which seem to be undocumented? At least, I do not see them on the standard Help page. For example, I undertstand the tricks used in the SUMPRODUCT example above. But I cannot apply the same trick (an operation on an entire range) in some other functions, although I believe I have seen the tricks used by others in some other functions. Bottom line: how do we know when such tricks will and will not work? Are there syntactic rules that govern this? Or are they discovered by trial and error? For instance (strawman): Do such tricks work only with functions that are designed normally to do element-by-element operations on ranges, for example as SUMPRODUCT is, but SUM is not? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
another sumproduct question | Excel Worksheet Functions | |||
SUMPRODUCT Question... | Excel Discussion (Misc queries) | |||
sumproduct question | Excel Worksheet Functions | |||
Question about sumproduct | Excel Discussion (Misc queries) | |||
Question about sumproduct | Excel Discussion (Misc queries) |