Mod function to repeat values

ddnron

Board Regular
Joined
Oct 11, 2003
Messages
110
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

Well-known Member
Joined
Mar 17, 2008
Messages
1,878
Office Version
2010, 2007
Platform
Windows
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

Board Regular
Joined
Oct 11, 2003
Messages
110
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

Board Regular
Joined
Oct 11, 2003
Messages
110
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,"")
 

Forum statistics

Threads
1,086,101
Messages
5,387,829
Members
402,082
Latest member
AstroSpider

Some videos you may like

This Week's Hot Topics

Top