Formula Syntax?

[QTE]

Hi Excel Forum,

What do the two dashes (--) mean and do in the following formulas:


=SUMPRODUCT(--A1:G1)

=SUM(IF(ISNUMBER(--A1:G1),--A1:G1))
entered with ctrl + shift & enter

=SUMPRODUCT(--(ISNUMBER(A1:G1)))

Please explain.

Kind regards,
QT

 

[Norman Harker]

Hi QTE!

No need to post to more than one group and it can cause wasted effort
of those who you want to help you.

Here's the reply I gave in New Users group:

The -- coerces Boolean returns of TRUE and FALSE to 1 and 0

Do a Google Search on "unary minus" and you'll find plenty of
discussions on the topic.

 

[QTE]

Hi Norman,

Great, thanks for explaining and referral. It did baffle me but no
anymore - thanks to you!

Kind regards,
QT

 

[Norman Harker]

Hi QTE!

Never just accept solutions offered is a very good tip. It's the fish
v. learning to fish thing.

 

[QTE]

Hi Norman,

I think you're missing the point and key aspect of the Forum: people
sharing their knowledge. If I cannot find a solution myself or only
have half the picture, I think the most sensible thing to do is ask?

*Hi QTE!
Never just accept solutions offered is a very good tip. It's the
fish
v. learning to fish thing. *

[/QUOTE]

You accept the solution and try it out (it may not work or may need
further investigation). It's also a part of learning: you then build
on that. It's difficult to go forward if you hit a brick wall: I
couldn't fathom the syntax, and so I posed the question to the Forum.
"You cannot know what there is to know without asking?"

Kind regards,
QTE

 

[Norman Harker]

Hi QTE!

The emphasis should have been on *accept*. Solutions are great but
understanding them is much better as that logic can be applied to many
other problems later.

 

[QTE]

Hi Norman,

I think we are going round in circles. If you refer to my first threa
of this post it was all about not understanding the logic of th
formula, that's why I posed the question about the two dashes withi
the formula.

*Hi QTE!
The emphasis should have been on *accept*. Solutions are great bu
understanding them is much better as that logic can be applied t
many other problems later.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed) *

I wholeheartedly agree with your above sentiment. However, emphasis o
solution acceptance is misplaced in this instance. I do however
accept your explanation of the two dashes (--) that has enabled me t
understand the LOGIC of the original said mentioned SUMPRODUCT formul
syntax.

I recognised the SUMPRODUCT function but did not understand the usag
of the two dashes? And, now with your explanation I do understand th
LOGIC! behind it (even checked out unary minus - thank you!) . M
question was never about a solution but the logic behind the formula;
didn't understand it.

Believe it or not: I see the point you're making about understandin
the LOGIC so it can be applied to many solutions. The forum is one o
the resources I use to assist my lack of LOGIC!

Not to say that I do not request solutions but on this occasion it wa
a straightforward request for an explanation of the usage of specifi
characters within the syntax to give me an understanding of what th
formulas were doing - explaining the LOGIC.

When I do request solutions because I just have not got a clue or hav
a partial solution, I find it much better to look at something tha
firstly, is in a proper working state and for the most part is doin
what it should. I can then try and decipher what is going on, and ma
even go back to the author and ask for further explanation. I can se
nothing wrong with that approach in my book. Whether it be Logic tha
is lacking or the solution itself, I think its rare to get eithe
without asking questions?

My aim is to have a much better understanding of Excel as a whol
(logic and all!) but I've got a long, long, way to go! Gosh!... I'
exhausted already.

Hope you'll assist in the future - it's pretty obvious that I'll b
back.

Kind regards
QT

 

[Norman Harker]

Round in circles but in agreement.

 

[QTE]

Hi Norman,

*Round in circles but in agreement.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed) *

Most definitely, in agreement.

Cheers
Kind regards,
QT

 

[Kalabalana]

Is this what mature flaming sounds like