- Email: [email protected]
Simulating electrical circuits with an electronic spreadsheet
Pergamon
036&1315(94)E8OO1-A
ComputersEduc. Vol. 22. No. 4. pp. 345-353, 1994 Copyright 0 1994 Elsevier ScienceLtd Printed in Great Britain. All rightsreserved 0360-I 3 IY94 $7.00 + 0.00
SIMULATING ELECTRICAL CIRCUITS ELECTRONIC SPREADSHEET
WITH AN
A. ALBERTO SILVA Escola Superior de Educa$o do Instituto Polittcnico do Porto, Portugal (Received 29 July 1993; accepted 28 October 1993)
Abstract-This paper describes ideas on how a spreadsheet can be an effective modeling and simulation tool for studying electrical circuits. An overview of the user interface is presented, and detailed
explanations are given so that any teacher can implement different spreadsheet models for simulation. The didactical interest of such a simulation tool is discussed, and a pilot in-field experiment is referred to. The teacher role is emphasized. Some aspects that characterize the computer as a special instrument are highlighted. I. INTRODUCTION
paper discusses an approach that is even more exciting and effective in its implementation when one can see the interactive features in operation. The author will be very pleased to offer a copy of the application to anyone who asks for it. The figures included here represent just one circuit layout and one simulation’s instance. We have used Microsoft Excel 4.0 for Windows as the developing tool. This tool has been chosen mainly for the following reasons:
This
l l l l
l l
no need for programming; possibility of high level programming if special interfaces for advanced features are needed; advanced built-in features in dealing with graphical objects and matrices; easier teacher’s customization, adaptation and generalization than with closed and specific applications [ 11; a standard, powerful and user-friendly interface, provided by the Windows environment; interoperability between PC compatible and Macintosh systems.
The availability of an alternative instrument like the one proposed here seems of particular importance, due to the known fact that electricity is a domain where alternative conceptions are common and persistent, both in adolescents and in non specialist teachers. Discussions about “alternative conceptions”, also known as “common-sense alternatives”, “misconceptions”, “preconceptions” and “prior conceptual frameworks”, can be found in the literature. See for example Ref. 2. The intended contribution of such an instrument in the understanding of concepts and in the development of capacities is based on some features that are intrinsically related to the computer as a supporting medium, namely: 0 interactivity; 0 easy interaction; l a persuasive incentive to experimentation, by freeing the user from tedious manipulations and repetitive tasks; l support in problem solving; l an abstraction level that is an intermediate between physical objects and formal algebraic entities. The instrument does not intend: l l l l
to to to to
C4E22:4-E
play the role of a tutor; create an environment for just drill and practice; be a substitute for laboratory work, pencil and paper use, or other media; impose methodologies, strategies or pedagogical norms. 345
A.
346
ALBERTOSILVA
It is intended to make available an open didactical instrument, to be used if and when its functional characteristics and the methodologies that it allows and potentiates are considered as adequate. The teacher’s role in making relevant focus and adequate suggestions, in providing “good ideas”, in promoting discussion and critical analyses, is irreplaceable. Thus, the ideas outlined here are: l l
addressed to the teachers, as supporting aimed at the pupils, as learning tools.
instruments;
2. DESCRIPTION A. Worksheet
layout
The Excel worksheet
is made up of four ranges,
as shown in the global view of Fig. 1:
0 top left-an input range; l middle left-a range for the matrix representation of Kirchoff’s rules and the resolution of the corresponding system of equations; l bottom left-a range for a diagram (schematic representation of the circuit under analyses); l top right-a range for auxiliary cells. These ranges will be referred to separately in the exposition. The matrices’ representation stresses the formal circuit rules and their mathematical counterpart, while the diagrammatic display is more intuitive and favors qualitative thought and anticipation. The adequacy of displaying both representations or just one of them will depend on educational purposes and context. To display the input range and just one of the representations above, we may, alternatively, proceed as follows: l
l
use the Excel Window, Split and Window, Freeze Panes commands to control vertical direction; make row height equal to zero to make specific worksheet lines invisible.
the display in the
In both cases, the different displays can be made permanently available, using the Window, View Excel feature: commuting from one view to another will then require only a selection from a menu. Another alternative to those just referred to, would consist of representing different parts in different worksheets, eventually linked in an Excel Workbook. In the pilot experiment referred to in Part 3, we have used a screen display as shown in Fig. 2. This kind of arrangement was obtained through commands Window, Split and Window, Freeze Panes. Adequate column widths were used to push the auxiliary range to the right, so as to make it invisible on the screen for normal use. For the sake of simplicity and effectiveness of the exposition, the circuit that will be used here as a reference is made up of one “real” voltage source, loaded by three resistors in a serial/parallel combination. The real source is, as usual, modeled as an “ideal” one, having an electro-motive force (e.m.f.) E, in series with an internal resistance Ri. B. Kirchofs
rules and matrices algebra
The input range is at the top of Fig. 1: to the right of each symbol, the user enters specific values for E, Ri, Ra, Rb, and Rc, represented in the diagram. In the matrices’ range, the unknown values are then computed as described in what follows. The Kirchoff’s rule for loops can be stated as “the algebraic sum of the voltages in any loop equals zero”. To apply this rule, we took the left-most loop (E, Ra, Rb, Ri) and the right-most one (Rb, Rc), and considered clockwise directions. It should be noticed that, according to these choices, the voltage across the ideal source terminals is - E. Other choices for directions or loops would lead to equivalent sets of equations: considering counter-clockwise directions would just change all the signs in the resulting equations; taking the outer loop (E, Ra, Rc, Ri) would not lead to another independent equation, but to one that is a linear combination of those corresponding to the preceding loops. The Kirchoff’s rule for branch points can be stated as “the sum of the currents going in one point equals the sum of the currents going out that point”. The circuit has two branch points: those
=SAfV
Vsource=
Fig. 1. Global
1ov
Y
10
view of the worksheet
Ri
-A
arrangement.
6.66 v
Mcurr
100
lb Ic
0.04
+
lsource
0.06 0.02
200
Rb
Left: the ranges
0.06 A
for Input,
1
= INDEX (MMULT (MINVERSE
Ra
100
and Diagram.
(Mres). Memf ),
Rc
Matrices
-I
Right:
an auxiliary
VRi
Vsource=
range for labels edition.
0.57 v
5.66 v
IcLabel
VaLabel
d
VparalLabei 3.77v
,0.04 A
0.02 A
IbLabel
0.06 A
IsourceLabel
10 v
ELabel
Rb 200 5-2
1on
Ri
100 R
Rc
Ra 100 R
348
A. ALBERTOSILVA
E (e.m.f.)
10
-P
5.66V
Vsource= =9.434v
Fig. 2. Entering
values and simulating
with a diagram: worksheet shown
one of the possible in Fig. 1.
screen arrangements
of the
connecting Rb and Rc. To apply this Kirchoff’s rule, we took the branch point at the top. Taking the one at the bottom would lead to an equivalent equation. With the choices made above, the equations (for loops and branch points, respectively)
T$(in)
= CZ,(out) I
become - E + Ra x Isource + Rb x Ib + Ri x Isource = 0 -RbxIb+RcxIc=O Isource = Ib + Ic
I
(Ra+Ri)xIsource+RbxIb OxIsource-RbxIb Isource - Ib in matrix
+OxZc +RcxIc -Ic
form, we have Ra+Ri ‘:
or, assuming
Rb -_;b
the input values shown in Fig. 1.
0 R;]
x [“‘r]=[!]
=10 = 0 =o
Electrical circuits in a spreadsheet
349
Notating the above matrices, respectively, as Mres, Mcurr and Memf, the last equation can be written as Mres x Mcurr = Memf or, by elementary matrix algebra Mcurr = Mres-’ x Memf The last equation is used at the right-most part of the matrices’ range to compute the values for the unknown currents, using the Excel syntax: l l
l
MINVERSE(Mres), computes Mres-‘, the inverse of Mres; MMULT((MINVERSE(Mres), Memf), computes the product (Mres-’ x Memf), that is to say, the matrix Mcurr; INDEX(. . . , l), INDEX(. . . ,2), INDEX(. . . , 3), get the first, second and third elements of the above computed matrix: Zsource, Zb and Zc, respectively.
We should stress here an obvious but very important aspect: all the values for Mres, Memf and Mcurr are automatically updated when the entered values are changed. With high performance hardware, this updating is virtually instantaneous. In Fig. 1 we include some explanatory labels that may be useful for the purpose of this exposition, but that are not required, and eventually not suitable, for student use. The computed values in the Mcurr matrix are rounded to two decimal digits, but Excel allows to work with numbers up to 15 digits. The number formatting can easily be changed with the command Formaf, Number. Please refer also to the next section about this aspect. C. Dynamic diagram The diagram presented in Fig. 2 belongs to the same worksheet as the input and matrices’ ranges referred to above, and is dynamically linked to those ranges. The diagram displays: l l l
l
the the the are the
inputted values; values computed in the matrices’ range; values for voltages across the resistors, computed as R x Z (the voltages across Rb and Rc the same and can be computed as Rb x Zb or as Rc x Zc); voltage supplied by the real source, computed as I/source= E- R x Zsource.
Drawing a diagram is a simple task that requires only basic skills. Excel has available a variety of toolbars, each of which contains buttons that can be selected via the mouse to accomplish a variety of tasks. One of these is the drawing toolbar. All symbols in Fig. 2 were created using buttons of this toolbar: text boxes (with borders) for the resistors; arrows for arrows; arcs for the arced curves; lines for the conductors and the source and also for the arrows in the arced curves. As in most familiar drawing software packages, placing the symbols is just a matter of “drag and drop” (selecting with the mouse, moving the mouse pointer with the mouse button pressed, and releasing this button). Obviously, any change made in the circuit layout implies making the corresponding changes in the input and the matrices’ ranges. The labels for entered and computed values in the dynamic diagram of Fig. 2 may be created directly in worksheet cells that are placed near the corresponding circuit elements, but Excel provides a better way: the utility toolbar includes a camera button that takes a picture of previously selected cells and pastes it at any selected place in the worksheet. This picture is dynamic: its content will reflect any changes made in the original cells. Using the toolbar camera has two important advantages: l
l
picture labels (this allows a auxiliary, and the user, fully
can be placed (by “drag and drop”) anywhere in the worksheet, not just in cells more precise and versatile graphical design); eventually hidden cells, can be used to “compose”, in a way that is transparent to explicit labels, including symbols for units representation (see the auxiliary range
A. ALBERTO SILVA
350
in Fig. 1). We have used A for ampere, V for volt and R for ohm. The former two symbols were included in labels using the joining operator “&“. The symbol for ohm was created using the Windows Equation Editor, supplied with Microsoft Word, and then conveniently placed (by “drag and drop”) in the auxiliary cells. The values in the diagram can be displayed with any number of decimal digits (up to 15). To control this display, we have used formulas that are illustrated by two labels in the auxiliary range. For example, the voltage value for the parallel Rb, Rc is displayed: l
l
with two decimal digits in Fig. 1-because we have used the formula = ROUND( Vparal,2)&“ I”‘; with three decimals in Fig. 2-because we have used the formula = ROUND( Vparal,3)&“v’ (not displayed in this figure).
We have used this approach because the Format, Number option referred to when discussing the matrices’ range does not have any effect in cells where the operator “&” is used. This approach has a disadvantage: numbers like 5.660 are displayed as 5.66 (see the voltage across Ra in Fig. 2) even if we write Round(. . . , 3). Of course, this peculiar behavior of Excel does not occur in cells containing only numbers. To overcome that aspect, we could use two cells instead of the operator “&“: in the example above, we should use one cell for the variable Vparal and another cell for the unit V (the camera tool works the same way for adjacent cells). Following this last approach, we could control the number of decimal digits by applying to the auxiliary cells containing values: either Format, Number; or Format, Column Width. To those who may prefer more explicit and appealing diagrams, it should be remembered that several packages (like CorelDRA W! and MicrograJix Designer) include electrical symbols ready to use that may be copied and pasted into an Excel worksheet. D.
Suggestions for simple generalizations
Developing worksheets for other circuit layouts (different arrangements, a greater number of passive elements, several sources, capacitive and inductive impedances) is an easy task. We first consider two simple generalizations that can be easily implemented by following the description above: l
l
increasing the number of the circuit loops or branching points. This will affect the number of elements of the three matrices; changing what is the input and what are the unknown values. For example: providing the value of one current and not the value of one resistance; or using a real current source, instead of a real voltage one. Such changes will affect the meaning of the matrices-as “voltages”, “currents” and “resistances” will be “mixed” in the matrices-but not the general matrices layout, nor the formulas in the unknowns’ matrix.
For alternating-current circuits including general impedances, the Mres matrix must become a (Impedances’ Matrix) one, and the matrix equation has no formal changes. But, although Microsoft Excel supports complex numbers’ representations and operations, the user must explicitly use the special Excel functions for operation with complex numbers-e.g. IMPRODUCT (numberl, number2) for the product of the complex numbers number1 and number2. As a consequence, the user must also specify an algorithm for the matrix operations MINVERSE(. . .> and MMULTIPLY(. . .), or, alternatively, specify some other way to solve the systems of equations. “Mimped”
3. A PILOT EXPERIMENT The ideas presented here have been tested during the school year 1992/93 with pre-service students of a teacher training school, in the Physics discipline (second year of the student’s course) in a class of 17 students. Those students will have a specialization in Mathematics and Sciences and will be teachers for age levels 6-13. The students were about 20 years old, on average. At the beginning of the course, we made some pretests for determining students’ concepts about basic
351
Electrical circuits in a spreadsheet
aspects of mechanics and electricity. The pilot experiment took place after covering some basic aspects concerning these fields, both in the laboratory and lectural classes. The total time dedicated to working on the spreadsheet model was 3 h. Students were arranged in groups of two, with one computer per group. They were allowed, and even encouraged, to discuss and to get help from their colleagues and from the teacher. The activities were closely followed and supported by the teacher. It should be stressed that teacher’s “explanations” were not of the “magistral” type (addressed to all the class at the same time). They were provided to each group, if and when it was necessary. Students were asked to make several simulations, with the following: Recommendations l l l l l
l l
try to predict what will happen as a consequence of changing the value of one parameter; analyse and interpret qualitatively what really happens when you make that change; do not forget to test extreme situations (very large, very low, zero values); make occasional quantitative verifications of Ohm’s Law; make occasional quantitative verifications of Kirchoff’s rules, with different choices for loops, branching points, circulation’s direction in loops, arbitrated directions for currents; make occasional quantitative verifications of energy relationships; write, for each type of the activities suggested above, one set of illustrating results and one statement summarizing the most important involved aspect(s).
We mention some student’s attitudes, ideas and (mis)conceptions discussed during the activities with the spreadsheet model: l
l
l
l
that have been explicitly
some pupils attempted to verify Kirchoff’s rule for branching points . . . in a loop (“the sum of the currents in a loop equals zero”); some pupils were confused about the meaning of the currently used statement “electrical power is dissipated”. They were right: this formulation suggests that “electrical power is lost”, and does not stress that “electrical power is transformed”; most of the students felt as against intuition that an increase in the current supplied by the (real) source implied a decrease in the voltage across its terminals; some of them thought that “to short-circuit a resistor” was the same as “to put the resistor out of the circuit”. The reason for this idea was: “in both cases, the resistor is irrelevant, there is no current in it”;
l
the situation with Ra = 0 and Rb = 0, also caused a lot of discussion: the voltage across Ra and Rb was zero, but there was a current in those “resistors”. “How can we compute Ifrom V= RI
l
some students questioned Ohm’s Law itself: “When setting Rb to 0, the current in Ra increases
in this case?“; (and we understand that), and the voltage across Ra also increases. Why does this voltage increase when that current increases? We can see this in the formula V= RI, but we don’t understand: how does Ra know that it must now have a greater voltage.?‘.
Specific details about the pretests and its results, as well as about the activities and the teacher support to students, are available from the author.
4. DISCUSSION
AND PROVISIONAL
CONCLUSIONS
The pilot experiment referred to above, and preliminary evaluation of results, suggest that the following aspects should be highlighted. The teacher’s role l
A previous diagnosis of students’ misconceptions, with a pretest and/or class discussion, seems of fundamental importance to the teacher’s planning;
352
l
A. ALBERTO SILVA
the teacher must play an important role: making focus; stressing relationships; facilitating cooperative understanding; inducing progressive abstraction; promoting anticipation and critical analyses; promoting verbalization of processes and algorithms; providing support in learning conflicts.
The student’s attitudes l
The students made simulations beyond what they were asked to; they worked with impressive commitment and concentration; they frequently called for teacher’s help or for teacher’s confirmation of results; they stayed in place after lesson time was over, and some of them asked to stay in the computer laboratory during lunchtime to go on with their work.
The students’ conceptions
Both pretests and work in the lab, as well as the work on the spreadsheet model, lead to the conclusion that students usually have difficulty with such topics as: l l l
l l l l
l
differentiating between current and voltage; differentiating between current and energy; an electric potential difference as (minus) the work done by an electrical field or an electric potential energy difference as (minus) the work done by an electrical force [3,4]; energy balances; serial and parallel constructions of circuit elements; extreme situations, such as short/open-circuits and very high or very low source loads; the difference between a real voltage source and an ideal one (distinguishing between E and Vsource, understanding the role of the internal resistance); how a modification in a part of a circuit affects all the circuit.
Curricular aspects l
Although the formalism of Kirchoff’s laws is not explicitly taught to ages Cl 3, we believe that the topics stated above are of crucial importance for Science teachers of those age levels. This is particularly important if (as is the case of the author’s country) they are going to deal in their future classes with some elementary related aspects (good and bad conducting materials, sample circuits with batteries and bulbs, magnets, different forms of energy).
The eflectiveness l
l
l
l
of the spreadsheet
model
The absence of wires, components, connections, measuring instruments and other hardware, provided a very time effective environment. In a 3 hour period, students could carry out a multitude of experiments, discuss them with colleagues and with the teacher, test extreme cases, write their reports. Every minute was useful, there were no experimental “accidents” and errors, no fastidious computations, no need for hardware setting up, no need for concern about other aspects not strictly related to the fundamental laws and theoretical aspects; the computer’s interface and processing power (allowing comfortable accomplishment of as many experiments as necessary and releasing the user from repetitive and fastidious aspects of experimentation) has provided a very effective way of promoting the users’ pleasure in experimenting and also in focusing their attention to the essential aspects (processes, local and global circuit values and states); the simulation environment provided the possibility of testing ideal conditions (for example, Ri = 0) and other extreme cases (for example, Ra = Rb = 0), cases of impossible or very difficult implementation with real hardware circuits; the spreadsheet modeling and simulation tool and the described activities provided a multitude of opportunities for cognitive conflicts between intuitive and scientific ideas, and helped to clarify ideas and overcome misconceptions.
The ideas presented in this paper were developed in the scope of a more general research program. The author will be very pleased to report further aspects in the future, as well as to hear any comments and suggestions.
Electrical circuits in a spreadsheet
353
REFERENCES 1. Silva A. A., A tool for language teachers: a Spread (use of the) Sheet. J. Educl Technol. Systems, 20, 3-9 (1991). 2. Cleminson A., Establishing an epistemilogical base for science teaching in the light of contemporary notions of the nature of science and of how children learn science. J. Res. Sci. Teaching, 27,429-445 (1990). 3. Feynman R. P., Leighton R. B. and Sands M., The Feynman Lectures in Physics, Vol. II, Chap. 4. Addison-Wesley, London (1966). 4. Sears F. W. and Zemansky M. W., University Physics, Chap. 26. Addison-Wesley, London (1975).
036&1315(94)E8OO1-A
ComputersEduc. Vol. 22. No. 4. pp. 345-353, 1994 Copyright 0 1994 Elsevier ScienceLtd Printed in Great Britain. All rightsreserved 0360-I 3 IY94 $7.00 + 0.00
SIMULATING ELECTRICAL CIRCUITS ELECTRONIC SPREADSHEET
WITH AN
A. ALBERTO SILVA Escola Superior de Educa$o do Instituto Polittcnico do Porto, Portugal (Received 29 July 1993; accepted 28 October 1993)
Abstract-This paper describes ideas on how a spreadsheet can be an effective modeling and simulation tool for studying electrical circuits. An overview of the user interface is presented, and detailed
explanations are given so that any teacher can implement different spreadsheet models for simulation. The didactical interest of such a simulation tool is discussed, and a pilot in-field experiment is referred to. The teacher role is emphasized. Some aspects that characterize the computer as a special instrument are highlighted. I. INTRODUCTION
paper discusses an approach that is even more exciting and effective in its implementation when one can see the interactive features in operation. The author will be very pleased to offer a copy of the application to anyone who asks for it. The figures included here represent just one circuit layout and one simulation’s instance. We have used Microsoft Excel 4.0 for Windows as the developing tool. This tool has been chosen mainly for the following reasons:
This
l l l l
l l
no need for programming; possibility of high level programming if special interfaces for advanced features are needed; advanced built-in features in dealing with graphical objects and matrices; easier teacher’s customization, adaptation and generalization than with closed and specific applications [ 11; a standard, powerful and user-friendly interface, provided by the Windows environment; interoperability between PC compatible and Macintosh systems.
The availability of an alternative instrument like the one proposed here seems of particular importance, due to the known fact that electricity is a domain where alternative conceptions are common and persistent, both in adolescents and in non specialist teachers. Discussions about “alternative conceptions”, also known as “common-sense alternatives”, “misconceptions”, “preconceptions” and “prior conceptual frameworks”, can be found in the literature. See for example Ref. 2. The intended contribution of such an instrument in the understanding of concepts and in the development of capacities is based on some features that are intrinsically related to the computer as a supporting medium, namely: 0 interactivity; 0 easy interaction; l a persuasive incentive to experimentation, by freeing the user from tedious manipulations and repetitive tasks; l support in problem solving; l an abstraction level that is an intermediate between physical objects and formal algebraic entities. The instrument does not intend: l l l l
to to to to
C4E22:4-E
play the role of a tutor; create an environment for just drill and practice; be a substitute for laboratory work, pencil and paper use, or other media; impose methodologies, strategies or pedagogical norms. 345
A.
346
ALBERTOSILVA
It is intended to make available an open didactical instrument, to be used if and when its functional characteristics and the methodologies that it allows and potentiates are considered as adequate. The teacher’s role in making relevant focus and adequate suggestions, in providing “good ideas”, in promoting discussion and critical analyses, is irreplaceable. Thus, the ideas outlined here are: l l
addressed to the teachers, as supporting aimed at the pupils, as learning tools.
instruments;
2. DESCRIPTION A. Worksheet
layout
The Excel worksheet
is made up of four ranges,
as shown in the global view of Fig. 1:
0 top left-an input range; l middle left-a range for the matrix representation of Kirchoff’s rules and the resolution of the corresponding system of equations; l bottom left-a range for a diagram (schematic representation of the circuit under analyses); l top right-a range for auxiliary cells. These ranges will be referred to separately in the exposition. The matrices’ representation stresses the formal circuit rules and their mathematical counterpart, while the diagrammatic display is more intuitive and favors qualitative thought and anticipation. The adequacy of displaying both representations or just one of them will depend on educational purposes and context. To display the input range and just one of the representations above, we may, alternatively, proceed as follows: l
l
use the Excel Window, Split and Window, Freeze Panes commands to control vertical direction; make row height equal to zero to make specific worksheet lines invisible.
the display in the
In both cases, the different displays can be made permanently available, using the Window, View Excel feature: commuting from one view to another will then require only a selection from a menu. Another alternative to those just referred to, would consist of representing different parts in different worksheets, eventually linked in an Excel Workbook. In the pilot experiment referred to in Part 3, we have used a screen display as shown in Fig. 2. This kind of arrangement was obtained through commands Window, Split and Window, Freeze Panes. Adequate column widths were used to push the auxiliary range to the right, so as to make it invisible on the screen for normal use. For the sake of simplicity and effectiveness of the exposition, the circuit that will be used here as a reference is made up of one “real” voltage source, loaded by three resistors in a serial/parallel combination. The real source is, as usual, modeled as an “ideal” one, having an electro-motive force (e.m.f.) E, in series with an internal resistance Ri. B. Kirchofs
rules and matrices algebra
The input range is at the top of Fig. 1: to the right of each symbol, the user enters specific values for E, Ri, Ra, Rb, and Rc, represented in the diagram. In the matrices’ range, the unknown values are then computed as described in what follows. The Kirchoff’s rule for loops can be stated as “the algebraic sum of the voltages in any loop equals zero”. To apply this rule, we took the left-most loop (E, Ra, Rb, Ri) and the right-most one (Rb, Rc), and considered clockwise directions. It should be noticed that, according to these choices, the voltage across the ideal source terminals is - E. Other choices for directions or loops would lead to equivalent sets of equations: considering counter-clockwise directions would just change all the signs in the resulting equations; taking the outer loop (E, Ra, Rc, Ri) would not lead to another independent equation, but to one that is a linear combination of those corresponding to the preceding loops. The Kirchoff’s rule for branch points can be stated as “the sum of the currents going in one point equals the sum of the currents going out that point”. The circuit has two branch points: those
=SAfV
Vsource=
Fig. 1. Global
1ov
Y
10
view of the worksheet
Ri
-A
arrangement.
6.66 v
Mcurr
100
lb Ic
0.04
+
lsource
0.06 0.02
200
Rb
Left: the ranges
0.06 A
for Input,
1
= INDEX (MMULT (MINVERSE
Ra
100
and Diagram.
(Mres). Memf ),
Rc
Matrices
-I
Right:
an auxiliary
VRi
Vsource=
range for labels edition.
0.57 v
5.66 v
IcLabel
VaLabel
d
VparalLabei 3.77v
,0.04 A
0.02 A
IbLabel
0.06 A
IsourceLabel
10 v
ELabel
Rb 200 5-2
1on
Ri
100 R
Rc
Ra 100 R
348
A. ALBERTOSILVA
E (e.m.f.)
10
-P
5.66V
Vsource= =9.434v
Fig. 2. Entering
values and simulating
with a diagram: worksheet shown
one of the possible in Fig. 1.
screen arrangements
of the
connecting Rb and Rc. To apply this Kirchoff’s rule, we took the branch point at the top. Taking the one at the bottom would lead to an equivalent equation. With the choices made above, the equations (for loops and branch points, respectively)
T$(in)
= CZ,(out) I
become - E + Ra x Isource + Rb x Ib + Ri x Isource = 0 -RbxIb+RcxIc=O Isource = Ib + Ic
I
(Ra+Ri)xIsource+RbxIb OxIsource-RbxIb Isource - Ib in matrix
+OxZc +RcxIc -Ic
form, we have Ra+Ri ‘:
or, assuming
Rb -_;b
the input values shown in Fig. 1.
0 R;]
x [“‘r]=[!]
=10 = 0 =o
Electrical circuits in a spreadsheet
349
Notating the above matrices, respectively, as Mres, Mcurr and Memf, the last equation can be written as Mres x Mcurr = Memf or, by elementary matrix algebra Mcurr = Mres-’ x Memf The last equation is used at the right-most part of the matrices’ range to compute the values for the unknown currents, using the Excel syntax: l l
l
MINVERSE(Mres), computes Mres-‘, the inverse of Mres; MMULT((MINVERSE(Mres), Memf), computes the product (Mres-’ x Memf), that is to say, the matrix Mcurr; INDEX(. . . , l), INDEX(. . . ,2), INDEX(. . . , 3), get the first, second and third elements of the above computed matrix: Zsource, Zb and Zc, respectively.
We should stress here an obvious but very important aspect: all the values for Mres, Memf and Mcurr are automatically updated when the entered values are changed. With high performance hardware, this updating is virtually instantaneous. In Fig. 1 we include some explanatory labels that may be useful for the purpose of this exposition, but that are not required, and eventually not suitable, for student use. The computed values in the Mcurr matrix are rounded to two decimal digits, but Excel allows to work with numbers up to 15 digits. The number formatting can easily be changed with the command Formaf, Number. Please refer also to the next section about this aspect. C. Dynamic diagram The diagram presented in Fig. 2 belongs to the same worksheet as the input and matrices’ ranges referred to above, and is dynamically linked to those ranges. The diagram displays: l l l
l
the the the are the
inputted values; values computed in the matrices’ range; values for voltages across the resistors, computed as R x Z (the voltages across Rb and Rc the same and can be computed as Rb x Zb or as Rc x Zc); voltage supplied by the real source, computed as I/source= E- R x Zsource.
Drawing a diagram is a simple task that requires only basic skills. Excel has available a variety of toolbars, each of which contains buttons that can be selected via the mouse to accomplish a variety of tasks. One of these is the drawing toolbar. All symbols in Fig. 2 were created using buttons of this toolbar: text boxes (with borders) for the resistors; arrows for arrows; arcs for the arced curves; lines for the conductors and the source and also for the arrows in the arced curves. As in most familiar drawing software packages, placing the symbols is just a matter of “drag and drop” (selecting with the mouse, moving the mouse pointer with the mouse button pressed, and releasing this button). Obviously, any change made in the circuit layout implies making the corresponding changes in the input and the matrices’ ranges. The labels for entered and computed values in the dynamic diagram of Fig. 2 may be created directly in worksheet cells that are placed near the corresponding circuit elements, but Excel provides a better way: the utility toolbar includes a camera button that takes a picture of previously selected cells and pastes it at any selected place in the worksheet. This picture is dynamic: its content will reflect any changes made in the original cells. Using the toolbar camera has two important advantages: l
l
picture labels (this allows a auxiliary, and the user, fully
can be placed (by “drag and drop”) anywhere in the worksheet, not just in cells more precise and versatile graphical design); eventually hidden cells, can be used to “compose”, in a way that is transparent to explicit labels, including symbols for units representation (see the auxiliary range
A. ALBERTO SILVA
350
in Fig. 1). We have used A for ampere, V for volt and R for ohm. The former two symbols were included in labels using the joining operator “&“. The symbol for ohm was created using the Windows Equation Editor, supplied with Microsoft Word, and then conveniently placed (by “drag and drop”) in the auxiliary cells. The values in the diagram can be displayed with any number of decimal digits (up to 15). To control this display, we have used formulas that are illustrated by two labels in the auxiliary range. For example, the voltage value for the parallel Rb, Rc is displayed: l
l
with two decimal digits in Fig. 1-because we have used the formula = ROUND( Vparal,2)&“ I”‘; with three decimals in Fig. 2-because we have used the formula = ROUND( Vparal,3)&“v’ (not displayed in this figure).
We have used this approach because the Format, Number option referred to when discussing the matrices’ range does not have any effect in cells where the operator “&” is used. This approach has a disadvantage: numbers like 5.660 are displayed as 5.66 (see the voltage across Ra in Fig. 2) even if we write Round(. . . , 3). Of course, this peculiar behavior of Excel does not occur in cells containing only numbers. To overcome that aspect, we could use two cells instead of the operator “&“: in the example above, we should use one cell for the variable Vparal and another cell for the unit V (the camera tool works the same way for adjacent cells). Following this last approach, we could control the number of decimal digits by applying to the auxiliary cells containing values: either Format, Number; or Format, Column Width. To those who may prefer more explicit and appealing diagrams, it should be remembered that several packages (like CorelDRA W! and MicrograJix Designer) include electrical symbols ready to use that may be copied and pasted into an Excel worksheet. D.
Suggestions for simple generalizations
Developing worksheets for other circuit layouts (different arrangements, a greater number of passive elements, several sources, capacitive and inductive impedances) is an easy task. We first consider two simple generalizations that can be easily implemented by following the description above: l
l
increasing the number of the circuit loops or branching points. This will affect the number of elements of the three matrices; changing what is the input and what are the unknown values. For example: providing the value of one current and not the value of one resistance; or using a real current source, instead of a real voltage one. Such changes will affect the meaning of the matrices-as “voltages”, “currents” and “resistances” will be “mixed” in the matrices-but not the general matrices layout, nor the formulas in the unknowns’ matrix.
For alternating-current circuits including general impedances, the Mres matrix must become a (Impedances’ Matrix) one, and the matrix equation has no formal changes. But, although Microsoft Excel supports complex numbers’ representations and operations, the user must explicitly use the special Excel functions for operation with complex numbers-e.g. IMPRODUCT (numberl, number2) for the product of the complex numbers number1 and number2. As a consequence, the user must also specify an algorithm for the matrix operations MINVERSE(. . .> and MMULTIPLY(. . .), or, alternatively, specify some other way to solve the systems of equations. “Mimped”
3. A PILOT EXPERIMENT The ideas presented here have been tested during the school year 1992/93 with pre-service students of a teacher training school, in the Physics discipline (second year of the student’s course) in a class of 17 students. Those students will have a specialization in Mathematics and Sciences and will be teachers for age levels 6-13. The students were about 20 years old, on average. At the beginning of the course, we made some pretests for determining students’ concepts about basic
351
Electrical circuits in a spreadsheet
aspects of mechanics and electricity. The pilot experiment took place after covering some basic aspects concerning these fields, both in the laboratory and lectural classes. The total time dedicated to working on the spreadsheet model was 3 h. Students were arranged in groups of two, with one computer per group. They were allowed, and even encouraged, to discuss and to get help from their colleagues and from the teacher. The activities were closely followed and supported by the teacher. It should be stressed that teacher’s “explanations” were not of the “magistral” type (addressed to all the class at the same time). They were provided to each group, if and when it was necessary. Students were asked to make several simulations, with the following: Recommendations l l l l l
l l
try to predict what will happen as a consequence of changing the value of one parameter; analyse and interpret qualitatively what really happens when you make that change; do not forget to test extreme situations (very large, very low, zero values); make occasional quantitative verifications of Ohm’s Law; make occasional quantitative verifications of Kirchoff’s rules, with different choices for loops, branching points, circulation’s direction in loops, arbitrated directions for currents; make occasional quantitative verifications of energy relationships; write, for each type of the activities suggested above, one set of illustrating results and one statement summarizing the most important involved aspect(s).
We mention some student’s attitudes, ideas and (mis)conceptions discussed during the activities with the spreadsheet model: l
l
l
l
that have been explicitly
some pupils attempted to verify Kirchoff’s rule for branching points . . . in a loop (“the sum of the currents in a loop equals zero”); some pupils were confused about the meaning of the currently used statement “electrical power is dissipated”. They were right: this formulation suggests that “electrical power is lost”, and does not stress that “electrical power is transformed”; most of the students felt as against intuition that an increase in the current supplied by the (real) source implied a decrease in the voltage across its terminals; some of them thought that “to short-circuit a resistor” was the same as “to put the resistor out of the circuit”. The reason for this idea was: “in both cases, the resistor is irrelevant, there is no current in it”;
l
the situation with Ra = 0 and Rb = 0, also caused a lot of discussion: the voltage across Ra and Rb was zero, but there was a current in those “resistors”. “How can we compute Ifrom V= RI
l
some students questioned Ohm’s Law itself: “When setting Rb to 0, the current in Ra increases
in this case?“; (and we understand that), and the voltage across Ra also increases. Why does this voltage increase when that current increases? We can see this in the formula V= RI, but we don’t understand: how does Ra know that it must now have a greater voltage.?‘.
Specific details about the pretests and its results, as well as about the activities and the teacher support to students, are available from the author.
4. DISCUSSION
AND PROVISIONAL
CONCLUSIONS
The pilot experiment referred to above, and preliminary evaluation of results, suggest that the following aspects should be highlighted. The teacher’s role l
A previous diagnosis of students’ misconceptions, with a pretest and/or class discussion, seems of fundamental importance to the teacher’s planning;
352
l
A. ALBERTO SILVA
the teacher must play an important role: making focus; stressing relationships; facilitating cooperative understanding; inducing progressive abstraction; promoting anticipation and critical analyses; promoting verbalization of processes and algorithms; providing support in learning conflicts.
The student’s attitudes l
The students made simulations beyond what they were asked to; they worked with impressive commitment and concentration; they frequently called for teacher’s help or for teacher’s confirmation of results; they stayed in place after lesson time was over, and some of them asked to stay in the computer laboratory during lunchtime to go on with their work.
The students’ conceptions
Both pretests and work in the lab, as well as the work on the spreadsheet model, lead to the conclusion that students usually have difficulty with such topics as: l l l
l l l l
l
differentiating between current and voltage; differentiating between current and energy; an electric potential difference as (minus) the work done by an electrical field or an electric potential energy difference as (minus) the work done by an electrical force [3,4]; energy balances; serial and parallel constructions of circuit elements; extreme situations, such as short/open-circuits and very high or very low source loads; the difference between a real voltage source and an ideal one (distinguishing between E and Vsource, understanding the role of the internal resistance); how a modification in a part of a circuit affects all the circuit.
Curricular aspects l
Although the formalism of Kirchoff’s laws is not explicitly taught to ages Cl 3, we believe that the topics stated above are of crucial importance for Science teachers of those age levels. This is particularly important if (as is the case of the author’s country) they are going to deal in their future classes with some elementary related aspects (good and bad conducting materials, sample circuits with batteries and bulbs, magnets, different forms of energy).
The eflectiveness l
l
l
l
of the spreadsheet
model
The absence of wires, components, connections, measuring instruments and other hardware, provided a very time effective environment. In a 3 hour period, students could carry out a multitude of experiments, discuss them with colleagues and with the teacher, test extreme cases, write their reports. Every minute was useful, there were no experimental “accidents” and errors, no fastidious computations, no need for hardware setting up, no need for concern about other aspects not strictly related to the fundamental laws and theoretical aspects; the computer’s interface and processing power (allowing comfortable accomplishment of as many experiments as necessary and releasing the user from repetitive and fastidious aspects of experimentation) has provided a very effective way of promoting the users’ pleasure in experimenting and also in focusing their attention to the essential aspects (processes, local and global circuit values and states); the simulation environment provided the possibility of testing ideal conditions (for example, Ri = 0) and other extreme cases (for example, Ra = Rb = 0), cases of impossible or very difficult implementation with real hardware circuits; the spreadsheet modeling and simulation tool and the described activities provided a multitude of opportunities for cognitive conflicts between intuitive and scientific ideas, and helped to clarify ideas and overcome misconceptions.
The ideas presented in this paper were developed in the scope of a more general research program. The author will be very pleased to report further aspects in the future, as well as to hear any comments and suggestions.
Electrical circuits in a spreadsheet
353
REFERENCES 1. Silva A. A., A tool for language teachers: a Spread (use of the) Sheet. J. Educl Technol. Systems, 20, 3-9 (1991). 2. Cleminson A., Establishing an epistemilogical base for science teaching in the light of contemporary notions of the nature of science and of how children learn science. J. Res. Sci. Teaching, 27,429-445 (1990). 3. Feynman R. P., Leighton R. B. and Sands M., The Feynman Lectures in Physics, Vol. II, Chap. 4. Addison-Wesley, London (1966). 4. Sears F. W. and Zemansky M. W., University Physics, Chap. 26. Addison-Wesley, London (1975).