How do I add consecutive numbers but start over at break in the.

[Guest]

Recently, the following question was posed by another party:

"How do I add consecutive numbers but start over at a break in them. I'm
trying to calculate consecutive days of attendance. One might have had 20
days in a row missed one and then I need to start over."

The rsvp was:

You received an answer...

In B1 enter:
=--(A1>0)

In B2 enter:
=(A2>0)*(1+B1)

and drag down to copy as needed

My question is:

What does =--(A1>0) mean?

i.e. what is the meaning of...=-- ??

Regards,

FL KULCHAR

 

[JE McGimpsey]

Take a look here:

http://www.mcgimpsey.com/excel/doubleneg.html

 

[Earl Kiosterud]

FL,

What does =--(A1>0) mean?

First of all, the expression A1>0 is a boolean expression. A1 is either
greater than 0, or it isn't. So the expression resolves to TRUE or FALSE.
If you select this part of the formula and press F9, it will evaluate it for
you, putting in TRUE or FALSE. Press Esc, not Enter, to keep the formula
from being permanently changed.

If we want 0 instead of FALSE, or 1 instead of TRUE, we use --, double
negation, which forces Excel to treat the (A1>0) expression as a number. We
say it coerces it from it's current data type (boolean) to a number. The
second - did that (the one directly in front of the (A1>0) expression), and
the first brought it back to the original sign, + or -. You can get the
same result with =(A1>0)+0. It has to coerce the (A1>0) to numeric before
it can add the zero. Or =(A1>0)*1. The double negation is probably the
most universally used and recognized.

The parentheses are necessary because the rules of precedence would
otherwise cause the -- (actually only the second one) to apply only to the
A1 term, instead of the expression A1>0.

 

[Guest]

To Earl:

Thank you for your RSVP: your eloquence and your illustrious knowledge
certainly answered my question...THANK YOU VERY MUCH.

--FL Kulchar*1