Bob,
I think one of my problems is I learn how to run before I learn how to walk. For example, I studied SUMPRODUCT before I read about SUM.
Sometimes I don't feel that I am the "majority" meaning that I may have a unique way of interpreting things. When it comes to learning, I make a point not to compartmentalize. All this plus being detailed or a _ _ _ contribute to numerous questions. Thank you all for putting up with me.
Yes, I am interested in counting and summing as well and I try to pick out these threads to learn. Whenever there is a SUMPRODUCT thread, I probably read it. Bob, thank you for posting that link on ROUNDING. I am going to study it too.
Always appreciate everyone's help.
Epinn
No problem Epinn. Actually I have found these exchanges very useful as I
give teaching sessions on complex counting and summing, and in my ignorance
I never saw things in the same way that you did. I was making some big
assumptions. Hopefully I can incorporate these lessons back in.
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
Bob and Roger,
Thanks for straightening me out. Guess I have g _ _ _ _ _ up big time.
Glad I found out at this early stage of learning. It is helpful for me to
see the similar formulae grouped together and explained. I appreciate that.
Yes, it has nothing to do with SUMPRODUCT. When I first learned about
double negating, coercing etc., SUMPRODUCT was the example. So, I thought
it only happened to SUMPRODUCT. Subsequently, when I found out I could use
double negating in VLOOKUP when the data type of the lookup value and the
array didn't match, I was "surprised" and realized that I had the wrong
impression.
So, when we include plus (+) for "or" in a formula, an addition actually
takes place. I think I am okay with *, -- and +. I won't worry about ^ and
N( ).
Thanks again for a valuable lesson and I know this kind of foundation is
important for me down the road.
Cheers,
Epinn
Hi
It has nothing to do with Sumproduct per se.
It has everything to do with Logic and Boolean operators.
Consider cells A1, B1 and C1.
We could write
=IF(AND(A1="A",B1="A",C1="A"),TRUE,FALSE)
Equally we could write that more simply as
=AND(A1="A",B1="A",C1="A")
which would return a value of TRUE if ALL 3 conditions were met or FALSE
if ANY was not met.
This could be written as
=((A1="A")*(B1="A")*(C1="A")>0)
So we would have (TRUE or FALSE)*(TRUE or FALSE)*(TRUE or FALSE)
which becomes (0 or 1)*(0 or 1)*(0 or 1)
where ANY of those conditions returning 0, will result in the whole
formula returning 0 (FALSE) because they are Multiplied together which
is the same as AND'ing them together. It would require 3 * 1's (TRUES )
to give a result of 1 (TRUE).
In this scenario, the final value to the left of the ">" operator can
only be 0 or 1.
Alternatively
=IF(OR(A1="A",B1="A",C1="A"),TRUE,FALSE)
which expressed more simply is
=OR(A1="A",B1="A",C1="A")
which would return a value of TRUE if ANY condition was met or FALSE if
ALL were not met.
Again, this could be written as
=((A1="A")+(B1="A")+(C1="A"))>0
which resolves to (0 or 1)+(0 or 1)+(0 or 1)
where ANY of those conditions returning 1, will result in the whole
formula returning at least 1 (TRUE) because they are ADDED together
which is the same as OR'ing
In this scenario, the final value to the left of the "> operator can be
0,1,2,or 3 where 0 is FALSE and 1, 2 or 3 is TRUE
--
Regards
Roger Govier
In order *not* to confuse SUMPRODUCT, we use semicolon as argument
separator when comma is used as digit grouping symbol.
e.g.
=SUMPRODUCT(--(A1:A10>=123,45);--(B1:B10="abc");C1:C10)
However, we use the plus sign (+) to indicate both OR and ADDITION.
e.g.
+ used for "addition"
=SUMPRODUCT(--(C2:C31="TLG"),--(H2:H31="TLG"),D2
31+I2:I31)
+ used for "or"
=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))>0))
I guess SUMPRODUCT knows that the "+" means "or" because of the equal
sign (i.e. logical test)? Why not use double plus (++) for "or" test?
Just a thought. Don't mean to confuse anyone.
I find all this interesting.
Epinn
