Excel Formula for Picking Columns with Skills Matrix Higher Than Zero

Objective

To create an Excel formula that selects or identifies columns in a skills matrix where the values are greater than zero.

Assumptions

• The skills matrix is organized in a range, for example, A1:D10, where rows represent individuals and columns represent different skills.
• Only the columns with values greater than zero should be highlighted or returned.

Formula Construction

Basic Formula

To create a simple selection of columns with values greater than zero, we can utilize the FILTER function (available in Excel 365 and Excel 2021). The formula can be structured as follows:

=FILTER(A1:D10, A1:D10>0)

Explanation of the Formula Components

• FILTER(array, include): This function returns an array that meets specific criteria.
  • array: The range of the skills matrix (e.g., A1:D10).
  • include: A logical condition applied to the array, which in this case is A1:D10>0. This condition checks each cell in the selected range and returns TRUE for cells greater than zero.

Alternative using Array Formulas for Older Versions

For versions prior to Excel 365, we can use an array formula in combination with INDEX, MAX, and IF functions:

=INDEX(A1:D10, , SMALL(IF(A1:D10>0, COLUMN(A1:D10)-COLUMN(A1)+1), COLUMN(A1:A1)))

Explanation of the Alternative Formula Components

• INDEX(array, row_num, column_num): Returns the value of a referenced cell or array.
• IF(condition, value_if_true, value_if_false): Checks if the condition is met and returns one value for TRUE and another for FALSE.
• COLUMN(): Returns the column number of a specified reference.
• SMALL(array, k): Returns the k-th smallest value in a data set.

Important Notes

• Array Formulas must be entered using Ctrl + Shift + Enter (CSE) instead of just Enter.
• Ensure to replace A1:D10 with the actual range of your skills matrix.

Practical Example

Suppose you have the following skills matrix in range A1:D10:

Using the FILTER function:

=FILTER(A1:D10, A1:D10>0)

This will return only the columns where there are values greater than zero.

Conclusion

By employing the FILTER function or, where necessary, an array formula, you can effectively identify and select columns in a skills matrix that contain values greater than zero. This enhances data analysis and reporting within Excel, allowing for focused evaluation of skillsets that individuals possess. For deeper learning on Excel functionalities, consider exploring courses available on the Enterprise DNA Platform.