Managing the Hyflex Scheduling Activity Using Excel Dynamic Arrays

1. Introduction

Starting in spring 2020, the COVID-19 pandemic compelled universities to teach most courses with an online component. As the public health authorities sought to manage the risk of virus transmission, they banned in-person classes or limited the number of people allowed in a given classroom. Universities responded by delivering courses using a variety of delivery modalities. These range from completely online and asynchronous to completely in-person with “socially distanced” students sitting in the classroom. The “hyflex” (hybrid flexible) modality (Lederman 2020) becomes particularly attractive when the socially distanced classroom capacity is insufficient to accommodate all students. In the hyflex modality, only a certain number of students are allowed to be in the classroom with the instructor, whereas the remaining students participate online using a synchronous video link such as Zoom. In the interest of fairness, students rotate through in-person and online presence. This allows some classroom interaction over the course of the academic term while also meeting the health goals of limiting attendance. It falls on the instructor to decide who shall be in-class and online for each session and to communicate these assignments to students.

The number allowed in the hyflex classroom is determined using guidelines provided by the local health authorities and can change on short notice. Because most universities strongly desire to maximize in-person interaction, it is important to ensure that all allowed classroom seats are filled. Work by Scherer (2011) supports such policies that encourage in-person class attendance as much as possible since in-person students outperformed both hybrid and fully online students.

In this paper, we present highly efficient design techniques in Excel to create various schedules and reports summarizing the assignments of each student to specific class dates. We acknowledge that the instructor could construct these schedules and reports manually, but it would be tedious, time consuming, and error prone. (The word “nightmarish” is sometimes heard.) The hyflex scheduling activity is not “one and done”—The scheduling activity likely must be performed several times in response to external events. For example, students often add or drop the course after the scheduling activity is first performed. The health authorities might change the room’s working capacity (number of available seats). The dean might adjust the number of sessions. Thus, instructors must be prepared to reimplement the scheduling activity multiple times for each course that they teach.

The need to redo the process on short notice is particularly vexing, so the automated solution that we propose for hyflex scheduling is clearly very desirable. We believe that our paper will be helpful to faculty teaching hyflex courses that require student scheduling and reporting of schedules by automating a tedious manual process, and especially by having automation that works quickly when students add or drop, and a new schedule must be disseminated under time pressure.

One might think that in the post–COVID-19 future, universities will return to status quo before COVID-19. However, it is likely that many educational institutions will want to continue some form of hybrid learning such as hyflex. This is due to its ability to accommodate students from a greater geographical range or those who need to stay at home for personal reasons, such as child or elder care, or because they are ineligible for vaccination. Of course, the COVID-19 pandemic is continuing to cause harm, and it is possible that another virus could emerge in the future. Thus, hyflex courses and this scheduling challenge will persist beyond the time window of COVID-19, as will the need for an automated solution.

1.1. Contribution

We present an innovative approach to automate the process to manage which students attend each session in-person and online. Our approach uses “pure” Excel without add-ins or macros. Upon entry of readily available data, our approach automatically generates an essential set of reports, including an instructor-facing roster (indicating for each session which students shall be in-person and which on-line), two types of student-facing schedules (indicating for each student which sessions shall be attended in-person and which on-line), and a summary indicating the number of in-person sessions for each student.

These reports are essential to implementing the hyflex approach and are tedious and error-prone to create by hand. Our approach responds automatically to changes in data and instantly updates all the required reports. Our approach scales well, in that it performs effortlessly with hundreds of students. Our automated approach is suitable for transfer to others, thus making it easy for less Excel-savvy faculty and administrators to use it in planning hyflex courses.

The nature of the problem necessitates the generation in Excel of output ranges with variable numbers of rows and columns, making it less suitable for traditional Excel programming approaches. Our approach leverages new features of Excel that have only recently become available. These features, called dynamic array functions, are “game changers” for many challenging spreadsheet development projects, and are essential to handle the hyflex scheduling process. The new dynamic arrays are not available in legacy versions of Excel such as Excel 2016, Excel 2019, and so on. Dynamic arrays are available only in Excel 365. Microsoft began to provide dynamic array formulas to Excel 365 users in early 2020 and rolled them out to all 365 users over a period of time. As a consequence, dynamic arrays can be poorly understood or even unknown to many faculty, students, and skilled Excel programmers.

