HOW CAN ADD AND SUBTRACT WITH THE SEQUENCE OF CELLS

  • Thread starter Thread starter japram
  • Start date Start date
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
 
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)
 
Sheeloo, I tried with indirect, offsett etc. getting to arrays with no luck,
ur solution rules! Very simple, thx.
 
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.
 
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)
 
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! :-)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I display 0 if it is the end result for a formula 4
Long formula 17
IF Statement 2
Stumped with count if's 2
Joining Two formulas with the Help of AND / OR / IF Functions to getthe desired result 1
Multiply Corresponding Values in Range 3
Referring the cell instead of typing the sheet name 3
Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA) 7

Back
Top