functions Sumproduct, Index, Match - What to understand

[John]

Hi Post,

Can some help me in understanding the above functions,
these all look very useful as I have seem many post in
relation to these solving tricky questions, however I
best learn via examples and then "playing" with the data,
do you know of any good tutorials on these/ downloads.

ie sumproduct("--"(

what's the -- for?, can see that in the excel help files?

Thanks

John

 

[RagDyer]

This is a pretty good explanation of SumProduct().

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi Post,

Can some help me in understanding the above functions,
these all look very useful as I have seem many post in
relation to these solving tricky questions, however I
best learn via examples and then "playing" with the data,
do you know of any good tutorials on these/ downloads.

ie sumproduct("--"(

what's the -- for?, can see that in the excel help files?

Thanks

John

 

[Ken]

Hello John,

About two weeks ago I was in the exact same position you
are, seeking to understand the devilish workings of these
functions, including the double unary (--). You won't find
it in help.

If you want to do some research, go to Google -> Groups ->
Advanced Groups Search, type in one of these functions in
the search box, and type in "*Excel*" in the Newsgroup
box. Look at some of those threads that come up.

I found one explanation of (--) and SUMPRODUCT at:
www.mcgimpsey.com/excel/formulae/doubleneg.html

My explanation of the (--) is that it it used to convert a
Boolean (Yes/No) answer to a number. Assume that cell A1
contains "John". If in B1 you enter =A1="John", the
answer returned is True. If you wanted to convert the True
to a number, you could multiply it by -1. Because a
Boolean True is represented by 1, you would get a -1 if
you entered =-(A1="John"). In these SUMPRODUCT-type
functions you want to get a positive number, so you
multiply the lookup by -1 twice (--). There are other ways
to skin this cat, but the threads kind of explain why the
double unary is the preferred method.

If you are like me, you will need to spend a few hours
reading through the threads and testing the functions
yourself before the lights start to come on.