Adding up cell position not values in cell

rayelle

New Member
Joined
Mar 30, 2009
Messages
6
I have a spreadsheet with two columns and 39900 rows, from that data I want to create two new columns so cell C1 = A143 and D1 = B143, C2=A286, D1=B286, and so on. The row location increases by 143.
The simplest thing I tried to do was to say C1=A1+142, but (knowing it is wrong) I get a #value error.
How can I tell excel to grab these values located every 144 rows below and create two new columns?
Thanks for your help.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Thanks, Mike.
I tried using the INDIRECT function but I can't seem to get it to work for me, probably because I made a mistake in my explanation, I am actually trying to create two new columns D & E with cell values from colums A and C.
So D3=value in cell A365 (date) and E3= value in cell C365 (which contains a formula to calculate the daily average).
D1=6/16/08
E1=14.47
D2=6/17/08
E2=10.28
D3=A365=6/18/08
E3=C365=13.42

Hope that helps :)
 
Upvote 0
I have a spreadsheet with two columns and 39900 rows, from that data I want to create two new columns so cell C1 = A143 and D1 = B143, C2=A286, D1=B286, and so on. The row location increases by 143.
The simplest thing I tried to do was to say C1=A1+142, but (knowing it is wrong) I get a #value error.
How can I tell excel to grab these values located every 144 rows below and create two new columns?
Thanks for your help.


In Cell C1 enter the formula:
="A"&(Row()*143)
then fill this formula down as far as you require

In Cell D1 enter the formula:
="B"&(Row()*143)
then fill this formula down as far as you require

This should give you what you require

I'm unsure why you need the INDIRECT command as specified above in this scenario
 
Last edited:
Upvote 0
Hi Jonathan,

Have you actually tried your suggestion? The results I get are;

B143
B286
B429
B572
B715

When the Op requires the cell values to get the value of these cells you need tio wrap it in an Indirect.

=INDIRECT("B"&(ROW()*143)) To retrieve the value of cell B143.
 
Upvote 0
Mike,

I stand corrected - that's what you get for not trying it out in Excel first... doh!

Using the INDIRECT function works perfectly (don't think I've ever used that before, but I must have as I've done similar things a long while ago)
 
Upvote 0
Thanks for your input guys.

So, I figured D1 =INDIRECT("A"&(ROW()*77))
E1 =INDIRECT("C"&(ROW()*77))

D1: 6/16/08
E1: 14.34

It works beautifully, however, when I drag the cell down to fill the cells below, I get:

D2: 6/17/08, E2: (BLANK)
D3: 6/18/08, E3: (BLANK)
D4: 6/18/08, E4: (BLANK)
D5: 6/19/08, E5: (BLANK)
D6: 6/19/08, E6: (BLANK).
ETC.

Any ideas why this would be happening?

Thanks again for all your help.
 
Upvote 0
Hi Mike,

Im trying to reference cells: A221 & C221, A365 & C365, A509 & C509, that is every 144 rows below.

However, when I dragged cell D1 AND E1 down, the results I get are D2 =INDIRECT("A"&(ROW()*77))
E2 =INDIRECT("C"&(ROW()*77))

Does that mean I need to change manually the 77 to 221, etc.? Is there any way I can tell excel to calculate the values automatically, since they are every 144 rows below? I thought by dragging the cell down it will automatically fill in the values.

Thank you! :)
 
Last edited:
Upvote 0
So why did you give us this;

So, I figured D1 =INDIRECT("A"&(ROW()*77))
E1 =INDIRECT("C"&(ROW()*77))

Surely it should have been;

D1 =INDIRECT("A"&(ROW()*144))
E1 =INDIRECT("C"&(ROW()*144))

D1 would reference A144
D2 = A288
D3 = A432
etc.

ROW() return the row number, so in Row 3 the formula gives you 3*144

The formula should just copy and paste down, the row will increment on its own.
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,440
Members
448,573
Latest member
BEDE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top