# Repeating a number in a given number of columns

#### daleward

##### New Member
I need to repeat a cell a certain number of times in the same row. The number represents an annual amount; the number of repetitions will be variable.

The annual amount appears in the year column that it first applies to. The amount needs to be repeated for a certain number of times, depending on whether the annual amount needs to be payable each year for, say, ten or eleven years. There is a cell which shows the required duration.

Any ideas?

Dale

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### AlohaJulio

##### Board Regular
If the columns with the years are in a number format, you could write and IF statement. You'd want the statement to be IF(End Year IS Less Than Current Year,Value,0). You'll have to modify this to fit your data, but without seeing the layout it is the best I can do.

#### nbrcrunch

##### Well-known Member
Another possibility:

Create a INSTANCE row with the numbers 0 to (10, 11, or whatever).

Then, referencing your duration cell, just below the above numbers:

=if(Instance<=Duration,StartYear+Instance,"")

Following the logic, for the first column (zero) the result will be the current year. For the next column (1) the result will be the following year. As soon as Instance is greater than Duration, the remaining cells will be blank (no year).

To make it "pretty" you could always hide the row with the Instance numbers.

#### DaVinci

##### Board Regular
As an example:

Column A is the Duration in years
Leave Column B empty (you can hide it if you want, we just want it to be "0")
Year numbers are across Row 1 beginning in Column C

Type this into cell C2 and then copy it over the range:

=IF(AND(B2>0,COUNTIF(B2:\$B2,">0")<\$A2),B2,"")

You will have a bunch of blank cells, until you put the amount of the monthly payment into the cell at the first year it is to occur. Then, the rest of the cells (payments) will be filled in for the row.

Replies
7
Views
630
Replies
2
Views
691
Replies
21
Views
3K
Replies
4
Views
504
Replies
2
Views
301

1,181,439
Messages
5,929,924
Members
436,705
Latest member
Priti19

### 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.

### Which adblocker are you using?

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

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