Repeating a number in a given number of columns

[daleward]

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

 

[AlohaJulio]

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]

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]

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.