Sample and testing methods
Our goal was to test and measure the effectiveness of the Sprego methodology compared to traditional approaches for teaching spreadsheet management. The research was carried out according to the following process. Before the experiment took place, all the students filled in a pretest, to measure their prior knowledge (Section 2.2). The experiment covered teaching spreadsheeting in three classes of a local middle and high school, detailed in following paragraphs. After completing the educational process, the post-test was administered, where the tasks of the pre-test were repeated.
The experiment consisted of three classes in a local middle and high-school: two grade-7 classes and one grade-10 class. All the groups were assigned to the same teacher throughout the whole school year, which means that from our point of view the groups were randomly selected. The grade-10 group and one of the grade-7 groups studied the subject using the Sprego methodology (experimental groups). The other grade-7 class, on the other hand, was taught using the official textbook available for high-schools (grades-9 and 10) (Dancsó and Korom 2013), with a traditional interface and problem-specific function-focused approach (control group). The textbook was analyzed beforehand, and no cognitive limits were found which would cause difficulties for grade-7 students. However, the textbook ignores the background knowledge of the intended school groups and starts spreadsheeting (Siersted 2018) at the absolute beginner level, using basic spreadsheet functions as well as typing tables. It is important to note that the two grade-7 groups had no prior knowledge in the subject – which was proved in the pretest (Section 2.5) – while the grade-10 class had previously studied spreadsheet management for two years according to the frame curriculum (OFI 2012, 2013). They encountered the topic in their grade-7 studies (1 lesson/week for 8 lessons) and, most recently, last year in grade-9 (2 lessons/week for 12 lessons). Furthermore, several grade-10 students acquired ECDL (European Computer Driving License) certifications from the spreadsheet management module, which indicates that not only should they be familiar with the topic, but should also have a sound knowledge of it, which they can use confidently and upon which we can build during the classes.
Regarding the number of classes, the experimental and the control grade-7 groups studied the topic for 13 and 12 lessons, respectively, with 1 class a week. The experimental grade-10 group studied the subject for 13 lessons with 2 classes a week. Considering the numerous disadvantages of typing data in classes (Csernoch 2017), we deviated at an early stage from the official textbook in such a way that instead of getting students to type data as the book instructed, we provided them with the tables and moved on to the topic of spreadsheet functions.
In the experimental groups, the first and second phase of the problem-solving process – understanding the problem and devising a plan (Pólya 1954) – were carried out with the coaching method (Burton and Brown 1979; Chmielewska and Matuszak 2017; Creasy and Paterson 2006; Flaherty 2006; Fletcher and Mullen 2012; Knight 2009; Rogers 2012; Van Nieuwerburgh 2012). During this stage, a continuous interaction runs between teacher and students. In the third phase – carrying out the plan – all students worked individually at separate computers following the teacher’s guidance. In the fourth phase the discussion and debugging – looking back – took place, again in the form of coaching.
Considering the tasks, carried out in the classes, the control group progressed following the steps, the instructions, and the spreadsheet functions presented in the official textbook. The experimental groups solved problems according to the Sprego methodology, detailed in Section 1.2. In Sprego classes, as mentioned above, the problem-solving method of Pólya (1954) was followed, combined with the coaching interactive method and our invention of unplugged and semi-unplugged tools. The unplugged (3D printed doll sets) and semi-unplugged tools (authentic sources and the applications) were presented in the Sprego classes. The doll sets were used to guide the students through the algorithm construction progresses (as detailed in Section 1.3).
Considering the authentic sources, the following tables were used in classes during the experimental period:
Top 250 movies based on the ratings of IMDb (Internet Movie Database) (IMDb 2019),
The 100 tallest buildings in the world (Council on Tall Buildings and Urban Habitat 2019),
The countries of the world (OFI 2004).
The Sprego applications were applied to reiterating knowledge items primarily introduced in previous classes, to help students practicing algorithms. In all groups the teacher constantly followed the students’ work, checking and correcting their progression.
Although all the participating students completed the requirements of the curriculum, there are slight differences between the number of students participating in the pre- and post-tests, as presented in Table 2. These differences can be explained by absences and various unforeseen school activities, making students miss their classes at the time of the tests. The number of paired tests shows the number of students who were present on both test days, with the grade-7 experiment group being the best attended (N = 12), followed by the grade-10 experiment and grade-7 control groups (N = 11 and N = 11, respectively).
The composition of the tests
The tests were designed (1) to cover contents with the datamanagement and the programming aspects of spreadsheeting in mind and (2), to make students work with unfamiliar data using their analytical, computational-thinking and algorithmic skills and schemata they had built up during practice. To provide increasing levels of difficulty and a logical structure in which each task builds on the knowledge required to solve the previous one, we separate the test into two main sections:
Basic formula-management and execution-order tasks (Task 1–Task 4),
Formula creation and function usage tasks (Tasks 5a–g).
In Task 1 the students are required to finish an incomplete spreadsheet formula for calculating averages. With this task our aim is to collect data on fundamental syntactic knowledge of how formulas and functions are constructed in spreadsheets. This is followed by tasks handling arguments (Task 2) and references of vectors and matrixes (Task 3). The last question in this section aims to measure the students’ ability to recognize and analyze the execution order of a composite array formula. Students are asked to put the commands in a correct execution order (Task 4). We must note here that the tests contain more rows, as a distractor, than the number of commands in the formula. If students listed the arguments of the functions as commands, we considered it a less serious mistake than writing the commands in the wrong order.
The second section of the test focuses (Tasks 5a-5g) on solving problems – creating spreadsheet formulas – based on the table and tasks presented (Fig. 3). For this section we use an authentic table – converted from a webtable – containing the names, code, and prices of cakes from a local pastry shop (Mandula Família Kft 2017), which the students are familiar with (Fig. 3). In the first two tasks the names (Task 5a) and codes (Task 5b) have to be separated for each cake, because these two pieces of data are originally stored in one data field as seen in Fig. 3. The next task is to convert the prices – originally stored as a string (Fig. 3, column B) – to numbers (Task 5c). Task 5d focuses on forming logical conditions (asking yes/no questions) and then an IF() function with three arguments: the yes/no questions and two string constants for the output values.
For the next task, solving a conditional counting problem, the students have to compose either a multilevel array formula or a built-in problem-specific function (Task 5e), along with cell C2 as a variable (Fig. 3): count the number of cakes which are more expensive than the value stored in C2. The next two tasks focus on conditional calculation problems, to provide the sum and the average of prices based on a condition, where the prices are lower than the value stored in variable C2 (Tasks 5f and 5 g, respectively). Similar to Task 5e, either multilevel array formulas or problem-specific built-in functions can be used to solve the problems.
Similarly to any programming tasks, there were multiple solutions for several problems to which we set up the items separately. Solutions based on the values of Column B (Fig. 3) – Tasks 5d–5g – were accepted, regardless of whether students handled the data type conversion or not.
During the lessons, students solve problems using computers, applications provided by the school, and occasionally unplugged tools. The data collection of the tests was carried out using unplugged tools in all groups to see clearly the algorithm-building processes and abilities of students. With this method the trial-and-error solutions were avoided.
We must further note that the grade-10 experimental group learned and solved problems with the algorithm of linear search as well, but since it was not present in the lower grades’ curriculum, it was excluded from the current study.
Considering the nature of this study and our goal to measure the efficiency of the Sprego methodology, we posed the following hypotheses:
The Sprego method is more effective in teaching the spreadsheet-management topic than the traditional approaches.
The students’ prior knowledge in spreadsheet management taught with traditional methods affects negatively the effectiveness of Sprego.
Students who learned both with Sprego and traditional methods prefer to follow the “Sprego way” than using problem-specific functions to solve tasks that can be completed with either approach.
The traditional approach to teaching spreadsheet management does not develop long-lasting knowledge.
Results of the pre-tests
For our fourth hypothesis we compared the results of the pre-tests in the grade-10 and grade-7 groups, the latter involving both the experimental and control groups (Table 3). In order to reveal the differences between the novice and the experienced groups, we used Mann–Whitney tests. The results show that the grade-7 students completed the test with almost zero prior knowledge of the topic, because they had not encountered it in ICT education beforehand. It follows that in almost every task grade-10 students earned significantly higher scores, except in four tasks. Both groups completed Task 2 at a 25.00% average score with no significant difference (p = 0.5829). This result demonstrates that despite their prior knowledge, the grade-10 students could not recognize correct argument formats better than those students who had never worked with spreadsheet functions. A similar phenomenon can be observed working with string functions and data conversion (Tasks 5a–5c). While in these tasks grade-10 students earned higher scores than grade-7 students, the difference was not significant in any of these cases (p = 0.0794, p = 0.2301, respectively). Note, that for Task 5c both groups scored 0.00 points and therefore, their difference could not be calculated with the Mann–Whitney test. This can be traced back to the practice that string functions are not part of the traditional spreadsheet approach, regardless of their importance in programming.
Considering the overall pre-test, grade-7 groups earned a 5.52% average score, while the grade-10 students completed it with an 18.75% result, making the difference between the groups significant (p = 0.0000) (Fig. 4). However, it is important to keep in mind that the grade-10 students earned this significant difference with very low scores in the second part (Tasks 5a–5g: using spreadsheet functions) (below 8%) compared to the 0% of the grade-7 students. Furthermore, grade-10 students have more developed abstraction and cognitive skills and, additionally, several of them have ECDL certifications that they seemingly could not take advantage of in the test. All these things considered, the assumed abilities of grade-10 students would imply much higher results when doing tasks using spreadsheet functions. Therefore, we cannot say safely that the traditional approaches develop long-lasting knowledge regardless of the statistically proven significant differences. In this light, we conclude that this last hypothesis is statistically rejected but consider it proved nonetheless, based on the details discussed above.
Results of the grade-7 groups
Considering our first hypothesis, which compares the results of the post-tests of grade-7 experimental and control groups, we analyzed each task of the tests separately to get an overview of the effectiveness of the Sprego method in the spreadsheet management topic, as well as in various subfields of it (Table 4). We calculated the average results of the students for each task and for the whole test and used Mann–Whitney tests to analyze the significance between the scores of the two grade-7 groups.
The data in Table 4 show that students who learned with the Sprego methodology completed the tasks more successfully in all but one task – Task 5d –, which focused on composing an IF() function with constant outputs. A similar tendency was revealed during our previous experiments, and we must draw attention to this phenomenon. Students learning with the traditional approach tend to achieve better results for the tasks focusing only on constant IF() functions. However, these students usually stop there, do not reach higher levels of abstraction and have a hard time completing tasks that require variable values and more complex algorithms. This previously observed pattern (Csernoch et al. 2015) was confirmed by our statistical analyses in Tasks 5d–5g (Table 4).
The experimental group achieved significantly better results than the control group in the following tasks:
syntax of formulas (Task 1, p = 0.0175),
correct argument formats (Task 2, p = 0.0082),
returning text with a constant number of characters (Task 5a, p = 0.0001),
returning text with a varying number of characters (Task 5b, p = 0.0002),
converting numbers stored as text to numbers (Task 5c, p = 0.0000),
a conditional counting problem (Task 5e, p = 0.0046),
a conditional summing problem (Task 5f, p = 0.0161),
a conditional averaging problem (Task 5g, p = 0.0041).
Although the experimental group completed Tasks 3 and 4 more successfully, the results showed no significant differences in either case (p = 0.5505 and p = 0.5009, respectively). Furthermore, while the control group achieved better scores in Task 5d the difference was not significant between the two groups (p = 0.3941).
Considering the results of the whole post-test, the experimental group completed the tasks more successfully (65.42%) than the control group (38.00%) with a significant difference (p = 0.0013) (Fig. 4). We can conclude, based on the data of the post-tests of the grade-7 experimental and control groups, that our first hypothesis proved that grade-7 students who learned with Sprego complete the post-tests more successfully than the control group.
Results of grade-7 and 10 experimental groups
The second hypothesis focuses on one of the differences between the experiment groups (grade-7 and grade-10 experimental groups): how previous studies influence progress in Sprego. An extract of the relevant data is presented in Table 5. In accordance with Champagne et al. (1983), we found that previous native and/or shallow knowledge hinders the acquisition of high-mathability problem-solving skills. Grade-10 students, despite their various advantages – age, experience, previous classes, etc. –, could not achieve higher scores than the grade-7 experimental group.
In 8 out of 11 tasks the grade-7 group performed better; however, the difference between the two groups is not significant. The grade-10 students only achieved better results in three tasks: returning text with a constant number of characters (Task 5a), using the IF() function with constant outputs (Task 5d), and the conditional averaging task (Task 5g). Similarly to the tasks where grade-7 students performed better, the differences between the two experimental groups were not significant in any of these three tasks. Also note that we accepted several possible solutions for tasks that can be solved either with Sprego or traditional approaches, and for the grade-10 group, the current results encompass not only Sprego answers, but also those achieved with other methods.
While the numbers in Table 5 clearly show no significant difference between the results of the two Sprego groups, it is worth taking into consideration the fact that the grade-10 students had studied the topic beforehand twice, in grades 7 and 9, and, as mentioned before, by their age they have more developed abstraction and cognitive abilities. Filling in the same tests resulted in minimal differences between the two groups. At this point, going beyond how previous surface approaches affected the problem-solving abilities of grade-10 students, we must draw attention to the results, which prove that surface approach methods do not support the development of sequencing, spatial, and mnemonic abilities (Kruck et al. 2003). If grade-10 students have higher abstraction and problem-solving abilities, this is more likely to be acquired in other classes, rather than classes focusing on spreadsheets.
Considering that the average score of the grade-10 experimental group (64.34%) is not higher than that of the grade-7 experimental group (65.42%) – although the difference is not significant (p = 0.7388) – the second hypothesis is proved (Fig. 4). In other words, the grade-10 experimental group does not achieve significantly better results in the post-tests than the grade-7 experimental group.
Selection of methods
For our third hypothesis we analyzed the selection of methods for solving the problems. In the grade-10 post-test we also wanted to see how students, who studied with both traditional and Sprego methodologies, build and choose items from their function base. We assumed that students would be influenced by the “Sprego way”, but we were afraid, given previous results, that their prior studies would have a negative effect on their development (Champagne et al. 1983; Kruck et al. 2003; Moyo et al. 2016). For this part of our analysis, only Tasks 5e–5g (Table 6) were relevant because these are the ones that show stronger differences in their solutions between the two approaches. The numbers of students dealing with the problems are detailed in Table 7, separated by their groups and selected methods. As students could not possess knowledge of the Sprego methodology prior to their Sprego classes, we counted empty submissions as part of the traditional approach.
Table 7 shows that based on the methodology applied, with minimal deviation, both grade-7 groups tend to follow one specific path, i.e. the method, which was introduced in their class. In contrast, we can observe stronger differences regarding the grade-10 post-test.
While almost all students in the grade-10 experimental group used problem-specific functions to solve the tasks of the pre-test, a high number of them switched to Sprego solutions in the post-tests (Tables 6 and 7). The conditional counting task (Task 5e) shows the smallest difference (only 3 more students followed the Sprego method than the traditional) which can be accounted for by the widespread use of the COUNTIF() function that students learn at an early stage of their ICT studies and revisit almost every time the topic is encountered. However, the conditional sum and average tasks (Tasks 5f and 5g) were solved by applying the algorithm they share and coded with the general-purpose Sprego functions, with only a few exceptions (2 and 3 students, respectively).
The tendency towards switching to Sprego is obvious based on the comparison of the data in Table 7. To analyze significant differences between the number of students who used different approaches in the grade-10 post-tests, we used binomial tests. In the tasks involving conditional counting (Task 5e) and averaging (Task 5g) the students showed no significant differences in their selection of methods (p = 0.5811 and p = 0.0923, respectively). However, in Task 5f, which requires the same algorithm as Tasks 5e and 5g, the difference was significant (p = 0.0225).
The comparison of the selected problem-solving methods of grade-7 and 10 experimental groups clearly shows that the students’ prior knowledge influences their decisions and approaches in the selection of functions. This is also confirmed by our experiences while correcting the tests, as we encountered several cases where grade-10 students tried to use either both methods (unsuccessfully) or approached the tasks using problem-specific functions which they did not remember how to use correctly. We also encountered the IF() function used with the logic and arguments of COUNTIF(), or SUM() and AVERAGE() functions with the arguments of SUMIF() and AVERAGEIF().
In Table 8 we present the number of students with their selection of methods without the empty submissions being counted as the traditional approach. We can observe considerable drops in the number of students in the traditional categories compared to the data in Table 7, except for the grade-7 control group’s post-test.
The grade-10 experimental group’s pre-test data further strengthens our fourth hypothesis discussed in Section 2.5, because it shows that most of the students (81%, 81% and 75% of the groups, respectively) who studied beforehand with traditional approaches could not even begin to solve these tasks. Considering the grade-10 experimental group’s post-test data, the students follow the pattern observed before, given that they tend to choose to work with Sprego. Using binomial tests, the significance differs slightly from our previous analyses: p = 0.2266, p = 0.0064 and p = 0.0117 for Tasks 5e, 5f and 5g, respectively. This leads us to the conclusion that if we disregard the empty submissions, the students choose Sprego with a significant difference in 2 out of 3 tasks.
The choice of using the Sprego approach is important in cross-topic learning and knowledge-transfer, as well. The higher number of students who used the Sprego approach follows on the logical, sequencing, spatial, and function-structuring abilities, because the algorithms the students learn and work with are the same in programming practices and build on the logic of programming (Table 6). As a higher number of grade-10 students chose to work with Sprego in the post-tests, but fewer than those in the grade-7 experimental group, our third hypothesis is proved, in accordance with previously published results. However, the tendencies are clear; these results prove that traditional surface approach methods and native knowledge have a negative effect on the students’ problem-solving skills.