How to add offset to my formula?

[vayana]

I'm using a formula that checks if a day falls between 2 dates that is listed in another sheet.
These dates are listed in 2 columns: B and C
The dates run down 10 rows each time, after which a 5 row break is in place before the next sequence begins

The problem is that I can't drag my formula down the normal way, because it just adds +1 row.
I've read about using "OFFSET", but I can't figure out how to add this to my formula.
In my case the next row should add "+14" to all values in B and C column.

The formula I'm using is:

=IF(OR(C$3=MEDIAN(C$3,Dates!$B6:$C6),C$3=MEDIAN(C$3,Dates!$B7:$C7),C$3=MEDIAN(C$3,Dates!$B8:$C8),C$3=MEDIAN(C$3,Dates!$B9:$C9),C$3=MEDIAN(C$3,Dates!$B10:$C10),C$3=MEDIAN(C$3,Dates!$B11:$C11),C$3=MEDIAN(C$3,Dates!$B12:$C12),C$3=MEDIAN(C$3,Dates!$B13:$C13),C$3=MEDIAN(C$3,Dates!$B14:$C14),C$3=MEDIAN(C$3,Dates!$B15:$C15)),1,0)

The formula on the following row should be:

=IF(OR(C$3=MEDIAN(C$3,Dates!$B20:$C20),C$3=MEDIAN(C$3,Dates!$B21:$C21),C$3=MEDIAN(C$3,Dates!$B22:$C22),C$3=MEDIAN(C$3,Dates!$B23:$C23),C$3=MEDIAN(C$3,Dates!$B24:$C24),C$3=MEDIAN(C$3,Dates!$B25:$C25),C$3=MEDIAN(C$3,Dates!$B26:$C26),C$3=MEDIAN(C$3,Dates!$B27:$C27),C$3=MEDIAN(C$3,Dates!$B28:$C28),C$3=MEDIAN(C$3,Dates!$B29:$C29)),1,0)

I've attached an example with what I would like to accomplish. I just don't want to manually change all the formulas all the way down 135 rows.

Any help is much appreciated.

[zbor]

Would this help?

=--(SUMPRODUCT((OFFSET(Dates!$B$6,14,0,10)<=$E$4)*(OFFSET(Dates!$B$6,14,1,10)>=$E$4))>0)

Change red number to desired section (0 for first, 14 for second, 28 for third... Or put somewhere reference like A1 and change only A1 value)

[vayana]

Hello Zbor,

Sorry mate, If I use that formula instead of the other one, the result isn't correct. (all dates light up green)

Basically I'm using the columns in sheet "Total" as individual days. so that's 7 x 365 days (year 2013 -2020)
Each column represents 1 day; I've narrowed them down to 1px to get a nice overview.

the formula is entered under each day, and checks if that day falls between 2 dates on the other sheet.
if it does, it returns 1, if it doesn't, it returns 0. I'm using Conditional Formatting to replace the 1 for a green color.

If you can, then please play around with the dates and you'll see what happens on the "Total" sheet.

I'm more than happy if you know of another formula that does the same as what I'm trying to get now, but the only way I see it is by changing the numbers in the formula as it is.

Cheers,

Michael.

[hemesh]

Try above in C4 as you drag down formula it will increment by 14 rows

[hemesh]

Hello You could use
for B6:C6 INDIRECT("Dates!$B"&((ROW(A6)-ROW($A$6))*14)+6&":"&"$C"&((ROW(A6)-ROW($A$6))*14)+6) to get dates!$B6:$C6
for B7:C7 INDIRECT("Dates!$B"&((ROW(A7)-ROW($A$7))*14)+6&":"&"$C"&((ROW(A7)-ROW($A$7))*14)+6) to get dates!$B7:$C7
for B7:C7 INDIRECT("Dates!$B"&((ROW(A8)-ROW($A$8))*14)+6&":"&"$C"&((ROW(A8)-ROW($A$8))*14)+6) to get dates!$B8:$C8
and so on
when you drag down each will be incremented by your desired number

[vayana]

I'm terribly sorry, but I fail to comprehend where to use that bit...

Do I include it in the current formula or is it meant to replace it?

