trk1 said:
I have one query on the SS sheet "Apps by NOC/Caps Estimates", column "F". What is the basis of this calculation, kindly share. Particularly how "current # " is based ?
Thanks and best wishes!
Hi Trk1,
You can actually see all the formulas in the special Formula spreadsheet. Values from it are copied to the main spreadsheet periodically:
https://docs.google.com/spreadsheets/d/1zRt8G7dejtaSf3pgRA_8FFhpU3qY6Q-Q7dBRFSU_Q2I/edit#gid=1293936751
The exact formula for column F is the following:
=IFERROR(QUERY('FSW14 Applicants'!$C$4:$AR,"select count(C) where C="&$A4&" and dateDiff(date'"&TEXT(TODAY(),"yyyy-MM-dd")&"',AR)<=37 label count(C) ''")/(NETWORKDAYS(TODAY()-37,TODAY())-1),0)
Basically, it counts all the applications for a given NOC which were added to the spreadsheet in the last 37 calendar days, and divides this count by the number of work days in the last 37 calendar days. 37 is chosen sort of arbitrarily, as a value large enough to eliminate noise and small enough so that the resulting value reflects the
current speed.
I hope this answers your question, but if you have more questions, feel free to ask.