Therefore, in addition to presenting a solution to the hyflex scheduling and reporting challenge, we provide a tutorial on the practical basics of using dynamic arrays. Our tutorial has the important advantage that the Excel features are introduced not in isolation but in a realistic problem-solving context that enhances interest and retention. We believe that this paper will be helpful to faculty teaching hyflex courses that require student scheduling and reporting of schedules, by automating a tedious manual process, and especially by having automation that works quickly when students add or drop, and a new schedule must be disseminated under time pressure. It will be of interest to faculty interested in continuous education by staying abreast of the startling new developments in the ubiquitous Excel spreadsheet; to faculty who teach students how to solve problems using spreadsheets; and ultimately to the students who will benefit from instructors with awareness and skills in new, powerful Excel programming techniques.

1.2. Review of Related Work

We are not aware of past research that specifically addressed the hyflex scheduling problem from an instructor’s point of view, although there has been work on related problems. Several authors have examined aspects of online versus in-person delivery modality. Bordoloi (2016) discusses a blended course with a mix of in-person and online learning. Scherer (2011) compares in-person, hybrid (a mix of online and in-person), and online modalities for an undergraduate statistics course. Isken (2014) compares online and in-person modalities for a spreadsheet modeling laboratory-based course. Each of these papers consider an arrangement where all students share modality for each session, whereas this paper considers hyflex (some in person, some online) with a dynamic split of students between modalities.

Cardonha et al. (2022) sought to optimize the assignment of classes to classrooms at the University of Connecticut under their pandemic guidelines of only 50% capacity for a given room. More specifically, their heuristic optimization approach involved the assignment of teaching modalities to class/classroom pairs (hybrid, completely online, etc.) due to this 50% requirement and the fact that total classroom space across campus could not accommodate all classes under it. Murray (2020) based his work on timber harvesting research to create a spatial optimization program for classroom layout that maximizes the number of students that a given classroom or educational space can accommodate given pandemic-related social distancing constraints. He pointed out that the challenge in the modeling for a given classroom is the large number of possible arrangements of desks or student positions in the classroom. Practical considerations for faculty space, equipment space, aisles, and so on, can prove difficult to visualize and include in such modeling. We observe that the classroom social distancing problem in this context approaches a manufacturing facility layout problem with its initial myriad of possible feasible spatial arrangements. In contrast, our approach takes the number of students allowed in a classroom as given data.

A related problem is the final exam scheduling or timetabling problem. Burke et al. (2010) provide the base modeling of this problem, considering a set of exams that must be scheduled, a limited number of ordered timeslots (time periods) in which to schedule exams, and a limited number of classrooms of certain capacity in each timeslot. The problem is to assign all the exams into the timeslots subject to a set of constraints that can be divided into two categories, known as hard and soft constraints. Hard constraints are defined as those which must be satisfied to have a feasible timetable solution. A common hard constraint is that different exams that have common students cannot be scheduled into the same timeslot. Soft constraints are less essential, and violation of them is acceptable if they cannot be satisfied. The amount of violation of these soft constraints is used to evaluate the quality of a given feasible solution. This final exam scheduling problem is more difficult than the focus of our work but shares the goal of ensuring equity in student scheduling subject to physical classroom limitations and automating decision making and reporting.

Some universities define how seats are assigned. The University of Northern Iowa (Provost 2020) provides guidelines defining how classrooms seats are to be assigned to students to allow for groups of students to be assigned to attend a given class meeting face-to-face while others attend online. The same student group would all attend in person on a designated class meeting day or all attend online. The class meeting schedule, the size/arrangement of the classroom, and the number of students in the class would determine the size of a group that would attend in-person for a given class meeting. The plan required that students receive assigned seats during the entire semester.

This allowed for rotations of set groups of students through the class meetings in the semester. The hope in requiring such was it would minimize the physical transfer of the virus between students because they would only be near the same group all the time. This arrangement would also facilitate contact tracing due to the limited numbers of students around one who might contract the disease. These guidelines are in contrast to the approach we are proposing that allows for students to choose their own seat for a given class meeting.

1.3. Overview of Paper

We introduce the problem, state our contribution, and review the literature in Section 1. We lay out the hyflex scheduling activity and the reports that need to be generated in Section 2. We next (Section 3) provide a tutorial on dynamic arrays. In Section 4, we present our automated solution, the underlying cell formulas, and show how to combine dynamic array functions into powerful formulas, highlighting the benefits of an iterative approach to discovering effective formulas. We end with conclusions and future work in Section 5.

2. Hyflex Scheduling Activity

A hyflex course is one in which for any given class session, certain designated students shall be physically present in the classroom, whereas all other students shall attend remotely using Zoom or a similar online meeting product (Lederman 2020). We refer to these two groups as “roomers” and “zoomers,” respectively.

This situation might arise purposefully in the design of a course or academic program. However, we, like many instructors, encountered this situation for the first time as a result of the pandemic, because health authorities required the university to establish a classroom working capacity that is less than the physical capacity used to set registration limits. The data required are the working capacity of the room; the registration list indicating the names of students in the course; and a list of dates the course meets. In practice, these data are readily available.

