HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS

J

japram

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
 
S

Sheeloo

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)
 
A

Alojz

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

Sheeloo

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.
 
S

Shane Devenshire

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)
 
A

Alojz

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! :)
 
Top