yogi_Compute The Total Distance Traveled In Last 7 Days Where Column C Lists Dates In Cells C5 C11 C17... And Distances In Cells C6 C12 C18 ...

                                          Google Spreadsheet   Post  #882
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 22, 2012
user ed-oxford said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/cj7Hknqe_0U)
Fixing a problem with references to cells of a column
Windows 7

Dear all,

I need some help with the following formula, which does not work:

VERSION 1=ARRAYFORMULA(FILTER(C:C,IF(MOD(ROW(C:C),6)=0,IF(TO_PURE_NUMBER(TODAY())-TO_PURE_NUMBER(OFFSET(C:C,-1,0))<=7,TRUE()),FALSE())))

The very similar version works:

VERSION 2=ARRAYFORMULA(FILTER(C:C,IF(MOD(ROW(C:C),6)=0,IF(TO_PURE_NUMBER(TODAY())-TO_PURE_NUMBER(OFFSET(C:C,+5,0))<=7,TRUE()),FALSE())))

but does not get the right result.

These formulas are meant to go through column C, which has the following information: C5 is a date and C6 the distance covered that day, C11 is the next date and C12 the distance for that day, and so on for many rows. The pattern is a cycle repeated every 6 cells: starting from C5 there is a date, and then right below at C6 a distance, and again C11 and C12, C17 and C18. The formula above filters column C so that it takes all distances (contained in C6, C12, C18, etc.) and then (incorrectly) also picks up the dates of those distances (C5, C11, C17, etc.) and compares them to TODAY(), and if the distance was covered in the last seven days, it prints it.

I don't know why Version 1 does not work. It gives an error which I suspect is because doing OFFSET(C:C,-1,0) has the consequence that it picks up row 0, which is a reference error. I have tried to make version of it that wrap OFFSET inside of an IFERROR, but none of that seems to fix the problem. Maybe there's a way to fix this, but I don't know it.

Version 2 does not work because it associates the wrong date with a distance. For instance, the date in C11 is pegged with distance C6, when in fact the date C5 should be pegged with distance C6.

I imagine two solutions for this:

1) Fix Version 1 so that it can avoid whatever error creeps in, or

2) Maybe it's possible to start column C not from row 1, which is what ARRAYFORMULA does, but at another higher row. In this case, even starting at row 2 (so element C2) should be enough.

Please help.

Best

Ed

---------------------------------------------------------------------------------

following is a solution to the problem, and in Sheet2 I have provided a solution to a bit more generalized problem