# EXCEL Use function as a row number

Say I have some values in rows like follows:

A1 - 5
A2 - 8
A3 - 3
A4 - 4

In row A5 I have a formula: "0,5 * A1". I want to autofill columns right so that they have formulas like so:

``````  A5   │   B5   │   C5   │   D5
│        │        │
0.5*A1 │ 0.5*A2 | 0.5*A3 | 0.5*A4
``````

So I want to increment rows as the columns increment.

• Switch to R1C1 reference mode. You'll find it in Excel settings. After that you can use numbers for columns, so you can probably do what you want. – Peter Jenkins Nov 15 '12 at 20:49

You can use INDEX which has some advantages over the INDIRECT method. 1) it's not volatile 2.) you don't have to "hardcode" the column, so if you add a column at A the formula will still work.

For that use this fomula in A5 copied across [edited as per Daniel Cook's comment]

`=INDEX(\$A:\$A,COLUMNS(\$A5:A5))/2`

• You're right. Index is better. Indirect was the first thing that came to mind. :) Though I think you mean `=0.5*Index(\$A:\$A,Columns(\$A5:A5))` if you want to copy the formula across. – Daniel Nov 15 '12 at 21:03
• shoot yeah, need \$s - edited - thanks Daniel – barry houdini Nov 15 '12 at 21:05
• Right, but you still need to use `\$A:\$A` instead of `A:A` or when you "copy across" the first column passed to the index column will keep shifting as well. – Daniel Nov 15 '12 at 21:07
• Ouch! Now I remember why I usually test these first....... – barry houdini Nov 15 '12 at 21:11

This will work for each cell you indicated:

``````=0.5*Indirect("A" & Column())
``````
• If this works, it's great, although pretty obscure. – Peter Jenkins Nov 15 '12 at 20:51
• @PeterJenkins Indirect lets you provide the reference as a string. So Indirect("A" & Column()) returns the value of A1 within the first column, A2 within the 2nd column etc... – Daniel Nov 15 '12 at 20:54

A different approach, for the sake of that and admittedly less versatile than the other answers, would be to transpose and then either apply a formula or, as shown below, use a Paste Special Operation, such as Multiply (by 0,5). It can sometimes involve fewer key strokes: Note (i) the results (highlighted) are shown is Row8 in the image but would appear in `Row5` and (ii) the results this way are values, not formulae.