Mod function to repeat values

[ddnron]

Hi, Thanks for your assistance and time.

This formula works nice for my needs, of repeating a value.

=if(mod(column()-1,3)=0,$a$4,0) it displays my value every 3rd column, in row 4.


I have 365 columns with date in row 3, for each day of the year. E3 has Jan 1, 2018.

i want to put a date in cell b4 and then have this formula, start on that date in the correct/corespondent column, which has that date in row 3... then repeat every 3 columns.

Is it possible to tweak this to select an appropriate column, based off date in b4, then find that column, then start the value every three columns.


Thanks again for help

 

[Leith Ross]

Hello ddnron,

This worked for me...

=IF(AND(COLUMN()>=MATCH($B$4,$E$3:$NE$3,0)+4,MOD(COLUMN()-1,3) = 0),$A$4,0)

 

[ddnron]

Hi Leith, Thanks for the quick reply. I used the date 1-12, and it worked fine, as well as 1-15, 1-18, etc. When I entered 1-9, then changed date to 1-8, or 1-7, it did not change the dates, it left it starting at 1-9. When I entered 1-6, it worked. I messed around with the +4 and -1 in the formula, but I could not get it to change correctly, when I changed the dates. any other suggestions would be appreciated. Thanks

 

[ddnron]

Hi Leith, Thanks for the quick reply. I used the date 1-12, and it worked fine, as well as 1-15, 1-18, etc. When I entered 1-9, then changed date to 1-8, or 1-7, it did not change the dates, it left it starting at 1-9. When I entered 1-6, it worked. I messed around with the +4 and -1 in the formula, but I could not get it to change correctly, when I changed the dates. any other suggestions would be appreciated. Thanks

Found solution, adjusted to work with my cells. Also used absolute cell reference for x,y,n.

=IF(AND(A1>=y,MOD(A1,n)=MOD(y,n)),x,"")