Before the course begins, the instructor must identify, for each class session, the specific students from the registration list to be roomers, with all other students being zoomers for that session. Then the instructor then needs to generate four reports.

1. The Roster Report indicates for each session, which students shall be roomers, and which shall be zoomers.

2. The Schedule Report indicates for each student, for each session, whether they are a roomer or a zoomer.

3. The Student-Roomer Report indicates for each student, the dates on which they are a roomer.

4. The Session Count Report indicates for each student, the number of sessions where they are a roomer.

2.1. Student Selection

The selection as to who is a roomer for each session is illustrated by the following small illustrative example. If a course has 12 students enrolled, and the classroom working capacity is 7 seats, we can assign students to be roomers as shown in Table 1. Naturally, for each session the students not indicated as roomers will be zoomers. Once the selection is made, the instructor must generate the four reports.

Table 1. Students Who are Selected as Roomers for Each Session

2.2. Roster Report

The Roster Report (Figure 1) indicates for each session, all the students who will be roomers. Figure 1 uses bottom borders below “Larry” to illuminate the structure of the table, but we do not use these bottom borders in subsequent figures. The Roster Report allows the instructor to plan in-class questions and activities based on the characteristics of individual students, take attendance, and record class participation.

The creation of the Roster Report can be time consuming and error prone when done manually. With manual creation, the instructor must enter student names into the columns of the sessions, placing the first student into the first seat of the first session, adding names downward and wrapping into successive columns until every student is entered, and then continuing with the first student. This is necessary for each course that the instructor teaches, and these reports must often be changed at the last minute.

The Roster Report supports the instructor’s needs. The students need a report so they know which sessions they shall attend in person. There are two ways to present this information, which we call the Schedule Report (Section 2.3) and the Student-Roomer Report (Section 2.4). Different institutions and student bodies might prefer one way to the other, so we generate both.

2.3. Schedule Report

The Schedule Report (Figure 2) shows for each student, for each class session, whether they are a roomer (indicated by “R”) or a zoomer (indicated by “Z”).

To create the Schedule Report (Figure 2) select a student, examine the first column of the Roster Report (Figure 1), and if the student appears, enter “R” in the first column of the Schedule Report. Otherwise, enter “Z.” Then, repeat for each column in the Roster Report, for all students.

2.4. Student-Roomer Report

The Student-Roomer Report (Figure 3) indicates for each student the sessions where they are a roomer. For example, Beth is a roomer in sessions 1, 2, 4, 6, 8, 9, 11, 13, and 14.

To create the Student-Roomer Report select a student, find the first occurrence of “R” in that student’s row of the Schedule Report (Figure 2), and indicate the Session (from the column header of the Schedule Report) in the first column of the Student-Roomer Report. Find the next occurrence of “R” in that student’s row of the Schedule Report and indicate the Session in the next column of the Student-Roomer Report. Continue until the end of the row and repeat for all other students.

2.5. Session Count Report

To increase understanding of the schedule, provide evidence of fairness, and increase confidence in the efficacy of the process, it is helpful to generate a Session Count Report (Figure 4). This indicates for each student the total number of sessions where they are a roomer. For example, Beth is a roomer for nine sessions.

To create the Session Count Report, select a student and count the number of times that student appears in the Roster Report (Figure 1) and repeat for all students.

2.6. Use Notes

We used this approach in practice during the COVID-19 pandemic. The first author automatically generated and used reports shown in this paper to assign students to be roomers and zoomers for four modules (half-semesters) in multiple MBA courses. A typical module had Num_Sessions = 14, Num_Students = 42, and Work_Cap = 30. Initially, reports were generated using hardcoding and awkward VBA macros, which motivated us to develop the automated approach shown in this paper.

3. Tutorial on the Practical Basics of Excel Dynamic Arrays

Dynamic arrays provide a dramatic improvement in Excel’s capability, and we provide a brief tutorial on them. For the reader not familiar with dynamic arrays, this section both enables understanding of the programming described in Section 4 and provides the ability to use dynamic arrays on one’s own. We discuss dynamic array concepts including the spill range and the #SPILL! error. We discuss three of the most useful new dynamic array functions (SEQUENCE, XLOOKUP, and FILTER) and show how the legacy function TRANSPOSE now works as a dynamic array. (A more comprehensive review of dynamic arrays is available at Cheusheva (2021a) and Exceljet (2021a).)

3.1. Dynamic Array Concepts

Dynamic arrays allow a user to enter a formula into one cell, and the formula populates results into neighboring cells. This is called spilling, and the populated cells are called the spill range. The spill range is dynamic in that it can change in size depending on the values of arguments in the formula. We illustrate using the SEQUENCE function.

