1	A Basic Model Translation From Excel to JavaScript Albert Lilly Alabama School of Mathematics and Science
2	Motivation Help students build on what they already know if there are similarities between what they commonly know and what is being taught. Avoid confusion with dissimilarities. Sometimes building on what students know is labeled ‘learning in context’ or contextual learning.
3	Some Similarities A store button on a calculator --- one can retrieve what is stored using a recall button. A cell in a spreadsheet --- a number can be stored in a cell and referenced elsewhere by cell address or by a defined name. JavaScript --- a number can be stored in a variable and retrieved by using the variable.
4	Ordering Operations Using a calculator, you would not expect to recall a value before storing one. In JavaScript, the order of statements is significant. An example in JavaScript is: x = 5 // store 5 in x y = x * 3 // recall the value in x
5	Dissimilarities Dissimilarities that cause confusion arise because of what students are taught in algebra. The meaning of = must be relearned. Variable names and usages are different. Expression syntax is not the same. A semicolon can appear at the end of a JavaScript statement. HTML tags are needed to access JavaScript.
6	The Dissimilarity of = In algebra, an = denotes an equation but not in Excel or JavaScript. In Excel or JavaScript, the syntax on the right-hand side of = is an expression to be evaluated. In Excel, the cell itself stores the result of the expression. In JavaScript, a variable should appear on the left-hand side of = to store the result.
7	Examples Contrasting = In algebra, x = x + 1 is impossible but is valid in JavaScript. In JavaScript, x + 1 = 2 is impossible but is valid in algebra.
8	Confusion Over Variable Names In algebra, xy means x times y. In Javascript, xy is a single variable. Variable names in JavaScript begin with a letter or underscore and can contain multiple characters including the 26 alphabetic characters, the 10 digits, and underscore.
9	Issues of Expression Syntax In algebra, x ^•y means x times y. In Javascript, the correct syntax for multiplying x times y is x _* y. In Excel, =2^3 means raise 2 to the third power. In Javascript, 2 _2 _2 can be done with Math.pow(2,3) [ Javascript functions begin with Math. ]. Also, note that JavaScript is case sensitive.
10	Semicolon at a Statement End A semicolon is not required at the end of a statement but is good form. (A semicolon at the end of a statement is required in some languages such as C++ and Java.) A semicolon is required if two statements appear on the same line. For example: x = 5; y = 10
11	Example HTML Tags Used to Access JavaScript Via a Browser <FORM NAME=“add_one"> </FORM> <TABLE> </TABLE> <TR> </TR> <TD> </TD> <INPUT TYPE=float NAME=A1 SIZE=3> <INPUT TYPE=button VALUE="Compute" onClick="calculate()"> <TEXTAREA NAME="textInsert" ROWS=1 COLS=10> </TEXTAREA> <SCRIPT LANGUAGE="JavaScript"> </SCRIPT>
12	Basic Model Procedure State a simple problem. Work the problem with a hand held calculator. Do the same problem in Excel and compare results. Translate Excel to JavaScript and compare results.
13	Simple Problem Statement Add 1 to a number. For example, if the user enters 5, the output should be 6. Formula(s) Results
14	JavaScript Translation Overview
15	JavaScript Line by Line <FORM NAME="add_one"> <TABLE> <TR><TD><INPUT TYPE=float NAME=A1 SIZE=3 ></TD> <TD> <INPUT TYPE=button VALUE="Compute" onClick="calculate()"> </TD></TR></TABLE> <TABLE> <TR><TD> <TEXTAREA NAME="textInsert" ROWS=1 COLS=10> </TEXTAREA></TD></TR> </TABLE> </FORM> <SCRIPT LANGUAGE="JavaScript"> function calculate() { var A1 = 1*document.add_one.A1.value; document.add_one.textInsert.value =A1+1; } </SCRIPT>
16	The Form Name Must Match <FORM NAME="add_one"> var A1 = 1*document.add_one.A1.value; document.add_one.textInsert.value =A1+1;
17	The ‘Cell Name’ Must Match <TR><TD><INPUT TYPE=float NAME=A1 SIZE=3 ></TD> var A1 = 1*document.add_one.A1.value; document.add_one.textInsert.value =A1+1;
18	The Function Name Must Match <TD> <INPUT TYPE=button VALUE="Compute" onClick="calculate()"> <SCRIPT LANGUAGE="JavaScript"> function calculate() {
19	The ‘Text Area’ Name Must Match <TR><TD> <TEXTAREA NAME="textInsert" ROWS=1 COLS=10> </TEXTAREA></TD></TR> document.add_one.textInsert.value =A1+1;
20	Simple Interest Calculation Model Problem Statement Jane Doe took out a loan on January 1 from a bank for $6,000 at an annual interest rate of 4.25%. How much interest does Jane owe on August 12? (Assume each month has 30 days and a year has 360 days which is the policy adopted by some banks.)
21	Improvements Add labels in both Excel and JavaScript for clarification of the application to the user. Use meaningful variable names in formulas for better documentation. Meaningful variable names can be set up in Excel by clicking on Insert, clicking on Name, and clicking on Define… .
22	Excel Formula(s)
23	Excel Results
24	JavaScript Interface
25	Input Section of Form <FORM NAME="Interest"> <TABLE> <TR><TD>(1) Loan Amount:</TD> <TD><INPUT TYPE=float NAME=Loan_Amount SIZE=7></TD></TR> <TR><TD>(2) Interest Rate:</TD> <TD><INPUT TYPE=float NAME=Interest_Rate SIZE=7></TD></TR> <TR><TD>(3) Month:</TD> <TD><INPUT TYPE=float NAME=Month SIZE=7></TD></TR> <TR><TD>(4) Day:</TD> <TD><INPUT TYPE=float NAME=Day SIZE=7></TD></TR> <TR><TD><INPUT TYPE=button VALUE="Compute" onClick="calculate()"> </TD></TR> </TABLE>
26	Output Section of Form <TABLE> <TR><TD> <TEXTAREA NAME="textInsert" ROWS=1 COLS=12> </TEXTAREA></TD></TR> </TABLE> </FORM>
27	JavaScript Section <SCRIPT LANGUAGE="JavaScript"> function calculate() { var Loan_Amount = 1document.Interest.Loan_Amount.value; var Interest_Rate = 1document.Interest.Interest_Rate.value; var Month = 1document.Interest.Month.value; var Day = 1document.Interest.Day.value; var Interest_Owed = Loan_AmountInterest_Rate ((Month-1)*30+Day)/360; document.Interest.textInsert.value=Interest_Owed; } </SCRIPT>
28	Enhancements Round to two decimal places in JavaScript. Format using $ in JavaScript. Perform error checking using the isNaN() and parseFloat() JavaScript functions. Use min(Day,30) in Excel and Math.min(Day,30) in JavaScript for months with 31 days.
29	Extensions Start at any day of the year. Span multiple years. Include compounding of interest as an option.
30	Some Advantages of Using JavaScript Rather Than Excel JavaScript is free while Excel is proprietary. Cells do not have to be protected to prevent the user from changing formulas. JavaScript runs over the Internet. More advanced error checking can be done. For students, JavaScript is a higher level skill.
31	Contact Information E-mail: alilly@asms.net Presentation on the web: www.asms.net/~lilly/Excel2JS.html