Add cells on two adjacent rows but non-adjacent columns

E

Eve

Hello,

Thank you for helping, as I am not too familiar with the advanced funtions
of Excel.

I am working with a very large worksheet and would like to add cells
together. I would like to add two cells (on two adjacent rows but same
column) every 7 columns apart.


eg.
A B C D E
F G
1 Days Date Value XAO Return XAO
Return AR
2 -2 02/12/2008 0.042 3437 0.1333 -0.023
-0.454
3 -1 03/12/2008 0.043 3427 0.083 -0.987
-0.587
4 0 04/12/2008 0.477 3553 -0.546 0.456
0.623
5 +1 05/12/2008 0.567 3438 0.9865 0.387
0.218

I would like to add cells G3:G4 for every one of my stocks as I want to
calculate the AR for each. Each of the stocks has the same format, and
therefore, in-between the AR column for each of the stocks is exactly 7
columns.

Hopefully, I would like to put the new values into a new column, so I can
just auto-fill down.

Thank you again. This is my first time posting here, I hope I have made my
question clear enough.
 
S

Simon Lloyd

I'm not sure i follow? if all you want to do is SUM G3:G4 then thats i
=SUM(G3:G4) can you explain further

Eve;528943 said:
Hello

Thank you for helping, as I am not too familiar with the advance
funtion
of Excel

I am working with a very large worksheet and would like to add cell
together. I would like to add two cells (on two adjacent rows but sam
column) every 7 columns apart


eg
A B C D
F
1 Days Date Value XAO Return XA
Return A
2 -2 02/12/2008 0.042 3437 0.1333 -0.02
-0.45
3 -1 03/12/2008 0.043 3427 0.083 -0.98
-0.58
4 0 04/12/2008 0.477 3553 -0.546 0.45
0.62
5 +1 05/12/2008 0.567 3438 0.9865 0.38
0.21

I would like to add cells G3:G4 for every one of my stocks as I want t
calculate the AR for each. Each of the stocks has the same format, an
therefore, in-between the AR column for each of the stocks is exactly
columns

Hopefully, I would like to put the new values into a new column, so
ca
just auto-fill down

Thank you again. This is my first time posting here, I hope I have mad
m
question clear enough
Click to expand...

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com
 
A

Ashish Mathur

Hi,

This formula will sum up the two adjacent cells every 7 columns apart

=SUMPRODUCT((MOD((COLUMN(B3:U3)-COLUMN($A$3)),9)={1;2})*(B3:U3))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
E

Eve

Hi,

I think the alignment of the example made it quite confusing. I need to add
up cells in the same row which are spaced 7 cells apart exactly.

But thank you for your help. If you could state a general formula, then I
can try to fit in my own data.

Thank you.
 
A

Ashish Mathur

Hi,

OK, help me understand this better. If your data is in B3:U3, then what do
you want to do:

1. Add B3, J3, R3 etc ;or
2. B3,C3,J3,K3,R3,S3

Please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
E

Eve

Hi,

The cells I want to add up start from G4, then N4, U4... etc and the last
cell is BCZ4.

Actually, there is only 6 cells in between each cell I want to add up.

Thanks
 
A

Ashish Mathur

Hi,

So you want to add up chunks of 6 cells in the same row staring from G4 and
all the way upto BCZ4. Therefore, you want to sum up G4:N4 + U4:AB4. If
this is indeed the case, you may use the following:

=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),14)={1;2;3;4;5;6;7;8})*(G4:BCZ4))

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
E

Eve

Hi,

Sorry. I haven't made myself that clear again. It is just single cells that
I want to add up, the first is at G4, the next is N4, then U4 etc etc...
until the last cell is BCZ4. So, every interval space of 6 cells. The values
are all in the same row.

Your other comments have been helpful to me to learn more about Excel. I
didn't know Excel was this powerful =P until I started to use it properly. I
always thought it could only sum or subtract numbers together.
 
E

Eve

Thank you so so much. Works wonderfully. It is exactly the same as the values
I got by manually selecting 200+ cells using the SUM function.
 
E

Eve

I have one more question. I need to stack data that is currently in different
columns. These sets of data are exactly 91 rows X 6 columns across, I would
like to stack it into 6 columns and as many rows as necessary but do not want
to manually cut and paste.

Do you have a good formula to do this as well?

Thank you again.
 
E

Eve

Hi,

The first "set"/box of data starts in cell B1 and goes till G93 (93rows
*6columns), the next set starts in I1 till N93 (93rows*6columns) and then P1
till U93 etc etc.... the last "set"/box of data is in BBZ1 till BCE93.

I want to stack all of these boxes into a very long data set with 6 columns
and as many rows as necessary.
Therefore, under box B1 to G93, I would like to have data from my second
"box"/set" in cells B94 to G186 and then the next box in cells B187 to
G279... etc until the last of all my boxes.

Sorry, about the confusion. You have been a great help already! If you do
not mind, I can send you the whole worksheet.

Thank you.
 
A

Ashish Mathur

Hi,

You may mail the file to me at ask(at)ashishmathur(dot)com. Do not send a
big file - send only the data for the question. Also, give before/after
examples - explain the problem clearly

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
E

Eve

Done.

Thank you for being so helpful.


Ashish Mathur said:
Hi,

You may mail the file to me at ask(at)ashishmathur(dot)com. Do not send a
big file - send only the data for the question. Also, give before/after
examples - explain the problem clearly

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
Click to expand...
 
Top