3.1.1. Dynamic Array Function SEQUENCE.

The SEQUENCE function populates a range with a sequence of numbers 1, 2,…, N. SEQUENCE can be used with this syntax: = SEQUENCE (Rows, Columns). The dynamic-array formula

is in cell D3 of Figure 5. It populates a spill range with eight rows and five columns, with values 1, 2,…, 40 running across the rows.

3.1.2. #SPILL! Error.

Should any cell in the spill range not be empty, Excel returns the #SPILL! error and does not populate any cell in the spill range. For example, cell M3 in Figure 5 contains the same formula as cell D3:

The formula in cell M3 is prevented from spilling because cell N8 in the spill range contains the text “This data prevents M3 from spilling,” generating a #SPILL! error in cell M3. A #SPILL! error does not indicate that there is an error in the formula but rather that there is an error in the spreadsheet layout due to insufficient empty cells. Thus, the user must provide space for spill ranges when designing a spreadsheet.

3.1.3. Editing Dynamic Array Formulas.

Only the top left cell in the spill range can be edited. If another cell in the spill range is selected, the formula does appear in the formula bar; but it is “ghosted” using a light gray font to signal that it cannot be changed (Figure 6).

If you need to update the formula, you must select the top-left cell in the spill range, change it, and press Enter. Excel will automatically adjust the size and contents of the spill range.

3.1.4. Dynamic Array Functions as an Extension to Legacy Array Functions.

Dynamic arrays can be thought of as an extension of Excel’s powerful legacy array functions (e.g., FREQUENCY, TRANSPOSE) that are coded using control-shift-enter (CSE). In Excel 365, the CSE functions are now implemented as dynamic arrays, greatly enhancing their flexibility and utility. Control-shift-enter is not required in Excel 365, although it is available for backward compatibility.

3.2. Nesting Array Functions

It is possible to nest a dynamic array function inside another dynamic array function, yielding subtle and powerful results (as in Section 4). We illustrate how nesting works using SEQUENCE inside the legacy array function TRANSPOSE.

SEQUENCE (8, 5) in Figure 7 populates the values 1, 2,…, 40 across the rows, placing values in the first row, then the second row, and so on. If values need to be populated down columns, we nest SEQUENCE in TRANSPOSE. In Figure 7 (bottom),

generates a five-row, eight-column spill range, and populates the range down columns, placing values in the first column, then second column, and so on. The dynamic array function TRANSPOSE processes the entirety of the SEQUENCE function’s output.

