Count every group of numbers whose sum is zero & put formula next toeach number
 

I have the following numbers in column A
1
2
3
-2
-4
5
5
-10
3
3
-6

I need a formula in column B next to each number telling me to which
group of sum zero it belows i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B
(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second
group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B
(third group of sun zero)

Pls help thxs

Count every group of numbers whose sum is zero & put formula next
 

Hi,

Here is a way to do it with 2 columns, it probably can be done with 1 but...

Assume your data start in A2, in B2 enter

=IF(SUM(A$2:A2)<0,1)

in C2 enter

=COUNTIF(B$2:B2,B1=TRUE)+B2

Copy down as far as necessary. Hide column B if desired.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"al" wrote:

I have the following numbers in column A
1
2
3
-2
-4
5
5
-10
3
3
-6

I need a formula in column B next to each number telling me to which
group of sum zero it belows i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B
(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second
group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B
(third group of sun zero)

Pls help thxs

Count every group of numbers whose sum is zero & put formula nextto each number
 

On Mar 17, 6:14 am, al wrote:
I have the following numbers in column A
1
2
3
-2
-4
5
5
-10
3
3
-6

I need a formula in column B next to each number telling me to which
group of sum zero it belows i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B
(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second
group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B
(third group of sun zero)

Pls help thxs

thxs shane !!! - exactly what i needed - do you know a bit about vba
macro -
can you give me a general macro which would achieve the same result if
i select the range of cells adjacent to the numbers sum of which need
to be zero - thxs

Count every group of numbers whose sum is zero & put formula next
 

On Mar 17, 7:51 am, Shane Devenshire
wrote:
Hi,

Here is a way to do it with 2 columns, it probably can be done with 1 but...

Assume your data start in A2, in B2 enter

=IF(SUM(A$2:A2)<0,1)

in C2 enter

=COUNTIF(B$2:B2,B1=TRUE)+B2

Copy down as far as necessary. Hide column B if desired.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"al" wrote:
I have the following numbers in column A
1
2
3
-2
-4
5
5
-10
3
3
-6

I need a formula in column B next to each number telling me to which
group of sum zero it belows i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B
(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second
group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B
(third group of sun zero)

Pls help thxs

shane tried to combine the above formula into one but in vain - can
you pls help to get the formula into one column only txs

Count every group of numbers whose sum is zero & put formula next to each number
 

Hi,

You can try this as well:

1. Assign a heading to the range of nos., say Numbers in cell C4;
2. Type Number in cell D4;
3. In cell F5, enter the following formula =ISNUMBER(C5);
4. In cell D5, type 1;
5. In cell D5, use the following formula =DSUM($C$4:C6,D$4,$F$4:$F$5) and
copy down;
6. In cell E5, use the following formula =COUNTIF($D$4:D4,"0")+1 and copy
down

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"al" wrote in message
...
I have the following numbers in column A
1
2
3
-2
-4
5
5
-10
3
3
-6

I need a formula in column B next to each number telling me to which
group of sum zero it belows i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B
(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second
group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B
(third group of sun zero)

Pls help thxs

Count every group of numbers whose sum is zero & put formula nextto each number
 

On Mar 17, 10:12 am, "Ashish Mathur" wrote:
Hi,

You can try this as well:

1. Assign a heading to the range of nos., say Numbers in cell C4;
2. Type Number in cell D4;
3. In cell F5, enter the following formula =ISNUMBER(C5);
4. In cell D5, type 1;
5. In cell D5, use the following formula =DSUM($C$4:C6,D$4,$F$4:$F$5) and
copy down;
6. In cell E5, use the following formula =COUNTIF($D$4:D4,"0")+1 and copy
down

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"al" wrote in message

...

I have the following numbers in column A
1
2
3
-2
-4
5
5
-10
3
3
-6

I need a formula in column B next to each number telling me to which
group of sum zero it belows i.e

1,2,3,-2,-4 would each have number 1 next to each of them in column B
(first group of sum zero)

5,5,-10 would each have number 2 next each of them in column B (second
group of sum zero)

3,3,-6 would each have number 3 next to each of them in column B
(third group of sun zero)

Pls help thxs

ashish i'm looking for something using formulas in 1 column only - can
you help pls

Count every group of numbers whose sum is zero & put formula next
 

Hello,

If A1 and B1 are empty and your data starts at A2, enter into B2:
=IF(ABS(SUM(A$1:A1))<0.0000000001,B1+1,B1)
and copy down.

Regards,
Bernd

Count every group of numbers whose sum is zero & put formula next
 

On Mar 17, 4:51 pm, Bernd P wrote:
Hello,

If A1 and B1 are empty and your data starts at A2, enter into B2:
=IF(ABS(SUM(A$1:A1))<0.0000000001,B1+1,B1)
and copy down.

Regards,
Bernd

great thxs a lot for that improved formula