Add certain rows....

[Zadig Galbaras]

Hi.

My spreadsheet has a repetitive calculation for each 25.th row.
In another place I want to list up each of these values, but then in each
and every row downwards.

That is;
A1 = 25
A26 = 26
A51 = 23

Now I want to copy these values to lets say to D1 like this
D1 = 25 - Linked to A1
D2 = 26 - Linked to A26
D3 = 23 - Linked to A51

Is there a trick in the formula in D2 to automatically jump 25 rows from the
row mentioned in D1, so it shows the value in A26, without me having to sit
here day and night in a week to do the tedious copy and link jockey routine?


--

regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-

 

[Biff]

Hi!

In D1 enter this formula and copy down as needed:

=OFFSET(A$1,(ROW()-1)*25,,)

Biff

 

[Arvi Laanemets]

Hi


D1=OFFSET($A$1,(ROW()-1)*25)


Arvi Laanemets

 

[Zadig Galbaras]

Thank you guys!

Now for a transaltion of function names from english to norwegian.....
There was a recommended spreadsheet I've got a year ago, but I seem to have
lost it.

Is there anyone who knows of such a help?
To translate english function names into norwegian?

--

regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-

 

[Zadig Galbaras]

Hi..

I have translated it into Norwegian, and typed your suggestions, but....

First of all I've got an error so I changed the formula to this:

=OFFSET(A$1;2;;

According to Excel Help the first parameter is the cell of origin. The
second is the offset size. The third, fourth and fifth I can forget this
time.

Anyhow. After typing the formula and got the right value shown in the first
cell, I copied downwards and the values came out wrong.
The A$1 changed to A$2, and the value shown was the value of the cell
situated two cells below A2. That was not what I wanted. This led to a list
where every number in the column was shown, not every second.
If I locked the entire first parameter with the cell of origin, I got the
same value as in the in the first in all the cells below the first.
I wanted to be able to pick out every second or 25.th, for that matter,
value in a column of values, and show them in a column of adjacent cells.




--

regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-

 

[Biff]

Hi!

Which formula did you try?

The formula offered in the other reply would have returned
an error because a required argument was left out. If you
used my example:

=OFFSET(A$1,(ROW()-1)*25,,)

it should have worked if you did indeed get it translated
correctly and it was placed in cell D1, where you said you
wanted it.

Here are the arguments to OFFSET

Reference_Cell,Rows,Columns,Height,Width

In your case the Reference cell is A1. When entered like
this: A$1 and copied down A$1 should not change to A$2 or
A$3 etc.. The row number should remain ABSOLUTE.

The Rows argument is how many rows offset from cell A$1.
In your case the Rows argument is: (ROW()-1)*25.

Here's what that means:

The ROW number that the formula is in minus 1 then
multiplied by 25. So, if you enter that formula in D1:

Row(1)-1 = 0 * 25 = 0

Thus:

=OFFSET(A$1,0

Now, since all the data you want is in the same column as
the reference cell you do not want to offset A$1 by any
columns but the column argument is required so you just
leave that blank or enter zero:

=OFFSET(A$1,(ROW()-1)*25,,)

Which is the same as:

=OFFSET(A$1,(ROW()-1)*25,0,)

Since you're only returning a single value the Height and
Width arguments are not needed and they are also OPTIONAL
arguments.

So, as you copy the formula down a column starting in cell
D1:

D1 OFFSET(A$1,0,0) Returns the value in cell A1
D2 OFFSET(A$1,25,0) Returns the value in cell A26
D6 OFFSET(A$1,50,0) Returns the value in cell A51
etc..
etc..

Biff

 

[Biff]

Typo correction:

D6 OFFSET(A$1,50,0) Returns the value in cell A51

Should read:

D3 OFFSET(A$1,50,0) Returns the value in cell A51

Biff