I. Intro
The LIST function converts multiple sets of data into a list or an array. It is commonly used in information analysis in domains such as operations management and planning management. For example, you can use the LIST function to summarize equipment status, eliminating the need to individually check the status of each device.
Arguments
LIST([value, ...])
- value: The data that you want to turn into an array.
Note: The order in which you enter the values in the formula determines the order in which they are listed in the result.
II. Steps
- Open the base and click the + icon on the rightmost side of the table to add a new field. Enter a field title and change the field type to Formula.
250px|700px|reset
- In the formula editor, enter the LIST function, and select the table or field you need.
- For example, LIST([Product List],[Status],[Period]) as shown in the following images will create a text list, where the text from each field is summarized in the formula field.
250px|700px|reset
250px|700px|reset
- If you want to filter out non-empty fields in the table, you can use LIST([Product List],[Status],[Period]).FILTER(CurrentValue!="") as shown in the following images. The FILTER function filters for non-empty fields and the LIST function lists the returned values.
250px|700px|reset
250px|700px|reset
Note: If the "value" in the LIST function contains a date, you need to use the TEXT function to change its format, otherwise the date field will be converted to a serial value.
For example, the [Start Date] in LIST([Product Name],[Start Date],[Product Category]) is a date field, so you need to use the TEXT function to change its format. The formula will then look like this: LIST([Product Name],TEXT([Start Date],'YYYY/MM/DD'),[Product Category]).
III. Use cases
Equipment inspection
Description: For equipment inspections, there are often multiple dimensions to evaluate, such as whether the appearance meets standards, whether the controller is responsive, and whether the emergency button is reliable. By summarizing the results of different fields through the LIST function, and then performing further analysis with the IFS and COUNTIF functions, you can quickly evaluate whether a piece of equipment is qualified.
Step 1: Summarize the results of the equipment performance check through the LIST function.
Formula: LIST([Appearance],[Controller],[Emergency Button])
250px|700px|reset
250px|700px|reset
Step 2: Determine the equipment status through the IFS() and COUNTIF() functions
Formula: IFS([Facility Status].COUNTIF(CurrentValue="Pass")=3,"Qualified",[Facility Status].COUNTIF(CurrentValue="")=3,"To be checked",TRUE,"❌")
250px|700px|reset
250px|700px|reset
Report submission
Description: Multiple reports, such as quality and evaluation reports, may need to be completed before a project can be completed. By combining LIST, CONTAIN, and IF functions, you can determine whether all the necessary reports are completed.
Formula: IF (CONTAIN (LIST ([Quality Report], [Evaluation Report]), "N"), "❌," "✅")