If you don't mind, ideally it makes the most sense if you could enter it as a working example to the sheet I uploaded.
I understand it's a bit more effort, but I would highly appreciate it.

Cheers,

Michael.

[zbor]

uops, my mistake.. Try now:

=--(SUMPRODUCT((OFFSET(Dates!$B$6,14,0,10)<=$C3)*(OFFSET(Dates!$B$6,14,1,10)>=$C3))>0)

If you want automatically for 14 rows:

=--(SUMPRODUCT((OFFSET(Dates!$B$6,14*(ROW(A1)-1),0,10)<=$C3)*(OFFSET(Dates!$B$6,14*(ROW(A1)-1),1,10)>=$C3))>0)

[vayana]

I'll give it a shot in an hour or so. It seems we cross-posted some replies. It's 18:00 here now and I need to leave the office. I'll check it out after dinner and come back to you with the results.

Thanks,

Michael.

[Pete_UK]

I've inserted a new column A in the Total sheet, and used that for the reference code that you have in column A of the Dates sheet. Then the formula in D4 (was C4) can be changed to this:

=IF(SUMPRODUCT((D$3>=INDIRECT("Dates!$B"&MATCH($A4,Dates!$A:$A,0)+3&":$B"&MATCH($A4,Dates!$A:$A,0)+12))*(D$3<=INDIRECT("Dates!$C"&MATCH($A4,Dates!$A:$A,0)+3&":$C"&MATCH($A4,Dates!$A:$A,0)+12))),1,0)

which can then be copied across and down as required. You can simplify it further by having this in C4:

=IFERROR(IF(A4="","",MATCH($A4,Dates!$A:$A,0)),"")

(coloured white, so you don't see it), then you can have this in D4:

=IFERROR(IF(SUMPRODUCT((D$3>=INDIRECT("Dates!$B"&$C4+3&":$B"&$C4+12))*(D$3<=INDIRECT("Dates!$C"&$C4+3&":$C"&$C4+12))),1,0),0)

I've copied this across and then down to row 20 in the attached workbook, to show it working.

Hope this helps.

Pete

[kvsrinivasamurthy]

Insted of this

=IF(OR(C$3=MEDIAN(C$3,Dates!$B6:$C6),C$3=MEDIAN(C$3,Dates!$B7:$C7),C$3=MEDIAN(C$3,Dates!$B8:$C8),C$3=MEDIAN(C$3,Dates!$B9:$C9),C$3=MEDIAN(C$3,Dates!$B10:$C10),C$3=MEDIAN(C$3,Dates!$B11:$C11),C$3=MEDIAN(C$3,Dates!$B12:$C12),C$3=MEDIAN(C$3,Dates!$B13:$C13),C$3=MEDIAN(C$3,Dates!$B14:$C14),C$3=MEDIAN(C$3,Dates!$B15:$C15)),1,0)

Try this

=IF(OR(PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$6:$C$6,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$7:$C$7,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$8:$C$8,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$9:$C$9,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$10:$C$10,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$11:$C$11,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$12:$C$12,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$13:$C$13,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$14:$C$14,(ROW(A1)-1)*14,)),PL$3=MEDIAN(PL$3,OFFSET(Dates!$B$15:$C$15,(ROW(A1)-1)*14,))),1,0)

Then drag down.

[zbor]

Ignore my above post, I've fix it now to real example:

=--(SUMPRODUCT((OFFSET(Dates!$B$6,14*(ROWS($C$4:$C4)-1),0,10)<=C$3)*(OFFSET(Dates!$B$6,14*(ROWS($C$4:$C4)-1),1,10)>=C$3))>0)

[vayana]

I would really like to thank everyone who has contributed to solve my problem/question. Since I had the luxury of picking from several solutions that were provided, I ended up going with Pete's solution as it also skips the empty rows automatically between the groups of properties by using a "MATCH" in column C. Everyone's help and support is greatly appreciated, and excelforum is one of the best forums around in my opinion. The response-time and quality of the answers provided by its members is absolutely outstanding.

You have all been thanked personally as well, and I hope to be helpful for someone some day too.

Cheers,

Michael.

[hemesh]

Thanks for the Feedback!

[Pete_UK]

Yes, thank you for the kind words, Michael.

Pete