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.
"Ashish Mathur" wrote:
Hi,
Question not clear. Please show the data layout and give before/after
examples
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Eve" wrote in message
...
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.
"Ashish Mathur" wrote:
Hi,
Try this
=SUMPRODUCT((MOD((COLUMN(G4:BCZ4)-COLUMN($F$4)),7)=1)*(G4:BCZ4))
Hope this helps
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Eve" wrote in message
...
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.
"Ashish Mathur" wrote:
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
"Eve" wrote in message
...
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
"Ashish Mathur" wrote:
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
"Eve" wrote in message
...
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.
"Ashish Mathur" wrote:
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
"Eve" wrote in message
...
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
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.