HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS
 

I HAVE TO ADD THE CELL EG(J7-K7+N7-O7+R7-S7+.....) LIKE THE SAME SERIES ANY
SHORTCUT TO ADD THIS UP TO 31 NOS

HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS
 

One way
Enter the pattern 1, -1, 0, 0 in any row (say Row 1) starting at Col J and
continue over the cells you want to add

then use this formula where you want the result

=SUMPRODUCT(J1:BR1,J7:BR7)

Verify that BR gives you 31 numbers to add (I am not counting 0's)

"japram" wrote:

I HAVE TO ADD THE CELL EG(J7-K7+N7-O7+R7-S7+.....) LIKE THE SAME SERIES ANY
SHORTCUT TO ADD THIS UP TO 31 NOS

HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS
 

Sheeloo, I tried with indirect, offsett etc. getting to arrays with no luck,
ur solution rules! Very simple, thx.

"Sheeloo" wrote:

One way
Enter the pattern 1, -1, 0, 0 in any row (say Row 1) starting at Col J and
continue over the cells you want to add

then use this formula where you want the result

=SUMPRODUCT(J1:BR1,J7:BR7)

Verify that BR gives you 31 numbers to add (I am not counting 0's)

"japram" wrote:

I HAVE TO ADD THE CELL EG(J7-K7+N7-O7+R7-S7+.....) LIKE THE SAME SERIES ANY
SHORTCUT TO ADD THIS UP TO 31 NOS

HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS
 

I am very happy that it worked for you...

I strongly believe in dividing the problem into smaller pieces, solve them
and then combine the results... It allows you to find creative solutions.

"Alojz" wrote:

Sheeloo, I tried with indirect, offsett etc. getting to arrays with no luck,
ur solution rules! Very simple, thx.

"Sheeloo" wrote:

One way
Enter the pattern 1, -1, 0, 0 in any row (say Row 1) starting at Col J and
continue over the cells you want to add

then use this formula where you want the result

=SUMPRODUCT(J1:BR1,J7:BR7)

Verify that BR gives you 31 numbers to add (I am not counting 0's)

"japram" wrote:

I HAVE TO ADD THE CELL EG(J7-K7+N7-O7+R7-S7+.....) LIKE THE SAME SERIES ANY
SHORTCUT TO ADD THIS UP TO 31 NOS

HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS
 

Hi,

And taking Sheeloo's idea, you don't need a dummy range just use the
following:

=SUMPRODUCT({1,-1,0,0,1,-1,0,0,1,-1},J7:BR7)

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

Cheers,
Shane Devenshire


"Alojz" wrote:

Sheeloo, I tried with indirect, offsett etc. getting to arrays with no luck,
ur solution rules! Very simple, thx.

"Sheeloo" wrote:

One way
Enter the pattern 1, -1, 0, 0 in any row (say Row 1) starting at Col J and
continue over the cells you want to add

then use this formula where you want the result

=SUMPRODUCT(J1:BR1,J7:BR7)

Verify that BR gives you 31 numbers to add (I am not counting 0's)

"japram" wrote:

I HAVE TO ADD THE CELL EG(J7-K7+N7-O7+R7-S7+.....) LIKE THE SAME SERIES ANY
SHORTCUT TO ADD THIS UP TO 31 NOS

HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS
 

definitely! U must make japram happy now!

regards,
Alojz

"Shane Devenshire" wrote:

Hi,

And taking Sheeloo's idea, you don't need a dummy range just use the
following:

=SUMPRODUCT({1,-1,0,0,1,-1,0,0,1,-1},J7:BR7)

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

Cheers,
Shane Devenshire


"Alojz" wrote:

Sheeloo, I tried with indirect, offsett etc. getting to arrays with no luck,
ur solution rules! Very simple, thx.

"Sheeloo" wrote:

One way
Enter the pattern 1, -1, 0, 0 in any row (say Row 1) starting at Col J and
continue over the cells you want to add

then use this formula where you want the result

=SUMPRODUCT(J1:BR1,J7:BR7)

Verify that BR gives you 31 numbers to add (I am not counting 0's)

"japram" wrote:

I HAVE TO ADD THE CELL EG(J7-K7+N7-O7+R7-S7+.....) LIKE THE SAME SERIES ANY
SHORTCUT TO ADD THIS UP TO 31 NOS

HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS
 

Actually, as I prefer arrays, would change it to:

=SUM({1,-1,0,0,1,-1,0,0,1,-1}*J7:s7)

hope japram is happy now! :-)

"Shane Devenshire" wrote:

Hi,

And taking Sheeloo's idea, you don't need a dummy range just use the
following:

=SUMPRODUCT({1,-1,0,0,1,-1,0,0,1,-1},J7:BR7)

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

Cheers,
Shane Devenshire


"Alojz" wrote:

Sheeloo, I tried with indirect, offsett etc. getting to arrays with no luck,
ur solution rules! Very simple, thx.

"Sheeloo" wrote:

One way
Enter the pattern 1, -1, 0, 0 in any row (say Row 1) starting at Col J and
continue over the cells you want to add

then use this formula where you want the result

=SUMPRODUCT(J1:BR1,J7:BR7)

Verify that BR gives you 31 numbers to add (I am not counting 0's)

"japram" wrote:

I HAVE TO ADD THE CELL EG(J7-K7+N7-O7+R7-S7+.....) LIKE THE SAME SERIES ANY
SHORTCUT TO ADD THIS UP TO 31 NOS