General Tips
Recommended order of operations
Add filters to the sheet
Review sheet and remove definitely unusable points in order to speed up the formula calculations
This would be points such as logical operators (AND/OR points) or trends (@h points in haystack)
Use below substring formula to extract equipment names if possible
Recommended to assign all points to an equipment (where possible) initially. Remove the equipment after the point has been reviewed and determined it will not be used. This allows for some level of tracking for points that have been processed
Copy equipment names into a new sheet, and remove duplicates to get a full equipment list
Use this newly generated equipment list to fill the equipment list tab, obviously completing the other required columns such as level and zone
Copy Level and Zone columns into a new sheet to get a list of levels and zones to add. (sort by level, append a comma to each zone so that you can copy / paste into PEAK)
Example of fast zone adding - PEAK will remove duplicates automatically
Filters
Check for equipment name patterns
Check for metadata name patterns within each equipment type. e .g. what have they called VAV damper position for this site
Find miscellaneous fans easier by searching for key words (status / enable points)
Filter by the validation column to review points with errors or #n/a indicating an issue with the equipment name or broken validation formula
Spreadsheet Tips
General - mention issues with testing in validated columns (A and B)
Note that with all formula described below, be careful not to test in Columns A and B of the Sheet Commissioning Map Input sheet. This is due to the form validation that will reject all changes in a single cell if it doesn’t match the validation. Use another column (such as C or D) to test and copy the formula back into A or B when the output matches your desired outcome.
Combines strings together
Methods to do e.g. combine cells A1, A2 and append “Manual String” to the end
Formal method is to use a function such as
=concat(A1, A2, "Manual String")Simplest is to join with &
=A1&A2&"Manual StringString"Combining formula - Concatenation can also have function outputs inserted in the middle e.g. including a mid substring function
=A1&mid(A2,5)&"Manual String"
Substring methods
How to extract a section of texts from a different cell automatically. This is typically useful when trying to assign equipment to large sections of the sheet if a pattern occurs. This will allow you to filter for a specific pattern (e.g. all objects with VAV in the name) and assign equipment to them in a time efficient manner. Typically best used for BMS vendors with highly structured but lengthy object names (Haystack, JCI)
General formula
Left - Get the left portion of a different cell specifying the length of the string you want
Right - Get the right portion of a different cell specifying the length of the string you want
Mid - Get the middle portion of a different cell, specifying the starting point and the length of the string you want
Len - Gets the length of the full string in a cell
Find - Searches for the first appearance of a given string in a specific cell. Option to start searching after a specific length of characters (e.g. only look for the string after the 4th character)
Advanced form x 2 - start and end points, diagrammatic breakdown
This still follows MID(Text to split, start point, length) however the start and the length are dynamic based on the string
The above case looks at a dynamic method of pulling equipment name from a complex string. This is useful when the length of the name is not set (e.g. no leading zeroes on levels or some units have additional suffixes). In the above example:
Starting characters are defined based on the naming pattern of equipment. In this case, we are targeting all ACUs. There is an assumption that ACU appears first when the full equipment name appears
The end point can be specified as the delimiter at the end of the name (in this case, the “.” between the equipment name “ACU_L14_04” and the start of the object/metadata name “AC_FanFlow”. However, the following points should be noted
This is not the first occurrence of “.”. As such, it is necessary to choose an appropriate starting point (leftmost point) in the string before the formula attempts to find the specified character. The most logical place to start the search is the start of the equipment name, which is why the second Find(“ACU”) is used.
This would be very difficult to achieve via a formula if the ACU name included “.” - e.g. “ACU.L14.04”
The start point is as above. Subtracting the end point from the start point will give the length of the string that we want. This is represented by the full formula:
find(".", E4053, find("ACU",E4053)) - find("ACU", E4053)
Sometimes patterns will appear outside the substring and can still be used with fixed modifiers
This example is even more complex as the string we are looking for does not have a standardised starting point. The Power Sub Meters can begin with many patterns, such as TDB, MDB, MSSB, etc or even just an equipment name (CHWPs). However, there was a pattern that each were filled in the navigation path of “EM”. While we prefer a different prefix (PM or PSM), we are able to utilise this to create an effective substring formula.
Adding + 3 after the first find pushes the start of the substring to beyond the “EM.” portion
The final -3 does the same with respect to dynamically calculating the string length
Can find the location of a provided value in an array of data, also with the ability to return a value relative to the found term. Similar to vlookup but more robust, more flexible and performs better. This is a two part formula:
Index - Given an array, find the value at a specified index (row and column)
Match - Returns the relative position of an item in a range that matches a specified value.
The combination of the two creates a method to lookup a value in a large array, with the potential to also return a value from an adjacent column
Use cases:
Confirm if values exist in a set of data (comparing a new haystack points list with one that has been commissioned already, which points are not commissioned)
When device names do not provide equipment names, but a map of device name to equipment names exists, it can be used to automatically assign equipment as per main use-case for substrings above. Example of this below:
For JCI site where Device Names do not reflect Equipment names, and a map can be created by global search:
From tool-tip, this JCI site does not include equipment name in device name
Raw Extract from JCI Global Search
Extracted controller names with substrings
There are two goals at this stage. First, as we now have a list of equipment based on a 1-to-1 relationship between Equipment and Devices, the audit sheet can be populated with Column B of the reordered Device-Equipment map. Secondly, there is an opportunity to use Index Match (along with some further substring manipulation) to assign all equipment names to each row. See below result:
1 formula to rule them all (for this use-case). Value in B2 is the Mid formula section of the main index match
QA Checks Prior to Application in PEAK
Two most common issues related to sheet commissioning submission are related to missing information on PEAK; equipment or level / zone combinations. To mitigate these two errors, the following two checks are recommended:
Copy paste equipment from sheet back into PEAK - this ensures all of the equipment used in the sheet exist on PEAK. This may also highlight some missing level and zones as they will fail validation within the commissioning table on the BACER.
Filter the sheet for overridden level / zone and add each back into PEAK (or at least visually confirm each exists in PEAK).
In addition to the errors that occur during the submission of the sheet for application, common mistakes are missing points or equipment. Recommended checks for this include:
Remove all filters and add them again to make sure filters are capturing all rows
Drag down all validation formula to every row in case there is any mistakes that occurred when working on the sheet
Filter on validation column (first to ensure no N/A values to indicate failed validations, then check all OK and all Errors separately and resolve as required)
Filter on equipment name and do a rough count for each to confirm if they have an equal number of favourites (or reasons why they wouldn’t be)
Filter on each object name to ensure key points aren’t missed based on 1 equipment (especially equipment with a high count)
e.g. look for all VAV damper positions based on the naming convention from 1 VAV (no filter on validation) to see if any are missed
Filter by common keywords (status, enable for instance) in the appropriate column (Object name or description for BACER sheets, Dis for Haystack sheets)