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
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

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