Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
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
add comment

3 Answers 3

up vote 4 down vote accepted

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

share|improve this answer
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 Cook Nov 15 '12 at 21:03
    
shoot yeah, need $s - edited - thanks Daniel –  barry houdini Nov 15 '12 at 21:05
2  
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 Cook Nov 15 '12 at 21:07
1  
Ouch! Now I remember why I usually test these first....... –  barry houdini Nov 15 '12 at 21:11
add comment

This will work for each cell you indicated:

=0.5*Indirect("A" & Column())
share|improve this answer
    
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 Cook Nov 15 '12 at 20:54
add comment

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:

SU506269 example

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.