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
- How to create a scale for weeks based on the number of work days that fall in range in these weeks
- How to use range that has end row provided from a formula
- How to calculate weekly numbers based on monthly data
- How to list values that are not a part of a different list
- How to list unique values
- How to fill in split categories automatically
- How to translate duration to words
- How to get the heading line several rows above the value
- How to find a project a person is assigned 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
Gives you the closest start date, or 0 if no matching period is found
- (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.
- max works as the closest date because of the bounds built into the if conditions
- 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)
=ArrayFormula(min(if((A31=Projects!A:A)*(Projects!E:E<=today())*(Projects!F:F>=today()),Projects!F:F)))
- 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.
=ArrayFormula(today()-min( if((A31=Projects!A:A)*(Projects!E:E<=today())*(Projects!F:F>=today()),today()-Projects!E:E,today())))
- today()-Projects!E:E is the "delta" conversion to give the start date closest to today
- last ",today()" in the "if" ensures that if no match found, you will get 0 as the answer
- min(...) aggregates the data to give you the closest start date to today, if found.
- 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%
- Move the dollar amount into F
- Move the following to D
- Copy both of the following TOGETHER to D and E.
- 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
