0

In Excel, columnA I have values which need to be tripled in columnB

So I currently have

     A
1   val1
2   val2
3   val3

I'd like each value to be tripled in column B, such as

     A          B
1   val1       val1
2   val2       val1
3   val3       val1
4              val2
5              val2
6              val2
7              val3
8              val3
9              val3

How can I do this?

Improve this question
2
  • I have made a MASSIVE change to your question as it was very unclear, I hope I've got it right for you. If you don't like it, please click on this link and roll it back – Dave Jun 1 '15 at 12:18
  • Welcome to Super User. Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed. – DavidPostill♦ Jun 1 '15 at 12:22
2

Based on Dave's change, formula from B2 onwards should read:

=INDEX($A$2:$A$9,CEILING(ROWS($B$2:$B2)/3,1),1)

How it works

ROWS($B$2:$B2)/3

The ROWS function divided by 3 says that for every 3 rows you fill down the formula, it will look one cell further down for A's value. The first $B$2 uses absolute referencing because you always want the formula to remember where the first cell in B is. The second $B2 will fill down and tell it how far to count to.

CEILING(#,1)

The CEILING function with a precision of 1 will ensure your ROWS result (#) is rounded up to the nearest whole number, and so tells us how far down A to go.

INDEX($A$2:$A$9, #)

The INDEX function uses your result to return the #'th item from your table in column A.

Improve this answer
1
  • I would have said this had to be done with VBa, what a good answer. +1 (simply because I can't give it the +2 it deserves for the explanation you provided) – Dave Jun 1 '15 at 13:06

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