Repeating a number in a given number of columns

daleward

New Member
Joined
Mar 8, 2004
Messages
1
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
Joined
Feb 10, 2004
Messages
188
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
Joined
Jan 1, 2003
Messages
2,062
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
Joined
Feb 18, 2004
Messages
57
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top