Bernard,
Thank you for the links. I am still struggling with SUMPRODUCT(), two steps
forward and one step backward.
I enjoyed Ken's illustration (same thread) and I also read this
http://www.officearticles.com/excel/...rosoft_excel.h
tm
Just when I think I am seeing light at the end of the tunnel, I read the
second link
http://mcgimpsey.com/excel/formulae/doubleneg.html
Then I get so confused with all the "coercing," "double negating" etc. from
the article.
Please refer to the above link. I have problem understanding this formula
=SUMPRODUCT(--(A1:A510),B1:B5))
This is the first time I see two minus signs side by side and a comma ","
instead of a "*" between the arguments/arrays.
The following formula will give me the exact same result and I would like to
embrace it as I understand it.
=SUMPRODUCT((A1:A510)*(B1:B5))
I haven't studied the first link in detail (I just skimmed through it) as I
don't want to overwhelm myself and get more confused. When I am more
experienced, I am sure I can appreciate it as an excellent resource.
Feedback welcome on coercing, double negating etc.
Epinn
"Bernard Liengme" wrote in message
...
Read these two
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
and
http://mcgimpsey.com/excel/formulae/doubleneg.html
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email
"Epinn" wrote in message
...
Please refer to Ntobias' thread on Aug 24, 2006 and Sandy's comment
before
you read on.
Sandy,
Thank you for your comment and a different perspective. Before JE's
post,
I
already had a hard time understanding SUMPRODUCT. When I read JE's post
I
did more research. Excel help was not a big help and I got lost in
cyber
space encountering things like matrix Algebra. To make a long story
short -
I am getting more confused with SUMPRODUCT and not sure if I understand
it.
Can someone point me to some good and straightforward tutorial please?
I found the following examples from the net but unfortunately there was
no
step by step explanation. I thought it was a good idea to use Excel's
ToolsFormula AuditingEvaluate Formula to analyze the formulas so that
I
could have a clue of what's going on. Feel free to laugh.
1 =SUMPRODUCT(1,2,3) = 6
2 =SUMPRODUCT({1,2},{2,3}) = 8
3 =SUMPRODUCT({1,2,3,4}) = 10
4 =SUMPRODUCT({1,2,3,4},{4,3,2,1}) = 20
5 =SUMPRODUCT({3,4;8,6;1,9},{2,7;6,7;5,3}) = 156
6 =SUMPRODUCT(MMULT({1,2;3,4},{1,2;3,4})) = 54
7 =SUMPRODUCT({3,4;8,6;1,9},{2,7;6,7}) = #VALUE!
8 =SUMPRODUCT((A1:A20=5)*(A1:A20<=10)*A1:A20)
I truly need some **simple and straightforward** guidance. Please bear
in
mind that I am a very new user. By the way, I am also trying to learn
by
looking at the big picture. For example, when I look at a SUMPRODUCT
formula, I try to think of the **equivalent** and more flexible SUM
(array)
formula. You guess it, I get more confused.
Help!! Appreciate feedback.
Epinn