Excel Google Spreadsheet LibreOffice Calc How To

How to choose a start date for the period with start date closest to today and end date in the future

  • Column A contains a name for the range we are looking for
  • Projects!E:E is the period start date
  • Projects!F:F is the period end date
=ArrayFormula(max(if((A31=Projects!A:A)*(Projects!E:E<=today())*(Projects!F:F>=today()),Projects!E:E)))
Gives you the closest start date, or 0 if no matching period is found
  1. (A31=Projects!A:A)*(Projects!E:E<=today())*(Projects!F:F>=today() is the logical AND for the conditions indicated, because AND() is an aggregator formula ,just like min or max, that would ignore the iterator that ArrayFormula uses. I.e. it will "and" the range, not the specific cells.
  2. max works as the closest date because of the bounds built into the if conditions
  3. I've omitted the "else" condition in the "if", but "0" could work just fine, and it work fine (but not in the below formula)
Complimentary formula for the closest end date is as follows:
=ArrayFormula(min(if((A31=Projects!A:A)*(Projects!E:E<=today())*(Projects!F:F>=today()),Projects!F:F)))
  1. the lack of the "else" condition argument in the "if" statement is important, because you want min return 0 only in case that conditions are not satisfied (FALSE), but putting the number in the "if" will return this number if no conditions are satisfied. You can not use 0 bacause it's a min function, not max like in the formula above, so it will always pick 0.
Alternative formula, that gives the same results as the first one, but uses delta in the date calculations

=ArrayFormula(today()-min( if((A31=Projects!A:A)*(Projects!E:E<=today())*(Projects!F:F>=today()),today()-Projects!E:E,today())))

  1. today()-Projects!E:E is the "delta" conversion to give the start date closest to today
  2. last ",today()" in the "if" ensures that if no match found, you will get 0 as the answer
  3. min(...) aggregates the data to give you the closest start date to today, if found.
  4. beginning "today()-" compensates for the earlier conversion from date to the delta, to give you the actual date (or 0 if nothing found)

Use today() instead of now() because now() is overly precise for our needs and will cause odd date calcs when used in the formula multiple times. I.e. sometimes now()-now(), it will be equal 0, sometimes less than 0

How to create a scale for weeks based on the number of work days that fall in range in these weeks

  • Row 2, J through BO contains Mondays of each week. Here is the formula:

=$B2-weekday($B2)+T1*7+2

Where row 1 contains a number of the week from a date specified in B2

  • E2 is the start of the range
  • F2 is the end of the range
  • Financials!$D$121:$L$121 is the holiday schedule

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

Explanation:

  • Net work days between the start date and teh start of the week it is calculated for, divided by 5 working days. Same for the end date
  • min/max pair caps the scale in the 0 to 1 range

How to use range that has end row provided from a formula

  • Assume starting with the next row
  • BS12 contains the number for the row to extend the range to (aka end)

For the range that is in a fixed column use

= indirect("$BO" & (row()+1) & ":$BO" & $BS12)

For the range that can be copied left and right use the following

= indirect(address(row()+1,column()) & ":" & address($BS12,column()))

Explanation:

  • Address() formula translates numerical column index, returned by column() into an alphabetical index required by the indirect() formula

How to calculate weekly numbers based on monthly data

  • Row 2, from S to BN contains dates for Mondays of each week
  • E2 is the start date for calculations
  • F2 is the end date for calculations
  • Column S indicate whether to do calculations or not
  • BN4 is the monthly amount

Spread the following formula directly under the values in Row 2

=if(or(counta(S5:S6)=0,S$2<$E$2,S$2>$F$2),0,$BN4*(month($F$2)-month($E$2)+1)/(count($J$2:$BM$2)-countif($J$2:$BM$2,"<"&$E$2)-countif($J$2:$BM$2,">"&$F$2)))

Explanation:

  • month($F$2)-month($E$2)+1 - number of whole months between two dates
  • count($J$2:$BM$2)-countif($J$2:$BM$2,"<"&$E$2)-countif($J$2:$BM$2,">"&$F$2) - number of whole weeks between two dates

How to list values that are not a part of a different list

  • Column B is a list of suspects (source)
  • Column F is a list of allowed values (check list)
  • Column L is a resultant list, starting from the second row. First row should contain a value that does not exist in F.

=INDEX($B$2:$B$394,MATCH(0,IF(ISERROR(MATCH($B$2:$B$394,$F$2:$F$50,0)),COUNTIF($L$1:$L1,$B$2:$B$394),MATCH($B$2:$B$394,$F$2:$F$50,0)),0))

Note the second $L1 in the countif construct lacks $ in front of the row number, on purpose. This way when you copy this formula down from L1 it grows itself. Explanation:

  • An item from the list of suspects, that has a "0" in the list that is constructed by matches either in the already constructed list, or in the matches against the check list

How to list unique values

  • Column G is the source list
  • Column J is the resulting list of unique values
  • First item in J (J2) should be =G2, then enter this as an array formula (Ctrl-Shift-Enter) in J3 and onward

{=INDEX($G$2:$G$993,MATCH(0,COUNTIF($J$2:J2,$G$2:$G$993),0))}

How to fill in split categories automatically

Lets assume that J, K, L contain

* fred meyer,  food,   50%
* fred meyer,  clothing,   25%
* fred meyer,  home furniture and furnishings, 25%

  1. Move the dollar amount into F
  2. Move the following to D
  3. Copy both of the following TOGETHER to D and E.
  4. Insert blank line below, copy and paste all content. It should fill in automatically

{=INDEX($legends.$K$20:$L$50,SMALL(IF(VLOOKUP($C56,$legends.$A$2:$B$785,2,0)=$legends.$J$20:$J$50,ROW($legends.$J$20:$J$50)-19,""),SUMPRODUCT(--($A$2:$A56=$A56),--($C$2:$C56=$C56))))}

How to translate duration to words

  • B and J are some test conditions
  • I is the date of availability

=if(or(B7="",J7>now()),"",if(I7="","now",if((I7-now())/7<=1,"less than a week",if(and(1<(I7-now())/7,(I7-now())/7<=2),"one to two weeks", if((YEAR(I7)-YEAR(NOW()))*12+MONTH(I7)-MONTH(NOW())<=1,"about a month",(YEAR(I7)-YEAR(NOW()))*12+MONTH(I7)-MONTH(NOW()) &" months")))))

How to get the heading line several rows above the value

=IF(Y7=0,"",INDEX(Projects!A:A,SUMPRODUCT(MAX((IF(ISNA(INDIRECT("'Projects'!C1:C"&Y7)),0,INDIRECT("'Projects'!C1:C"&Y7)="Lead:"))*ROW(INDIRECT("'Projects'!C1:C"&Y7))))))

where Column Y is the line number for first match of the value

=ArrayFormula(min((if((A7=Projects!A:A)*(H7=Projects!E:E),ROW(Projects!A:A)))))

Where A is the value and H is the start date, calculated as follows

=ArrayFormula(if(or(min(if(A13=Projects!A:A,Projects!E:E))=0,min(if(A13=Projects!A:A,Projects!F:F))<now()),"",min(if(A13=Projects!A:A,Projects!E:E))))


How to find a project a person is assigned to

For an array that looks like this:

Client1, Lead:
Alex
Bob

Client2, Lead:
Ralph
Joe

Given "Alex" figure out the assigned project

=IF(ISNA(MATCH(A2,'Active jobs'!A:A,0)),IF(C2="Sub","","Bench"),INDEX('Active jobs'!A:A,SUMPRODUCT(MAX((IF(ISNA(INDIRECT("'Active jobs'!B1:B"&MATCH(A2,'Active jobs'!A:A,0))),0,INDIRECT("'Active jobs'!B1:B"&MATCH(A2,'Active jobs'!A:A,0))="Lead:"))*ROW(INDIRECT("'Active jobs'!B1:B"&MATCH(A2,'Active jobs'!A:A,0)))))))


Explanation
Bottom up:
MATCH(A3,'Active jobs'!A:A,0) - get row number for the matching person
INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0)) - convert to a range starting from 1 down to the matching person

INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))="Lead:"))*ROW(INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))))) - create a numerical array that steadily increases for each row matching the "Lead:" tag

MAX((IF(ISNA(INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))),0,INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))="Lead:"))*ROW(INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))))) - get the max i.e. number corresponding to the last matched "Lead:", filtering out N/A entries that for some reason give TRUE in the '=' evaluation

SUMPRODUCT(MAX((IF(ISNA(INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))),0,INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))="Lead:"))*ROW(INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0)))))) - executes MAX as an array formula, giving the last match for the "Lead:" for the corresponding range

=IF(ISNA(MATCH(A3,'Active jobs'!A:A,0)),"",INDEX('Active jobs'!A:A,SUMPRODUCT(MAX((IF(ISNA(INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))),0,INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0))="Lead:"))*ROW(INDIRECT("'Active jobs'!B1:B"&MATCH(A3,'Active jobs'!A:A,0)))))))
Filter out any non-matching and give the value of the "Project" column (A:A) that is next to the last matching "Lead:" string

@HowTo @Microsoft @Google @FOSS