3.3. Spilled Range Operator (# Character)

It is sometimes necessary to reference all the cells in a spill range, but because the spill range is dynamic, its size can be unknown ahead of time. To dynamically reference a full spill range, a hash character (#, called the spilled range operator) is appended to a cell reference. The spilled range operator causes the cell formula to adapt automatically as the spill range grows or contracts.

Figure 8 provides examples. Cell B3 uses the SEQUENCE worksheet function to create a range with two rows and three columns. The results spill to B3:D4. Cell H3 contains

where appending the spilled range operator to the cell reference B3 causes the formula to apply to the spill range of cell B3. This makes the formula spill into a range the same size as the spill range generated by cell B3. The formula in cell H3 must refer to the cell containing the dynamic array formula (cell B3) and not to any of the other cells in the spill range B3:D4.

Cell N3 contains

which returns the largest value in the range spilled by cell H3.

Comparing cells B3, H3, and N3 with B8, H8, and N8, respectively, we see how both cell values and the size of the spill range change dynamically with different argument values in a dynamic array formula.

3.4. Dynamic Array Function FILTER

Excel has long provided a basic Filter feature (Data > Sort and Filter > Filter) that selectively displays rows in a range or table of data (Microsoft 2022a). However, the legacy Filter feature is static in that it handles only fixed values—not cell formulas—and does not update should data change. The new dynamic array function FILTER is a “live” function—it allows cell formulas and updates instantly when a cell value changes.

We provide an example in Figure 9. Cell B7 contains

The first argument contains a range of cells to be filtered, and the second argument provides criteria for filtering in the form of a TRUE/FALSE logical test. In this example, the logical test for each cell in the range E2:J2 is whether the cell content is “chairs.” This is TRUE only for the first cell (E2) and the sixth cell (J2). FILTER then visits the cells in the first argument (B4:G4) and returns the values in the first and sixth cells of B4:G4, (“1st” and “6th”) and displays them in cells B7:C7.

In cell E7, FILTER uses a range with multiple rows, using cell formula

The range of cells to be filtered (B4:G5) has two rows, and the results (cells E7:G8) also have two rows. The filter criterion in the second argument has one row; FILTER determines the criteria by column and returns all rows in the selected columns.

If there is no match (all logical tests in the second argument are FALSE), FILTER returns the #CALC! error as shown in cell I8 of Figure 9. The #CALC! error can be replaced with a user-selected value by using the optional third argument (cell J8). See Microsoft (2021a) and Cheusheva (2021b) for more information and the formal syntax of FILTER.

3.5. Dynamic Array Function XLOOKUP

Microsoft developed the XLOOKUP function to supersede legacy functions including VLOOKUP, HLOOKUP, and LOOKUP and the workhorse INDEX(MATCH) technique (Exceljet 2021c, PowerUser 2021). XLOOKUP can look vertically, horizontally, first to last, last to first, and even return an entire column or row of data and not just a single value (Cheusheva 2021c). We illustrate the basic functionality as used in this paper in Figure 10. Cell D3 contains

The function looks for the first appearance of 5 in range B2:B6, which occurs in the second cell (B3), and returns the value “IPA” from the second cell in H3:H7.

XLOOKUP can also return a range of values, as shown in cell D6 of Figure 10. This is not possible with VLOOKUP. The XLOOKUP in cell D6 finds the value 6 in the third cell of range B2:B6 and returns the third row of range H3:J7.

XLOOKUP provides optional arguments for more powerful lookups. Unlike VLOOKUP, XLOOKUP has no restriction on the location of the lookup array with respect to the return array. The formal syntax is XLOOKUP (lookup value, lookup array, return array, [if not found], [match mode], [search mode]). See Microsoft (2022c) and Exceljet (2021c) for more information and the formal syntax of XLOOKUP.

4. Overview of Automated Solution

In Section 2, we showed what needs to be accomplished. We now show how to use the dynamic array functions of Section 3 to accomplish it. Given a set of data, we explain in Section 4.1 how to preprocess it. We code the Roster Report, Schedule Report, Student-Roomer Report, and Session Count Report in Sections 4.2–4.5. We show how reports update automatically when data change. For each nested cell formula, we carefully “build” the formula step-by-step so the reader can understand the elegance and power of dynamic array formulas. For simplicity, we defer the coding of session dates until Section 4.6. We discuss the benefits of an iterative development approach in Section 4.7.

4.1. Data and Preprocessing

We use a small illustrative example to show how the reports function and update. The Initial Raw Data column of Figure 11 comprises a registration list containing student names, a working capacity (available classroom seats) with value 3, and the number of sessions with value 6. The Initial Raw Data are preprocessed as shown in the Initial Working Data column of Figure 11.

The user makes the registration list into an Excel table named Reg_List, and Excel applies a light blue border to indicate the table boundaries. We make use of the Excel table feature because the table Reg_List dynamically changes size as the Raw Data changes size, which is a great aid to coding the reports. (Excel tables are discussed in Cheusheva (2022) and Exceljet (2021b).)

In Figure 11 (left), the user places the Working Capacity into a cell named Work_Cap, and the Number of Sessions into a cell named Num_Sessions. Excel automatically calculates the number of students using

and displays the value 7 in the cell named Num_Students.

Now, suppose that because of changes in the environment, the Initial Raw Data are replaced by New Raw Data, which necessitates reimplementing the process and generating new reports. In the New Raw Data column of Figure 11, two additional students register (Helen and Irene); the health authorities increase the working capacity to four seats, and the dean decides the course should have seven sessions.

It is trivial for the user to update the working data. As shown in the New Working Data column of Figure 11, the user enters 4 into cell Work_Cap and 7 into cell Num_Sessions. To update the Reg_List table, the user simply appends “Helen” and “Irene” to the bottom of the Initial Working Data table (or pastes the new registration list on top of the original table), and the Reg_List table automatically expands to accommodate the new data. The calculation in cell Num_Students immediately responds to indicate that there are nine students.

Our approach instantly updates all the reports as soon as the user changes the Working Data, without any other user intervention. In subsequent sections, we use the preprocessed Working Data shown in Figure 11 and reference it using the Excel names Reg_List, Work_Cap, Num_Sessions, and Num_Students.

4.2. Roster Report

Figure 12 (top) shows the Roster Report for the Initial Data. Each column in the body of the report (cells J17:O19) indicates the students assigned to that session.

The Roster Report is generated automatically upon data preprocessing (Section 4.1). When the data change from Initial Data to New Data, the preprocessing actions described in Section 4.1 cause the Roster Report to automatically update as shown in Figure 12 (bottom), including populating the appropriate number of rows and columns, the content of the report, and the row and column headers.

We now explain the cell formulas used to create the Roster Report.

4.2.1. Formulas for the Row and Column Headers of the Roster Report.

The row and column headers are each generated by a single formula. The row header formula (indicating seat number) is entered into cell I17:

We do not enter a formula into cells I18 and downward; they are populated by the dynamic array formula in cell I17.

The column header formula (indicating Session number) is entered into cell J16:

We do not enter a formula into cells K16 and to the right; they are populated by the dynamic array formula in cell J16.

4.2.2. Formula for the Body of the Roster Report.

The body of the Roster Report (Figure 12; cells J17:O19 in the top, cells J17:P20 in the bottom) requires only a single cell formula in cell J17. All the other cells are populated by the spill from cell J17. Compared with traditional Excel design, there is no need to copy and paste into the other cells in J17:P20.

The body of the Roster report is generated using Equation (1) in cell J17 of Figure 12:

Even experienced Excel users may find Formula 1 to be challenging, or even intimidating. Fear not! In the remainder of this section, we build it from the bottom up, illustrating in a stepwise fashion how its component functions do their work, and how they are nested. This stepwise approach also illustrates an iterative approach to devise a complex formula that we find essential when creating a nested dynamic array formula.

Equation (1) uses four Excel functions with three levels of nesting: SEQUENCE is nested in TRANSPOSE, which is nested in MOD, which in turn is nested in INDEX. The SEQUENCE function, which is most deeply nested within Equation (1), is shown on its own in Equation (2):

To illustrate the coding of the body of the Roster Report, we use in Figures 13–19 an example with Num_Sessions = 9, Work_Cap = 7, and Num_Students = 8. The reader is encouraged to enter the formulas into Excel to get a feel for how they work.

Figure 13 shows the results when Equation (2) is in the top left cell with-Num_Sessions = 9 and Work_Cap = 7.

Equation (2) populates 9 × 7 = 63 cells with values from 1 to 63 written across the rows. However, we want the values written down the columns. This is easily handled by nesting Equation (2) in TRANSPOSE, yielding Equation (3):

Figure 14 shows the results when Equation (3) is in the top left cell with Num_Sessions = 9 and Work_Cap = 7.

Now that the values from 1 to 63 are written down the columns, we next want the values to cycle from 1 to Num_Students (in our example, from 1 to 8 rather than 1 to 63). This requires modular arithmetic using a modulus of Num_Students. Excel performs modular arithmetic using the MOD function (Microsoft 2021b). We nest Equation (3) inside MOD to yield Equation (4):

Figure 15 shows the results when Equation (4) is in the top left cell with Num_Sessions = 9, Work_Cap = 7, and Num_Students = 8.

We now have the repeating sequence 1, 2, 3, 4, 5, 6, 7, 0 written down the columns. Next, we must coax Excel into changing the final 0 to be an 8, generating down the columns the repeating sequence 1, 2, 3, 4, 5, 6, 7, 8. This requires a few steps. First, we subtract 1 from Equation (3) to so it starts at 0 instead of 1, resulting in Equation (5):

Figure 16 shows the results when Equation (5) is in the top left cell with Num_Sessions = 9 and Work_Cap = 7.

We see that Equation (5) writes the values 0 to 62 down the columns. Recalling that we nested Equation (3) inside MOD with modulus Num_Students to yield Equation (4), we now nest Equation (5) within MOD to yield Equation (6):

Figure 17 shows the results when Equation (6) is in the top left cell with Num_Sessions = 9, Work_Cap = 7, and Num_Students = 8.

We now have the repeating sequence 0, 1,…, 7 written down the columns. To achieve the desired sequence of 1, 2, 3, 4, 5, 6, 7, 8, we simply add 1 to Equation (6), resulting in Equation (7):

Figure 18 shows the results when Equation (7) is in the top left cell with Num_Sessions = 9, Work_Cap = 7, and Num_Students = 8.

We have now achieved the milestone of the repeating sequence 1, 2, 3, 4, 5, 6, 7, 8 written down the columns. Our final iteration is to map each value in Figure 18 to the corresponding name in the Registration List. The INDEX function (Microsoft 2022b) handles this nicely. We nest Equation (7) inside INDEX to return the corresponding name from the registration list Reg_List, resulting in Equation (8):

The alert reader will notice that Equation (8) is the same as Equation (1). Figure 19 shows the output when this formula is placed in the top left cell with Num_Sessions = 9, Work_Cap = 7, and Num_Students = 8, with Reg_List as shown.

The first entry in Reg_List is Student Al. In Figure 19, Al appears in every cell that contains value 1 in Figure 18. Student Beth is the second entry in Reg_List. In Figure 19, Beth appears in every cell that contains value 2 in Figure 18. The eighth entry in Reg_List is Helen, who appears in every cell that contains value 8 in Figure 18.

This concludes our development of Equation (1) and the Roster Report, illustrating an iterative approach to nesting dynamic array functions to achieve a sophisticated and valuable result.

4.3. Schedule Report

Figure 20 (top) shows the Schedule Report for the initial data. In the body of the report, an R in a cell indicates that the Student is a roomer for that Session, and a Z indicates that the Student is a zoomer.

The Schedule Report is generated automatically upon data preprocessing (Section 4.1). When the data change from Initial Data to New Data, the preprocessing actions described in Section 4.1 cause the Schedule Report to automatically update (Figure 20, bottom), including populating the appropriate number of rows and columns, the content of the report, and the row and column headers.

4.3.1. Formulas for the Row and Column Headers of the Schedule Report.

The row and column headers are each generated by a single formula. The row header formula (indicating Student) is entered into cell AB13:

We do not enter a formula into cells AB14 and downward; they are populated by the dynamic array formula in cell AB13. Column AB will contain values only as indicated in the Reg_List table, and cells in column AB below the last student will be empty. The column header formula (indicating Session number) is entered into cell AC12:

This dynamic array formula uses a “spill range reference” that uses “#” to return the entire range populated by the formula in cell J16 (visible in Figure 12). As before, we do not enter a formula into cells AD12 and rightward; they are populated by the dynamic array formula in cell AC12.

4.3.2. Formula for the Body of the Schedule Report.

The Excel coding to create the body of the Schedule Report (Figure 20, cells AC13:AH19 in the top, cells AC13:AI21 in the bottom) does not use dynamic arrays. It uses a formula that is written in cell AC13 and then copied right and down to other cells. The formula in cell AC13 is

This formula is copied to the right for as many columns as the highest possible number of sessions (we used 14, but the number is arbitrary). These formulas are then copied down for as many rows as the highest possible number of students (we used 100, but the number is arbitrary). Cells in the Schedule report are active only if both the Student row header in column AB displays a value, and the Session column header in row 12 displays a value. Cells that are not active are “forced blank” using the ISBLANK check in the logical test field of the first IF function. Each cell that is active examines the Roster Report (Figure 12) and uses COUNTIF to determine whether the student appears in the Roster for the given Session, and if yes returns “R” (for roomer) and otherwise returns “Z” (for zoomer). In a later section, we explain how to adjust the numbered column headers in cells AC12:AI12 to display both session number and date.

4.4. Student-Roomer Report

Figure 21 (top) shows the Student-Roomer Report for the Initial Data and the New Data. For each Student, the Sessions where the Student is a roomer are listed across the row.

The Student-Roomer Report is generated automatically upon data preprocessing (Section 4.1). When the data change from Initial Data to New Data, the preprocessing actions described in Section 4.1 cause the Student-Roomer Report to automatically update (Figure 21, bottom), including populating the appropriate number of rows and columns, the content of the report, and the row headers.

The formula to create the row headers of the Student-Roomer Report is the same as for the Schedule Report in Section 4.3, using this formula in cell AU13:

As before, cells AU14 and downward are populated by the dynamic array formula in cell AU13.

The formulas to create the body of the Student-Roomer Report are based on this formula in cell AV13:

This formula is copied down for as many rows as the highest possible number of students (we used 100). A row in the Student-Roomer Report is active only if the Student row header in column AU contains a value. Cells in rows that are not active are “forced blank” using the “ISBLANK” check in the logical test field of the IF function. For active rows, the formula in cell AV13 uses the dynamic array function FILTER to populate each row. FILTER inspects the student’s row of the Schedule Report, identifying the columns where the cell value is R (for roomer). For the first student Al in row 13, this is cells AC13:AP13 (partially shown in Figure 20). The formula returns the corresponding column header from the Schedule Report in cells AC12:AP12 (partially shown in Figure 20).

4.5. Session Count Report

Figure 22 (left) shows the Session Count Report for the Initial Data and the New Data. The report shows, for each student, the number of sessions where the student is a roomer.

The Session Count Report is generated automatically upon data preprocessing (Section 4.1). When the data change from Initial Data to New Data, the Session Count Report automatically updates (Figure 22, right), including populating the appropriate number of rows, the content of the report, and the row headers.

4.5.1. Formula for the Row Headers of the Session Count Report.

The formula to create the row headers is the same as for the Schedule Report in Section 4.3 and the Student-Roomer Report in Section 4.4, using this formula in cell BM13:

As usual, cells BM14 and downward are populated by the dynamic array formula in cell BM13.

4.5.2. Formula for the Body of the Session Count Report.

The Session Count Report is calculated by counting the number of times each student in the Reg_List appears in the Roster Report (Figure 12). The formula in cell BN13 is

The argument J17# uses the spilled range operator “#” to reference the entire spill range populated by the formula in cell J17 (cells J17:O19 in Figure 12, top, and cells J17:P20 in Figure 12, bottom). Cells BN14 and downward are populated by the dynamic array formula in cell BN13.

4.6. Extension: Displaying Session Dates

In Figures 1–3, we presented reports that displayed both session number and date. For example, session 1 meeting on January 20 was displayed as “(1) Jan 20.” Session number and date appear as column headers in the Roster Report (Figure 1) and Schedule Report (Figure 2) and are in the body of the Student-Roomer Report (Figure 3). To simplify the exposition in Sections 4.2–4.5, we displayed only session number and did not display the corresponding date of the session. In this section, we present a simple extension to display the session number and date rather than only the session number.

The Roster Report in Figure 23 presents column headers that display the session number and date in cells J16:P16 rather than just the session number.

To display the date of each session, we require new data in the form of a date list that indicates the session number and the corresponding date of the session. We place this data in an Excel table named Date_List (cells B12:C19 in Figure 23) with two fields [Session] and [Date]. Excel defaults to storing a date as a numeric value and uses formats to control display of the date. If the date is stored as a numeric value, the cell formulas in the reports will display the numeric value rather than a formatted date. Therefore, it is essential that the dates in the date list be entered as text. We ensure this by entering the dates with an apostrophe followed by the written date. For example, in Figure 23, cell C13 contains the datum:

The use of the date list necessitates additional data preprocessing. The number of sessions (Num_Sessions in cell F25) is now no longer data but is instead calculated from the table Date_List using this formula:

To include the date in the column headers in cells J16:P16 of the Roster Report, we replace in cell J16 this old formula (from Section 4.2) that presents only the session number:

with this new formula that presents the session number and date:

The dynamic array function XLOOKUP takes a session number, finds it in the Session column of Date_List, and returns the corresponding value from the Date column in Date_List.

At this point, we established in the column headers of the Roster Report the desired display of session number + date. These cells are automatically picked up by the column headers for the Schedule Report, and the body of the Student-Roomer Report, and no additional modifications to the spreadsheet are required to display dates in those reports. Figure 24 shows the updated Schedule Report containing the new column headers.

Figure 25 shows the updated Student-Roomer Report containing the new dates in the body of the report.

Because the cell formulas for the body of the report return selected values from the column headers of the Schedule Report (see Section 4.4), no new coding is required.

4.7. Iterative Development Approach

We recommend that users follow an iterative development approach and do not attempt to write nested dynamic array formulas all at once. Section 4.2 illustrated the iterative development process that ultimately yielded Equation (8). We find that systematic experimentation with complex formulas such as nested functions is a necessary step. Equations (3), (4), and (6) were not part of the final result, but they were essential for “the coder’s journey” to determine the correct syntax. We find that this approach of iterative experimentation is helpful, perhaps essential, to the creation of nested dynamic array formulas.

5. Conclusion

We explained the hyflex approach to teaching and identified the need to create four reports to implement the hyflex approach in practice and to have the ability to quickly update the reports when circumstances change. This is a regular occurrence due to the ebb and flow of COVID-19 precautions. Our teaching experience has taught us that creating these reports is tedious and error-prone and that students want updated reports quickly. Therefore, our automated solution is highly valuable. Because the number of rows and columns in the reports change with the data, these reports are unsuitable for traditional Excel programming approaches. Fortunately, Excel 365’s recently released dynamic array functions allow us to create the reports automatically in a manner that updates instantly and effortlessly when the data change. We explained how to code the reports in Excel. To enable genuine access to the programming approach, we also provided a practical tutorial to the Excel’s new dynamic array functions.

5.1. Equivalent Situations

This same problem of equitably assigning people to slots can occur in other contexts. A company might want to invite vendor reps to a series of lunches that are held periodically. If there are dozens of vendors, each with one or more reps to be entertained, then the company might not to want to invite all of them to every lunch, so it must choose which reps will attend which specific lunches on a rotating basis. Another example is an executive wanting to periodically meet with small groups of employees in his organization on a rotating basis.

5.2. Strong Student Interest in Learning Dynamic Array Functions

We are finding that our MBA students are very interested in the powerful new dynamic array functions of Excel. We are teaching dynamic arrays (including this paper!) in our spreadsheet analytics elective courses with good success. The power and novelty of this new class of functions can be helpful for sustaining and increasing enrollments in our electives.

5.3. Further Research

From time to time, a student who is scheduled to be a roomer might be unable to attend class due to planned travel or other foreseeable circumstance. It is desirable to have another student attend instead. Our approach does not accommodate this situation. Further research might find an automated solution to this challenge.