Employing Microsoft Excel for Enhanced Mathematical and Statistical Online Pedagogy in Economics Amidst a Pandemic
Abstract:
During the COVID-19 pandemic, a profound impact was experienced in various domains, including education. The abrupt shift to remote learning presented challenges, especially in subjects necessitating practical problem-solving, commonly executed on traditional boards. It was observed that students at the College of Economics particularly grappled with Mathematics and Statistics during online sessions. This study aims to demonstrate the effectiveness of MS Excel in facilitating online, application-driven learning in Mathematics and Statistics. Initially, emphasis is placed on articulating basic mathematical expressions, ensuring full visibility of formulae for students. Subsequently, an illustrative approach is adopted to elucidate the resolution of intricate systems characterized by multiple equations and variables. This framework provides students with a foundation, aiding in the application of the acquired knowledge. Additionally, the versatility of MS Excel is highlighted by detailing its potential in deploying various statistical functions. For contextual relevance, data concerning human mobility during the pandemic, disseminated by Google for 135 nations globally, is employed. This research not only bridges the pedagogical gap but also offers a resilient teaching tool in uncertain times.1. Introduction
In many university-level economics programs, Mathematics and Statistics are often identified as challenging disciplines. One potential explanation, particularly in specific regions, could be that high-achieving mathematics students from high schools predominantly opt for more technical colleges. Consequently, a larger proportion of students in economics programs might require additional time to comprehend mathematical and statistical content compared to other subjects. The onset of the COVID-19 pandemic markedly disrupted numerous facets of individual and professional spheres. Decisions made by governments globally to impose restrictions or prohibitions on outdoor movement notably impinged upon the education sector. Transitioning to online platforms became a mandate for educational entities ranging from primary to tertiary levels. While theoretical subjects seamlessly adapted to this paradigm shift, disciplines demanding practical problem-solving engagements posed greater challenges. Historically, the acquisition of mathematical and statistical knowledge has been intrinsically linked to the resolution of practical problems. In conventional classroom settings, problems are methodically elucidated on blackboards.
However, amidst the pandemic, a myriad of alternative online teaching methodologies emerged (Friesen et al., 2003; Hollebrands & Lee, 2020; Moreno-Guerrero et al., 2020). Some of these methods encompassed:
• Capture via web or mobile cameras, wherein educators resolved problems on classroom blackboards, subsequently broadcasting through platforms like Google Classroom or Zoom.
• Documentation of problem-solving on paper, followed by dissemination via platforms mentioned above.
• Production of lessons using professional cameras for telecast on dedicated channels.
• Photographing completed tasks and disseminating those images directly to students via email, Viber, or WhatsApp.
• Drafting formulas in Word documents and subsequently distributing them to students.
• Drafting formulas in Excel documents for student access.
Such diversity in pedagogical approaches, it is surmised, may have inadvertently impacted the quality of knowledge acquisition, as reflected in examination outcomes. One of the contributing factors could be the inability of students to independently grasp certain mathematical and statistical concepts while confined to home environments. The focus of this research pivots on elucidating the utility of MS Excel in bolstering practical online teaching for Mathematics and Statistics. The initial sections will delve into articulating basic mathematical expressions, ensuring formulae are transparently accessible to students. Moreover, the versatility of statistical functions will be showcased using illustrative data on human mobility during the pandemic, as released by Google.
2. Related Works
Over the past two years, an influx of professional and scientific literature addressing the ramifications of the COVID-19 pandemic on daily life and occupational engagements has been observed. Of particular interest are studies delineating the pandemic's implications on educational methodologies. While both educators and students grappled with substantial pedagogical transformations, it is posited that educators confronted more profound operational shifts. Assessments of the positive and negative repercussions ensuing from governmental lockdown measures are currently a focal point for many researchers. Even as the pandemic's immediacy appears to have diminished, remote working and online educational practices persist.
A comparison of diverse pedagogical strategies for Mathematics and Statistics during the pandemic era was undertaken in this research. Within this literature review, select studies concerning the topic are briefly encapsulated, with particular emphasis on the integration of Excel in Mathematics and Statistics instruction.
Giles & Ganesh (2002) noted that a considerable segment of students displayed mathematical apprehensions, particularly when confronted with calculus. Given Excel's ubiquity and computational prowess, calculus-based business mathematics was taught in computer laboratories. It was reported that student experiences underscored Excel's efficacy in simplifying business mathematics comprehension and in mitigating errors when applying mathematical formulae to quantitative business issues (Giles & Ganesh, 2002).
In their work, Duller (2008) from Austria illuminated practical examples employing Excel for basic statistical methodologies, accentuating data set descriptions. It was revealed that while frequency distributions often initiate data set descriptions, their derivation in Excel proved more seamless than conventional methods. Emphasis was also laid on the pertinence of graphical data representation (Duller, 2008).
Liang & Martin (2008) postulated that Excel facilitates calculus-based business mathematics comprehension, especially when conveyed in computer laboratories. By integrating heuristic examples with practical numerical challenges, their research indicated that Excel could enhance understanding and application of intricate calculus-based mathematical principles in quantitative business contexts (Liang & Martin, 2008).
Das (2019) deliberated on the role of communication technology tools in Mathematics instruction. It was argued that the amalgamation of knowledge and communication technologies in Mathematics education at both Teacher-Training College and School levels is pivotal (Das, 2019).
The heightened adoption of internet-based learning paradigms, particularly over recent years, was examined by Engelbrecht et al. (2020). They identified three domains where educators innovated pedagogical approaches: design principles for novel settings; social interaction facilitating knowledge construction; and instrumental resources. The transformative potential of digital technologies on mathematical cognition was also emphasized.
During the pandemic, educators faced the task of recalibrating instructional methodologies (Engelbrecht et al., 2020). Fujita (2021) enumerated challenges linked to online mathematics instruction. His international study explored the intricacies of designing online pedagogical landscapes, emphasizing environments, social dynamics, and resources (Fujita, 2021).
Lastly, Tran & Nguyen (2021) presented survey findings on the adoption of digital platforms and software in high school settings during the pandemic. Their research illuminated the burgeoning importance of information and communication technologies, offering a renewed lens on digital tools in Mathematics instruction (Tran & Nguyen, 2021).
3. Methodology
This study seeks to elucidate the potential of Excel in aiding instructors and students during the delivery of Mathematics and Statistics curricula within economic institutions. Notably, during the COVID-19 pandemic, where a significant shift to online teaching was necessitated across various nations, the importance of tools such as Excel was highlighted. Examination outcomes from online sessions were scrutinized, revealing that student performance was less optimal in comparison to results from in-person teaching sessions. Such an observation was consistent across subjects incorporating practical components within economic educational establishments. A comprehensive outline of the adopted research methodology is depicted in Figure 1.
4. Results
In this segment of the investigation, practical instances are presented, illustrating the utility of Excel as a supportive tool during online instruction in Mathematics and Statistics. While these examples are tailored for students within economic academic institutions, it is posited that certain examples might be adaptable for instruction at secondary and elementary educational levels.
The indispensable role of the Excel application in today's economic professional landscape is emphasized. Beyond economists, a myriad of professions incorporate Excel into their daily tasks. This segment elucidates the efficacy of Excel in facilitating online Mathematics instruction. Within Excel, worksheets are constituted, enabling efficient presentation and manipulation of both numerical and textual data. Basic mathematical operations such as addition (+), subtraction (-), multiplication (*), and division (/), alongside logical operations like greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=), and equality (=), are readily inputted and showcased in Excel (Frye, 2013; Ragupathi, 2015).
Mathematical expressions discussed in academic settings are seamlessly integrated as formulas within Excel. It has been noted that the utilization of cell references (e.g., A1, B12, D29) rather than direct numerical input is advocated. Such practices lend a dynamic nature to the data, implying that alterations to one cell instigate automatic adjustments in all cells wherein that particular data is incorporated into formulas or functions.
A | B | C | |
1 | X | Y | Z |
2 | 12 | 3 | 2 |
3 | Sum | 13 | 13 |
4 | Product | 5 | 5 |
5 | Brackets | 4 | 4 |
Illustratively, Table 1 exhibits basic mathematical computations, while Table 2 delineates the associated formulas. This example serves as an illustrative tool, acquainting students with the prioritization of operations, underscoring that parentheses supersede all mathematical operators. It is further posited that in instances of ambiguity regarding operational priority, the incorporation of parentheses is recommended. For instructional purposes, Excel's "Show Formulas" command, nestled within the "Formula Auditing" submenu, proves invaluable. Activation of this command unfurls all extant formulas on the worksheet in a manner reminiscent of Table 2.
A | B | C | |
1 | X | Y | Z |
2 | 12 | 3 | 2 |
3 | Sum | =A2+B2-C2 | =12+3-2 |
4 | Product | =A2/B2*C2-B2 | =12/3*2-3 |
5 | Brackets | =A2/(B2*C2-B2) | =12/(3*2-3) |
The utility of the "Trace Precedents" command is showcased in Figure 2, where arrows direct attention to all cells integrated within a formula for a chosen cell. Conversely, the "Trace Dependents" command, demonstrated in Figure 3 with reference to cell A2, directs arrows to all worksheet cells that employ the selected cell in their formulas.
Further instructional tools encompass the "Data Validation" command found within the "Data Tools" submenu. This command institutes varied constraints, enabling students to preclude incorrect data entry. Subsequent verification of data accuracy is feasible via the "Circle invalid data" command, positioned beneath "Data Validation". Incorrect data entries are transiently ensconced within a red ellipse, as exemplified in Figure 4, prompting corrective action. Table 3 provides an illustrative example where exam scores for students are subject to automatic rounding.
It may be worth expanding on the educational implications of these tools, detailing how they can aid educators in creating more effective teaching modules, and how students can leverage these tools for better understanding of complex mathematical concepts.
In data entry and analysis, sorting and filtering data are recognized as pivotal processes. Demonstrations using Excel have elucidated that lists containing first and last names achieve enhanced clarity when alphabetically organized. Conversely, inventories detailing product stock quantities become more comprehensible when numerically arranged from the highest to the lowest value. Augmented visual clarity is achieved when data cells are systematically categorized by diverse colors or symbols. Such data organization techniques not only facilitate rapid discernment and differentiation but also streamline the processes of data retrieval and decision-making.
A | B | C | D |
Product | Price | Tax E 17% | Tax R 17% |
A | € 100.5 | € 17.094 | € 17.09 |
B | € 200.99 | € 34.168 | € 34.17 |
C | € 300.68 | € 51.116 | € 51.12 |
D | € 400.57 | € 68.097 | € 68.10 |
E | € 1002.79 | € 170.47 | € 170.48 |
A prevailing issue in the realm of economics pertains to the rounding of real numbers, primarily due to the standard practice of expressing product prices up to two decimal places. As depicted in Table 3, discrepancies in rounding can engender errors in financial reports. In Column C, taxes, at a rate of 17% on four products, were computed in Excel and subsequently rounded to three decimal places, inclusive of the tax on the aggregate product sum. Contrastingly, in Column D, similar tax calculations were rounded to two decimal places. This led to a discrepancy of $0.01 in the cumulative tax amount, a deviation deemed impermissible in financial statements. Given that economists typically engage with a vast array of products, the magnitude of such rounding discrepancies can amplify, undermining the accuracy of financial reports. Illustrating this nuance to students becomes substantially more feasible with Excel as an instructional tool.
Formula | Result |
Round (23.000; 2) | 23.00 |
Round (23.111; 2) | 23.11 |
Round (23.119; 2) | 23.12 |
Round (23.115; 2) | 23.12 |
Round (23.125; 2) | 23.12 |
Round (23.12500001; 2) | 23.13 |
Round (-23.111; 2) | -23.11 |
Round (-23.119; 2) | -23.12 |
Round (-23.115; 2) | -23.12 |
Round (-23.115; 2) | -23.12 |
Table 4 provides exemplifications of rounding real numbers to a precision of two decimal places. A recurrent pitfall observed among students arises when rounding numbers concluding with the digit 5; often, the standard convention of rounding to the nearest even number is overlooked. In such scenarios, Excel emerges as an invaluable resource for elucidation and correction (Damjanović et al., 2020).
Apple [kg] | Pear [kg] | Banana [kg] | Paid [€] | |
1 Customer | 3.4 | 1.2 | 2.1 | 10.16 |
2 Customer | 2.1 | 0.8 | 1.5 | 6.77 |
3 Customer | 1.2 | 2.3 | 3.4 | 12.5 |
During interactions with economics students, challenges in percentage calculations were frequently observed. To address this, a practical exercise, depicted in Table 6, was designed, entailing three successive price reductions in a retail scenario. The initial price tag on a dress was set at 300€. The initial month saw a 10% reduction, followed by an additional 20% reduction the subsequent month. In the third month, a further 30% reduction was applied to the already discounted price. The task is to compute the final cost of the dress, the cumulative discount amounts, the successive post-discounted prices, and the overall discount percentage applied to the original price. It should be noted that the total discount does not simply equate to the summation of individual discounts, i.e., 10%+20%+30%≠60%.
E | F | G | |
2 | Solution | ||
3 | Product Price | € 300.00 | |
4 | % Discounts 1 | 10.00% | |
5 | Reduction 1 | € 30.00 | =F3*F4 |
6 | New price 1 | € 270.00 | =F3-F5 |
7 | % Discounts 2 | 20.00% | |
8 | Discount 2 | € 54.00 | =F6*F7 |
9 | New price 2 | € 216.00 | =F6-F8 |
10 | % Discounts 3 | 30.00% | |
11 | Reduction 3 | € 64.80 | =F9*F10 |
12 | New Price 3 | € 151.20 | =F9-F11 |
13 | % of Total Discount | 49.60% | =(F3-F12)/F3 |
14 | or | ||
15 | New price 3 | € 151.20 | =F3*(1-F4)*(1-F7)*(1-F10) |
In another instance, represented in Table 7, a problem centered on the proportional relationships between three variables is presented. This pertains to a jam recipe, characterized by a specific fruit ratio of plums, apples, and pears, given as 5:2:1 respectively. For the production of 4 kg of jam (assuming zero water loss during cooking), the objective is to ascertain the requisite quantity of each fruit.
F | G | H | I | |
Solution | ||||
3 | Ratio | Production[kg] | ||
4 | Plum | 5 | 2.5 | =H7*G4/G7 |
5 | Apple | 2 | 1 | =H7*G5/G7 |
6 | Pear | 1 | 0.5 | =H7*G6/G7 |
7 | Total | 8 | 4 |
Initial analysis necessitates the aggregation of individual fruit proportions in the recipe, amounting to 8 (5+2+1). In a resultant 4 kg of jam, plums account for 5/8, apples for 2/8, and pears for 1/8. The merits of utilizing Excel for this mathematical resolution become evident when the input data is integrated; the solution is promptly derived upon formula insertion. Conversely, when executed on a traditional blackboard, each modification to the input mandates an equivalent time for resolution.
Matrix operations in Excel are posited to be exceptionally beneficial, particularly during online mathematical instruction. For matrices with dimensions up to 3×3 and containing simple integers, manual computation of determinants, inverses, and matrix arithmetic is feasible. Nonetheless, for matrices with expanded dimensions and real number elements, manual evaluations become substantially time-intensive. Therefore, the adoption of Excel for manipulating matrices of varying dimensions is advantageous for educators and learners alike.
Various mathematical techniques exist for resolving systems of equations encompassing multiple unknowns. Typically, introductory examples provided to students are characterized by basic prime integers, leading to integer-based solutions. Contrarily, real-world scenarios often introduce extensive and complex numbers, complicating manual solutions. An illustration is presented here, demonstrating how Excel facilitates the resolution of a system comprising three equations with three unknowns. Adopting a similar approach, systems with diverse unknown quantities can also be tackled. This methodology presents a significant benefit; upon setting it up in Excel, diverse equation systems can be addressed by merely inputting the requisite coefficients and variables, culminating in an immediate solution.
A practical scenario is depicted in Table 5, where three distinct customers purchase varying quantities of the same fruit and incur different bill amounts. The challenge lies in determining the cost per kg for each fruit type. The problem entails resolving a system characterized by three equations with the variables being X (apple), Y (pear), and Z (banana).
3.4*X +1.2*Y+2.1*Z=10.16
2.1*X+0.8*Y+1.5*Z=6.77
1.2*X+2.3*Y+3.4*Z=12.5
To solve the problem presented in Table 8, we will utilize the matrix methodology for resolving a system of equations. Matrix A, spanning cells B3 to D5, encapsulates the coefficients associated with variables X, Y, and Z, representative of our three-equation system. Simultaneously, Matrix B, from cells F3 to F5, corresponds to the independent coefficients positioned to the right of the equal sign in our system.
B | C | D | E | F | G | H | I | J | |
2 | A= | B= | Inverse Matrix A (A-1) | ||||||
3 | 3.4 | 1.2 | 2.1 | 10.16 | 0.9567 | -0.983 | -0.1573 | ||
4 | 2.1 | 0.8 | 1.5 | 6.77 | 6.9987 | -11.848 | 0.90433 | ||
5 | 1.2 | 2.3 | 3.4 | 12.5 | -5.072 | 8.36173 | -0.2621 | ||
6 | Multiplication Matrix | Multiplication Matrix | |||||||
7 | C=A-1*B | Control A*C | Price | ||||||
8 | 1.1 | 10.16 | Apple | 1.1 | €/kg | ||||
9 | 2.2 | 6.77 | Pear | 2.2 | €/kg | ||||
10 | 1.8 | 12.5 | Banana | 1.8 | €/kg |
The inverse matrix A (A-1) can be found within cells H3 to J5, which is derived using the formula {=MINVERSE (B3: D5)}. It's crucial to understand that these curly brackets {=...} are not to be manually input. Instead, they emerge through the following process:
(1) Cells H3 to J5, where we aim to ascertain the inverse matrix, must first be selected.
(2) We then activate the mathematical function MINVERSE, designating cells B3 to D5 (representing matrix A) as its argument array.
(3) Instead of a simple 'OK' command, a simultaneous press of the Ctrl, Shift, and Enter keys is essential, leading to an automatic result generation in cells H3 to J5.
The system's solution, comprising three equations with three unknowns, is most effortlessly obtained by multiplying the inverse matrix A (A-1) and Matrix B within cells B8 to B10. This is achieved through the formula {=MMULT (H3: J5; F3: F5)}. A verification of this solution is accomplished by multiplying Matrix A and the resultant Matrix C within cells F8 to F10, using the formula {=MMULT (B3: D5; B8: B10)}.
Excel emerges as an invaluable ally for educators and students navigating the waters of trigonometry. All trigonometric functions are readily available, accommodating angles both in radians and degrees. Often, students find it more intuitive to conceptualize angles in degrees, and manual conversions into radians can be time-consuming. While many resort to handheld calculators for computing trigonometric values, our recommendation leans towards Excel. This preference stems from its enhanced speed and simplicity, particularly when multiple calculations are at play.
Furthermore, Excel extends beyond mere computation. It allows for the graphical representation of these trigonometric functions, producing visuals far superior to traditional blackboard illustrations. As an instance, Figure 5 showcases the graph of a function Y=(2*sin(X))/(3*X+1) with X varying from 0 to 6.28. On the other hand, Figure 6 depicts the same function, but with X spanning from 0 to 12.56. For illustrating Y=f(X) functions with a singular variable, the XY Scatter chart in Excel is the recommended choice.
Modern economists' daily activities frequently necessitate various analyses and reports utilizing an array of statistical functions and analytical procedures. While the market offers a plethora of statistical software tools, popular academic instruction often defaults to IBM's SPSS software for teaching purposes. However, it has been suggested that Excel, with its robust statistical capabilities, offers notable advantages for pedagogical integration. One primary rationale is the prevalence of pre-existing data in Excel formats, which facilitates seamless statistical processing. Furthermore, Excel encompasses the vast majority of statistical functions employed by the standard economist. Notably, a few intricate financial analyses unfeasible via Excel are typically reserved for specialized reports and are beyond the purview of most economists.
Within Excel, an extensive suite of functions falls under the 'Statistical functions' category. Each function is accompanied by concise annotations detailing its purpose, augmented by a multitude of practical examples for enhanced understanding. These functions predominantly cater to various statistical analyses, typically executed on a combination of numerical and logical data.
In traditional classrooms, an inherent challenge for instructors of Statistics is the selection of diverse, representative datasets for demonstration. The constraints of time often deter the usage of expansive datasets, given the time required for manual data transcription on blackboards. In contrast, the digitization of online classes allows for datasets to be disseminated in advance, typically in Excel formats. Such a transition seeks to maximize the time allocated for data analysis.
Recently, a novel dataset, focusing on human mobility patterns post the inception of the COVID-19 pandemic, was introduced into the statistical curriculum. In 2020, Google initiated the publication of the "Community Mobility Reports" (CMR), providing daily updates on global mobility trends. Spanning 135 countries, this data repository, commencing on February 15, 2020, offers insights into human movement patterns. In addition to foundational data, specific temporal snapshots also feature tailored statistical reports for individual countries. This data was procured by tracking individuals who activated the 'location history' feature on their mobile applications across various handheld devices. The collected data delineates each user's physical presence across diverse locales, also detailing the duration of each visit. For every country, the data categorizes the locations into six discrete categories: retail and recreation venues, pharmacies and grocery stores, parks, transit stations, workplaces, and residential structures.
The CMR contains the percentage change in the movement of people for each of the six locations, which is derived by comparing it with the data obtained for that location before the outbreak of the COVID-19 pandemic (a five-week period running from January 3, 2020 to February 6, 2020). A comparison was made of the movement of people for each day of the week (Monday with Monday, Tuesday with Tuesday, and so on for each day of the week). By showing only the percentage change in attendance at each location, the possibility of drawing a conclusion about how many people were at a certain location in one day was prevented, thus preserving the privacy of user’s data for each person. For most countries, Google provides data for the entire country and regions in that country, but also a large number of cities, which depends on the size of the country and the number of inhabitants. We saw that the students were very interested in statistically analyzing the data for their country, especially the regions and cities they came from. Based on the statistical analysis, the students were also interested in trying to explain the differences between the obtained results (Damjanović et al., 2022).
Figure 7 shows the graph of people's mobility in Italy, and Figure 8 shows the graph of people's mobility in Sweden in 2020, 2021 and 2022. The X- axis of the graph shows the months from February 2020 to February 2022. The Y-axis of the graph shows the percentage change mobility of people for just four locations. In order to get uncluttered graphics, the mobility of people for the locations retail and recreation as well as pharmacies and grocery stores was omitted, because the obtained curves for these two categories are less interesting than the four locations shown. When drawing the graphs, the data for the weekend days were omitted because these data introduced a lot of noise. When working with students, we suggest the processing of statistics for all six locations.
The Community Mobility Reports (CMR) detail the percentage change in human movement across each of the six aforementioned locations. These changes are quantified by contrasting them with baseline data acquired from January 3, 2020, to February 6, 2020—a five-week span prior to the acknowledged onset of the COVID-19 pandemic. The percentage variations were discerned by matching movement data for each day of the week, thereby facilitating a like-for-like comparison. By exclusively presenting percentage changes rather than absolute figures, user privacy was maintained, preventing potential extrapolation of individual visitations to these locations.
For the majority of countries, the CMR not only furnishes national data but also offers granular insights into regional and city-level movements. Such granularity is contingent upon the country's geographic expanse and population density. A marked interest was observed among students in analyzing data pertinent to their native countries, particularly regions and cities of their residence. The resultant statistical analysis further piqued curiosity, as students endeavored to decipher the reasons behind discrepancies in findings.
Figure 7 and Figure 8 illustrate the mobility trends in Portugal and Sweden, respectively, spanning 2020 to 2022. The X-axis chronicles the period from February 2020 to February 2022, while the Y-axis delineates the percentage change in mobility for four specific locations. In the quest for clarity, data from retail and recreation, as well as pharmacies and grocery stores, were excluded due to the relative lack of intrigue these curves introduced compared to the displayed locations. Moreover, weekend data was disregarded owing to its propensity to introduce statistical noise, although comprehensive analyses encompass all six locations.
Upon juxtaposing the mobility patterns of Italy and Sweden, the most salient disparity emerged within the 'parks' category. Following the COVID-19 outbreak in March 2020, park visitations in Sweden witnessed an upsurge, contrary to the pronounced decline observed in Portugal—and analogously in other European nations. This deviation can be attributed to the Swedish government's decision to abstain from imposing residential restrictions post the pandemic outbreak, a strategy deviating from the broader European response. Another noteworthy observation pertains to the inverse relationship between workplace and residential attendance. An ascent in residential stays correlated with a decline in workplace attendances. Seasonal variations, especially heightened park visitations during summer, predominantly correlate with the holiday season, particularly evident in coastal cities inundated with non-residential tourists.
Students were first acquainted with foundational statistical functions: the mean, maximum, and minimum values across each location. Given the voluminous dataset, subsequent analyses could be compartmentalized by temporal segments—annually, quarterly, or monthly. Upon procuring results, students were prompted to elucidate disparities in data between countries or even between regions within a singular country.
Attention was subsequently drawn to the standard deviation. It was underscored that Excel differentiates between the calculation for sample standard deviation, denoted by STDEV.S (σ=SQRT( ∑ (Xi -SV)² / (n - 1) )), and population standard deviation, represented by STDEV.P (σ=SQRT( ∑ (Xi -SV)² / n )). Although these functions yield divergent results with limited data, congruence is typically observed as the dataset amplifies.
Correlation serves as an indicator of potential causal links between observed phenomena. The correlation coefficient can be transformed into a percentage by multiplying the value obtained by 100. Its range is demarcated by positive values, which represent a positive interdependence between data sets, and negative values, which suggest a negative interdependence. The strength of the correlation can be categorized as follows:
• Values ranging from 0 to 0.25 (or -0.25 to 0) denote a weak degree of connection between phenomena, typically overlooked in analyses.
• Values between 0.25 to 0.50 (or -0.50 to -0.25) signal a moderate degree of connection, warranting attention.
• Values between 0.50 to 0.75 (or -0.75 to -0.50) signify a significant degree of connection.
• Values from 0.75 to 1 (or -1 to -0.75) indicate a strong, nearly functional connection between the phenomena under study.
In Excel, the CORREL function, which yields a correlation coefficient between -1 and 1 for two data sets, is defined as follows:
=CORREL(array1, array2)
This function employs the formula:
where,
• Xi and Yi represent the i-th elements of the first and second sequences of n numbers, respectively, being analyzed for correlation.
• SVx and SVy are the mean values of the respective sequences.
During the observed period, particularly in the initial three months post the 2020 COVID-19 outbreak, students were guided to calculate the correlation between changes in workplace attendance and the other five locations. High correlation degrees were noted between workplace locations, home stays, and public transport usage. Conversely, other locations typically demonstrated a weaker correlation.
Statistical tests such as the Student's t-test and the Chi-square test can be applied to the mobility data. The Student's t-test, predominantly utilized for hypothesis testing, assesses the significance of differences between two data set means. Required parameters for this test include sample size (n), degrees of freedom (n-1), standard deviation (SD), and the mean.
The Chi-square test (χ^{2}) evaluates discrepancies between observed frequencies (f_{d}) and expected (theoretical) frequencies (f_{t}), determining the probability of association between two variables. Only frequency data is utilized, excluding measurement units.
Excel's Data Analysis feature, under the Descriptive Statistic option, can execute several simultaneous statistical analyses, producing results such as Mean, Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, and Count.
In the Gaussian distribution (Normal PDF) depicted in Figure 9, the mean value of the input data is represented by the label "$\bar{x}$", while its standard deviation is denoted by "s". Within this distribution, the proportion of total data residing within specific intervals can be characterized as:
• Within the interval $\bar{x}$±1σ, 68.26% of the data is encompassed.
• Within the interval $\bar{x}$±2σ, 95.46% of the data is captured.
• Within the interval $\bar{x}$±3σ, a notable 99.72% of the data is included.
B | C | D | E | F | G | H | I | |
2 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | |
3 | Distribution Probability (%) | 50.0% | 15.9% | 2.3% | 0.1% | 84.1% | 97.7% | 99.9% |
4 | Measured Value | 100 | 90 | 80 | 70 | 110 | 120 | 130 |
5 | Mean Value | 100 | 100 | 100 | 100 | 100 | 100 | 100 |
6 | Standard Deviation | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
7 | =NORM.DIST(C4;C5;C6;TURE) | 0.5 | 0.159 | 0.023 | 0.001 | 0.841 | 0.977 | 0.9987 |
8 | =NORM.DIST(C3;C4;C5;FALSE) | 0.039 | 0.024 | 0.005 | 4E-04 | 0.024 | 0.005 | 0.0004 |
9 | =NORM.INV(C3;C5;C6) | 100 | 90.01 | 80.05 | 69.1 | 110 | 120 | 130.9 |
In the realm of a Gaussian distribution (Normal PDF), dispersion intervals are often referred to as probability or confidence intervals with appropriately designated percentage probability boundaries. When integration is performed across all input data spanning from -∞ to +∞ (encompassing the entire area beneath the Gaussian curve), a probability of 1 is derived, indicating that 100% of the data lies within the observed range. It was observed that the initial 50% of the data lies below the mean value (from -∞ to “x”), whereas the latter 50% surpasses this mean (spanning from “x” to +∞).
The probability associated with the occurrence of any undetermined value “x” (The area beneath the curve spanning from -∞ to a specific value “x” is considered when x is less than the mean x. Conversely, for values of x exceeding the mean x, the area under the curve from that specific value “x” to -∞ is taken into account), conforming to the normal distribution, can be deduced through the z-value, further interpreted using the standard table beneath the normal PDF. This z-value, representing the distance between the observed value “x” and the mean “$\bar{x}$” in units of standard deviation “s”, can be computed using the given formula.
A more streamlined method for assessing the area (or probability) beneath the normal PDF has been identified: the employment of the "NORM.DIST" statistical function in Excel. This function necessitates four input arguments:
• x - The value for which the probability (area under the curve from -∞ to this specific value) is determined.
• Mean - Represents the average value for the normal PDF.
• Standard_Dev - Denotes the standard deviation applicable to the normal PDF.
• Cumulative - This factor must be set to TRUE.
Outcomes from the "NORM.DIST" function range between 0 and 1. By scaling the result by 100%, the probability is discerned in percentage terms.
The "NORM.INV" function in Excel, an inverse of the cumulative distribution beneath the normal PDF, requires the following inputs:
• Probability: This pertains to the probability for which the normal PDF distribution's value is determined.
• Mean: The central tendency for the normal PDF.
• Standard_Dev: The dispersion for the normal PDF.
Table 9 illustrates practical uses of both "NORM.DIST" and "NORM.INV" functions. Cells employing the "NORM.DIST" function (spanning from C7 to I7) may be formatted to display values in percentage, thus immediately revealing percentage distribution values.
To provide a tangible example, consider an automated coffee packing machine in a factory. From a total of 800 coffee bags, a mean weight of 1005 g was determined, with a standard deviation across all bags being 5 g. It was hypothesized that these values followed a normal distribution. The following queries were then addressed:
a) What is the weight of the bag within which 95% of all bags are contained?
b) What is the deviation in the weight of the bag (X1) from the mean (1005 g) such that 80% of all bags are located within the range 1005 g±X1?
c) Given that 5% of bags weighing either more or less than the mean are eliminated from the total, what are the marginal weights of these excluded bags?
In the implementation of Excel solutions for the aforementioned example Table 10, the NORM.INV function was utilized within cells C6, D6, and E6 to ascertain a cumulative value that falls below a given probability, denoted as = NORM.INV (C3; C4; C5). The interpretation for problem 'b' suggests that up to the mean (ranging from -∞ to half of the normal PDF, which represents 50% of the bags), there lies 40% of the bags. From this, it is inferred that a 10% probability (50% - 40%) is employed to determine the lower limit value (X). The deviation in bag weight surrounding the mean value, represented as X1 in Table 9, is computed in cell D7 using = D4 - D6. Subsequently, the upper weight limit of the bag is derived in cell D8 as = D4 + D7. Addressing the problem denoted as 'c', it is understood that 2.5% of the bags that are lighter and another 2.5% that are heavier are eliminated from consideration. The deviation of the bag's weight surrounding the mean value is calculated in cell E7 as = E4 - E6. The upper weight threshold for the bag is then derived in cell E8 as = E4 + E7.
B | C | D | E | |
2 | Assignment | a) | b) | c) |
3 | Probability % | 95.00% | 10.00% | 2.50% |
4 | Mean Value (g) | 1005 | 1005 | 1005 |
5 | Standard Deviation (g) | 5 | 5 | 5 |
6 | Lower Limit (g) | 1013.2 | 998.6 | 995.2 |
7 | X1 (g) | 6.4 | 9.8 | |
8 | Upper Limit (g) | 1011.4 | 1014.8 |
In another illustrative example concerning the utilization of the Excel function for a normal PDF, a scenario with 80 apple trees in an orchard is presented. Measurements taken across a 10-year period—the effective exploitation duration of the orchard—revealed an average yield of 45 kg per tree annually. During harvest collection, individual apple weights were recorded. The derived mean weight for an apple stood at 180 g, with a standard deviation of 14 g. The assumption made was that these recorded apple weights follow a normal distribution.
Given this backdrop:
a) The task is to deduce the weight range between the lightest and heaviest apples from a sample size of 100 apples.
b) The endeavor is to project the weight extremities (lightest and heaviest) for apples from a single tree over a year.
c) From the comprehensive yield of 80 trees annually, the challenge is to ascertain the weight range of the lightest to the heaviest apple.
d) Over a span of 20 years, from the collective output of 80 trees, the goal is to determine the weight parameters from the lightest to the heaviest apple.
The adopted solution in Excel for this example Table 11 proceeds as follows: In a sample pool of 100 apples, considering the weight measurements adhere to a normal PDF, it's assumed the presence of one apple being the lightest and another as the heaviest. Scrutinizing the normal PDF suggests that for a batch of 100 apples, the lightest apple's weight (observed along the x-axis) corresponds to a 1% probability value, determined using the NORM.INV function in cell C11. The heaviest apple's weight, conversely, aligns with a 99% probability, discerned using the NORM.INV function in cell C12. As the apple sample size augments, the likelihood of encountering the lightest apple diminishes, whereas the probability of encountering the heaviest apple amplifies. The probability value, represented in percentages, for the lightest apple's occurrence within the sample is formulated as "= 1 / (number of apples)", with the respective cell being formatted in percentage (observed in cells C9, D9, E9, and F9). The counteracting probability, also formatted in percentages, of encountering the heaviest apple in the sample is computed by "= 1 - (probability of occurrence of the lightest apple)" and is discernible in cells C10, D10, E10, and F10. Leveraging these derived probabilities, the NORM.INV function facilitates the calculation of the weight range of the lightest (visible in cells C11, D11, E11, and F11) to the heaviest apples (observable in cells C12, D12, E12, and F12) for varying sample sizes.
B | C | D | E | F | |
2 | Assignment | a) | b) | c) | e) |
3 | Mean Vaule (g) | 180 | 180 | 180 | 180 |
4 | Standard Deviation (g) | 14 | 14 | 14 | 14 |
5 | One Tree (kg) | 45 | 45 | 45 | 45 |
6 | Year | 1 | 1 | 1 | 20 |
7 | Number of Trees | 1 | 1 | 80 | 80 |
8 | Number of Apples | 100 | 250 | 20000 | 400000 |
9 | Min Probablity | 1.000% | 0.400% | 0.005% | 0.000% |
10 | Max Probablity | 99.000% | 99.600% | 99.995% | 100.000% |
11 | Min Weight of Apple (g) | 147 | 143 | 126 | 116 |
12 | Max Weight of Apple (g) | 213 | 217 | 234 | 244 |
5. Discussion
In 2020, the onset of the COVID-19 pandemic markedly altered global patterns of living and working. Numerous governments, in an endeavor to mitigate the transmission of the virus, instituted rigorous measures, markedly limiting human mobility. Consequently, these measures exerted profound impacts on the educational sector, spanning from elementary institutions to tertiary levels. A transition to online learning modalities was observed across myriad educational entities.
An analysis of examination results from the College of Economics revealed a discernible decline in knowledge retention among students engaged in online pedagogy. In subjects of theoretical nature, examination outcomes were comparatively superior to subjects necessitating practical problem-solving demonstrations. It is posited that particular challenges were encountered during online pedagogical sessions in disciplines such as Mathematics, Statistics, and Programming languages.
Within the confines of this discussion, insights are proffered regarding the potential utility of MS Excel in online practical sessions, particularly within the domains of Mathematics and Statistics. Initial forays involved the articulation of rudimentary mathematical expressions, ensuring formula transparency for student cohorts, a strategy potentially viable even for primary educational levels. Additionally, applications of diverse statistical functions were elucidated, drawing upon pandemic-induced human mobility data, as disseminated by Google. It is postulated that heightened student engagement was achieved by enabling them to process and analyze mobility data pertinent to their locale, subsequently juxtaposing these with international datasets. Such an approach not only alleviated the repetitiveness for educators but also facilitated a diversified data pool for student assignments. Consequently, scenarios wherein students replicate peers' outputs, subsequently forwarding these to educators, were effectively circumvented.
Suggestions for Further Consideration
To bolster the efficacy and receptiveness of online educational methodologies, future endeavors could explore the integration of more real-time datasets, fostering a dynamic learning environment. Furthermore, periodic pedagogical audits, gauging student receptivity and outcomes, may provide pivotal insights, facilitating iterative enhancements to the e-learning ecosystem. It would also be prudent to assess the scalability and applicability of these strategies across diverse academic disciplines, ensuring a holistic enhancement of the online educational paradigm.
6. Conclusions
In many academic environments, statistics courses are predominantly delivered through the IBM SPSS software. However, it was observed that students often display an increased proficiency when mastering elementary statistical functions and analyses within Excel, a platform where data is frequently inputted. Subsequent to this preliminary analysis, data is typically exported to IBM SPSS to undertake more intricate tasks. The rationale posited is that initial data-related endeavors are more efficiently conducted within the originating software environment, predominantly Excel in most instances.
By integrating mathematics and statistics within Excel, two significant outcomes were noted. Firstly, an enhanced comprehension of the subject matter from the respective courses was achieved among students. Secondly, a tangible improvement in their operational proficiency with Excel was discerned. Research findings indicate that among potential employers, a pronounced emphasis is placed on a robust working knowledge of the Excel application when considering graduates in economics for employment.
Further Implications
As the digital landscape evolves and academic pedagogy adapts, it might be pertinent to further investigate the potential advantages of integrating more universally accessible and commonly used software platforms in academic instruction. A holistic exploration into the long-term benefits, both in terms of academic outcomes and employment prospects, derived from proficiency in such platforms could provide valuable insights for future curriculum development.
The data used to support the findings of this study are available from the corresponding author upon request.
The authors declare that they have no conflicts of interest.