Formula when between weeks, on rolling calendar

[wiseone]

Hi All,

I stumped myself with this one.

I have a sheet with columns numbered 1 through 52 (representing weeks of the year). The column headers can change based on another selection to keep it a fiscal year (start with 1, end with 52), OR they can be updated to show the current week in the first column and roll over after week 52. There is always a total of 52 columns as shown in is example:

Column Headers Example 1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]..[/TD]
[TD]..[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Column Headers Example 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]49[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]...[/TD]
[TD]47[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I also have 2 columns ahead of these weeks indicating a start week and an end week, which will be used to populate data between and including those 2 weeks. A formula is under each of the week column headers (there are multiple rows, but for simplicity I displayed 1 row of data.)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[TD]Formula here[/TD]
[/TR]
</tbody>[/TABLE]

Can you help make a formula which determines when to populate data between the start and end week, taking into account that weeks can roll over (or wrap around a year). There are several scenarios I can think of which makes 1 formula a challenge.

Scenario 1 - Week columns start at 1 and data is between a lower and higher week, and weeks requiring data do not roll over the year.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]Data should populate here[/TD]
[TD]data should populate here[/TD]
[TD]data should populate here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Scenario 2 - Week columns start at 1, but data rolls over the year, with start week lower than end week.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]...[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[/TR]
</tbody>[/TABLE]

Scenario 3 - Columns do not start at 1, and data rolls over the year, with start week lower than end week.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]...[/TD]
[TD]...[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10[/TD]
[TD]Data should populate here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[/TR]
</tbody>[/TABLE]

Scenario 4 - Columns do not start at one, and start week higher than end week.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]49[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]...[/TD]
[TD]47[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD]Data should populate here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is an example of what I did, but it fails when the weeks requiring data wrap around the year because its basing it on column location.

Weeks of the year are in in AM6:CL6.
Start week is in AK7
End Week is in AL7

=IF(OR(MATCH(AM$6,$AM$6:$CL$6,0<match($ak7,$am$6:$cl$6,0),match(am$6,$am$6:$cl$6,0)>MATCH($AL7,$AM$6:$CL$6,0)),"","Calculation formula here")

thanks in advance!

Robin</match($ak7,$am$6:$cl$6,0),match(am$6,$am$6:$cl$6,0)>

 

[markmzz]

Hi!

Try the formula below in C2 and copy to the right:

=IF($B2>$A2,IF(MEDIAN($A2,$B2,C1)=C1,"For",""),IF((COUNTIF($A2:$B2,C1)=0)*MEDIAN($A2,$B2,C1)=C1,"","For"))

Markmzz

 

[markmzz]

Hi!

A small modification in my last formula:

=IF(MEDIAN($A2,$B2,C1)=C1,IF($B2>$A2,"For",IF(COUNTIF($A2:$B2,C1),"For","")),IF($B2>$A2,"","For"))

Do some tests.

Markmzz

 

[markmzz]

Hi!

Another version:

=IF((MEDIAN($A2,$B2,C1)=C1)+($B2>$A2)+COUNTIF($A2:$B2,C1)=1,"","For")


[TABLE="class: grid, width: 474"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]AW[/TD]
[TD]AX[/TD]
[TD]AY[/TD]
[TD]AZ[/TD]
[TD]BA[/TD]
[TD]BB[/TD]
[TD]BC[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD]49[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Scenario 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD]49[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Scenario 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]For[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Scenario 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Start Week[/TD]
[TD]End Week[/TD]
[TD]49[/TD]
[TD]50[/TD]
[TD]51[/TD]
[TD]52[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]43[/TD]
[TD]44[/TD]
[TD]45[/TD]
[TD]46[/TD]
[TD]47[/TD]
[TD]48[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]50[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD]For[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Scenario 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]**********[/TD]
[TD]***********[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[TD]***[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[wiseone]

ooh. I like this last one the best! I will try in my sheet thank you!

 

[wiseone]

Ok this works! Thanks again! I could use your help with one part of my formula that isn't working. There is a static row above this table in row 1:1 which contains only blanks 1s or 2s. I need to SUMIF row 1:1 if the row with your formula says to execute the formula. (Or simply said: SUM row 1:1 if the formula in row 3:3 should return a result)

Here is my formula with your solution incorporated and the problem section is underlined:

<match($aj7,$am$6:$cl$6,0),(median($ak7,$al7,am$6)=am$6)+($al7><match($aj7,$am$6:$cl$6,0),"",$ai7 (5*52-sum($am$1:$cl$1))*(5-am$1))))
<match($aj8,$am$6:$cl$6,0),"",$ai8 (5*52-sum($am$1:$cl$1))*(5-am$1))))
=IF(OR($AI7="",$AJ7="",$G7="Not Active",$G7=""),"",IF(AND($AK7<>"",$AL7<>""),IF(OR(MATCH(AM$6,$AM$6:$CL$6,0)<MATCH($AJ7,$AM$6:$CL$6,0),(MEDIAN($AK7,$AL7,AM$6)=AM$6)+($AL7>$AK7)+COUNTIF($AK7:$AL7,AM$6)=1),"",$AI7/(5*IF($AL7>$AK7,$AL7-$AK7+1,53-$AK7+$AL7)-SUM(INDIRECT(ADDRESS(1,MATCH($AK7,$6:$6,0))&":"&ADDRESS(1,MATCH($AL7,$6:$6,0)))))*(5-AM$1)),IF(MATCH(AM$6,$AM$6:$CL$6,0)<MATCH($AJ7,$AM$6:$CL$6,0),"",$AI7/(5*52-SUM($AM$1:$CL$1))*(5-AM$1))))


