Google Docs How To

Google Spreadsheet

How to sum values falling between two dates

=ArrayFormula(sum(filter(J22:BM22,J$2:BM$2>=E$2,J$2:BM$2<F$2)))

How to show day for monday of Nth week

  • B2 is the start date
  • S1 is the number of the week from the start date
=$B2-weekday($B2)+S1*7+2

How to calculate a scale from 0 to 1 based on a number of working days in a week, that fall between a start date and an end date

  • E2 is the range start
  • T2 contains monday of the next week's range
  • Financials!... range is the list of holidays
  • F2 is the range end
  • S2 is monday of this week

min(max(networkdays($E$2+1,T$2,Financials!$D$119:$L$119)/5,0),1)*max(min(networkdays(S$2,$F$2,Financials!$D$119:$L$119)/5,1),0)

How to get heading for a cell that shows under it, filtering by start and end dates

The heading is identified by "Lead:" in column C. Look up by A's cell content.
=IF(AK2=0,"",INDEX(Projects!A:A,SUMPRODUCT(MAX((IF(ISNA(INDIRECT("'Projects'!C1:C"&AK2)),0,INDIRECT("'Projects'!C1:C"&AK2)="Lead:"))*ROW(INDIRECT("'Projects'!C1:C"&AK2))))))
Where AK contains the following
=ArrayFormula(min((if((A2=Projects!A:A)*(H2=Projects!E:E),ROW(Projects!A:A)))))
Where A2 is the lookup criteria, H2 contains the auto calculated project start date:
=ArrayFormula(if(or(min(if(A2=Projects!A:A,Projects!E:E))=0,min(if(A2=Projects!A:A,Projects!F:F))<now()),"",min(if(A5=Projects!A:A,Projects!E:E))))
Projects tab F and E are the lookup criteria start and end dates respectively

How to calculate range based upon some criteria

  • We want the sum of a range. OFFSET provides a range, based upon a starting point (optional offset in x and y) and then some dimensions.
  • A1 is the reference corner, 0 and 0 are the offsets (we want to stay in the A column). the 1 at the end means 'only the A column'.
  • the 'height' parameter of the OFFSET function is calculated by row of empy cell - ROW(A1) (which is the starting cell)
  • INDEX(ROW(A1:A), MATCH(TRUE, ISBLANK(A1:A), 0)) calculates the rownumber of the first empty cell below A1.

=SUM(OFFSET(A1,0,0,INDEX(ROW(A1:A),MATCH(TRUE,ISBLANK(A1:A),0))-ROW(A1),1))

@TechnicalNotes @Google