Abstract:

Array formulas, especially conditional array formulas seem to be the stepchild of spreadsheet studies; they are not taught by most of teachers of Informatics. Statistics show that a high number of teachers are not familiar with this feature, while others know about it but never realized its importance in the classes of spreadsheets. Consequently, most of the teachers belonging to the latter group just ignore the conditional array formulas and do not introduce them to their students. They do not do, in spite that teaching conditional array formulas to normal classes of Informatics has several advantages.

The advantages of conditional array formulas
Programming, creating algorithms, coding them in an environment which the students are familiar with is one of the advantages of spreadsheets. The other advantage of spreadsheets-languages is that they belong to the group of functional languages and such as students, from previous studies in other subjects, are familiar with the concept of function, they know how to handle them, how to call them, how to create multi-level embedded functions. Considering the programming aspect, conditional array formulas would be used to introduce both the concepts of array and iteration. Beyond all these advantages, evaluating conditional array formulas with the built-in debugging spreadsheet tool, the Formula Analyzer, the students would learn how to discover semantic mistakes and the need for such evaluation. We cannot forget that there are spreadsheet-specific advantages of conditional array formulas also: These formulas can substitute the built-in conditional spreadsheet functions. Using the conditional array formulas instead of the build-in conditional functions we do not have face and deal with the inconsistencies of the latter group of functions, their limited usage, and also their incompatibility between the different versions of spreadsheets. Finally, it is worth mentioning that conditional array formulas are compatible between the two most commonly used office applications: Microsoft Excel and OpenOffice Clac.

Introducing conditional array formulas
All these advantages of conditional array formulas in mind teachers have to find suitable spreadsheet tables to introduce this concept to their students and provide examples and opportunities to practice these formulas. Tables of different content downloadable from the Internet or provided by other subjects of the students will serve well in spreadsheet environment.
In my presentation I would like to give a detailed description of the method of introducing conditional array formulas, how to build them, how to expand them to the different requirements of the user. Beyond that I would like to present tables of different content yield to the very same array formulas, so both teachers and students have the opportunity to select tables to their interest and find solutions to problems using conditional array formulas. Testing students’ results using conditional formulas it was found that they are more convinced and build algorithms with more confidence using array formulas than using the traditional built-in conditional functions. These tests seem to prove that teaching the concept of array formulas from the very beginning of spreadsheet studies would result in better understanding of spreadsheet applications, especially conditional functions, than exclusively using the built-in functions.

Keywords:

Spreadsheet, array formulas, algorithm.