The problem with the current formula is the INDIRECT(MATCH()) is returning a wrong range for all scenarios. I feel like I have to use your criteria to SUMIFS, but don't know how to build a SUMIFS with ALL these criteria.

IF(OR(MATCH(AM$6,$AM$6:$CL$6,0)<MATCH($AJ7,$AM$6:$CL$6,0),(MEDIAN($AK7,$AL7,AM$6)=AM$6)+($AL7>$AK7)+COUNTIF($AK7:$AL7,AM$6)=1)</match($aj8,$am$6:$cl$6,0),"",$ai8></match($aj7,$am$6:$cl$6,0),"",$ai7></match($aj7,$am$6:$cl$6,0),(median($ak7,$al7,am$6)=am$6)+($al7>

 

[wiseone]

Text in my formulas keep disappearing when I post....how do I fix that?

 

[wiseone]

Ok trying again:

Here is my formula with problem section underlined.

=IF(OR($AI7="",$AJ7="",$G7="Not Active",$G7=""),"",IF(AND($AK7< >"",$AL7< >""),IF(OR(MATCH(AM$6,$AM$6:$CL$6,0)< MATCH($AJ7,$AM$6:$CL$6,0),(MEDIAN($AK7,$AL7,AM$6)=AM$6)+($AL7>$AK7)+COUNTIF($AK7:$AL7,AM$6)=1),"",$AI7/(5*IF($AL7>$AK7,$AL7-$AK7+1,53-$AK7+$AL7)-SUM(INDIRECT(ADDRESS(1,MATCH($AK7,$6:$6,0))&":"&ADDRESS(1,MATCH($AL7,$6:$6,0)))))*(5-AM$1)),IF(MATCH(AM$6,$AM$6:$CL$6,0)< MATCH($AJ7,$AM$6:$CL$6,0),"",$AI7/(5*52-SUM($AM$1:$CL$1))*(5-AM$1))))

Here are the conditions that i feel like need to be used to SUM IF

IF(OR(MATCH(AM$6,$AM$6:$CL$6,0)< MATCH($AJ7,$AM$6:$CL$6,0),(MEDIAN($AK7,$AL7,AM$6)=AM$6)+($AL7>$AK7)+COUNTIF($AK7:$AL7,AM$6)=1)

 

[markmzz]

Ok trying again:

Here is my formula with problem section underlined.

=IF(OR($AI7="",$AJ7="",$G7="Not Active",$G7=""),"",IF(AND($AK7< >"",$AL7< >""),IF(OR(MATCH(AM$6,$AM$6:$CL$6,0)< MATCH($AJ7,$AM$6:$CL$6,0),(MEDIAN($AK7,$AL7,AM$6)=AM$6)+($AL7>$AK7)+COUNTIF($AK7:$AL7,AM$6)=1),"",$AI7/(5*IF($AL7>$AK7,$AL7-$AK7+1,53-$AK7+$AL7)-SUM(INDIRECT(ADDRESS(1,MATCH($AK7,$6:$6,0))&":"&ADDRESS(1,MATCH($AL7,$6:$6,0)))))*(5-AM$1)),IF(MATCH(AM$6,$AM$6:$CL$6,0)< MATCH($AJ7,$AM$6:$CL$6,0),"",$AI7/(5*52-SUM($AM$1:$CL$1))*(5-AM$1))))

Here are the conditions that i feel like need to be used to SUM IF

IF(OR(MATCH(AM$6,$AM$6:$CL$6,0)< MATCH($AJ7,$AM$6:$CL$6,0),(MEDIAN($AK7,$AL7,AM$6)=AM$6)+($AL7>$AK7)+COUNTIF($AK7:$AL7,AM$6)=1)

Hi!

Try this two new formulas:

=IF($AL7>$AK7,IF(AM6>=$AK7,IF(AM6<=$AL7,"For",""),""),IF((AM6>=$AK7)+(AM6<=$AL7),"For",""))

=SUMPRODUCT(($AL7>$AK7)*(AM6:CL6>=$AK7)*(AM6:CL6<=$AL7)+($AL7<=$AK7)*((AM6:CL6>=$AK7)+(AM6:CL6<=$AL7)),$AM$1:$CL$1)

Markmzz

 

[markmzz]

Hi!

Try this two new formulas:

=IF($AL7>$AK7,IF(AM6>=$AK7,IF(AM6<=$AL7,"For",""),""),IF((AM6>=$AK7)+(AM6<=$AL7),"For",""))

=SUMPRODUCT(($AL7>$AK7)*(AM6:CL6>=$AK7)*(AM6:CL6<=$AL7)+($AL7<=$AK7)*((AM6:CL6>=$AK7)+(AM6:CL6<=$AL7)),$AM$1:$CL$1)

Markmzz

Another formula:

=IF(($AL7>$AK7)*(AM6>=$AK7)*(AM6<=$AL7)+($AL7<=$AK7)*((AM6>=$AK7)+(AM6<=$AL7)),"For","")

Markmzz