Autocalc HTML table cell value from other cell val

# Thread: Autocalc HTML table cell value from other cell val

1. Junior Member
Join Date
Dec 1969
Posts
28

## Autocalc HTML table cell value from other cell val

Hi,<BR><BR>I have an an HTML table set up where I can key in a cell value on the first row using JavaScript. Now I need to have several other row cells to automatically calculate their values based on the entry. I guess I&#039;m trying to simulate a spreadsheet. Can anyone get me started on figuring out how I can do this automatic calculation. Thanks in advance!<BR>

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Where does formula come from?

Is the formula static, or can the user enter it?<BR><BR>To pick an example, using a simple static formula:<BR><BR>&#060;FORM&#062;<BR>&#060;INPUT Name="quantity" Value=1<BR>onChange="this.form.total.value=this.va lue * this.form.price.value;"&#062;<BR><BR>&#060;INPUT Name="price" Value=0<BR>onChange="this.form.total.value=this.va lue * this.form.quantity.value;"&#062;<BR><BR>&#060;INPU T Name="total" onFocus="this.form.quantity.focus();"&#062;<BR>&#0 60;/FORM&#062;<BR><BR><BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
28

## RE: Where does formula come from?

Thanks for responding!<BR><BR>The formula is static. The specific calculations that I&#039;m trying to represent are "\$ Variance vs Last Year" and "% Variance vs Last Year" from one period to the next. For instance, I have a table that has all 12 months (Jan - Dec) as my column headers. As rows, I have "2002 Adjusted Budget", "\$ Variance vs Last Year", "% Variance vs Last Year", "2001 Actual/Forecast" and so on for several years each with their own variance rows comparing it to the previous year. The user can only update the "2002 Adjusted Budget" row, but what ever they enter in will affect the values of both the 2002 variance rows for the column they entered in. I really appreciate your help, because I&#039;m new at Web programming and my boss has already sold this application to some customers with a delivery date in a few weeks. He thinks the best way to learn is under pressure. Thanks!

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## You asked for it:

&#060;HTML&#062;&#060;BODY&#062;<BR><BR>&#060;SCRI PT Language=JavaScript&#062;<BR>function formatTwo( num )<BR>{<BR>&nbsp; &nbsp; var sgn = ( num &#060; 0 ) ? "-" : "";<BR>&nbsp; &nbsp; num = Math.round( Math.abs(num * 100) );<BR>&nbsp; &nbsp; if ( num &#060; 9 ) return sgn + "0.0" + num;<BR>&nbsp; &nbsp; if ( num &#060; 99 ) return sgn + "0." + num;<BR>&nbsp; &nbsp; num = sgn + num;<BR>&nbsp; &nbsp; return num.substring(0,num.length-2) <BR>&nbsp; &nbsp; &nbsp; &nbsp; + "." <BR>&nbsp; &nbsp; &nbsp; &nbsp; + num.substring(num.length-2);<BR>}<BR><BR>function doCalc( yearMonth )<BR>{<BR>&nbsp; &nbsp; var frm = document.BudgetForm;<BR>&nbsp; &nbsp; var elems = frm.elements;<BR>&nbsp; &nbsp; var priorVal = <BR>&nbsp; &nbsp; &nbsp; &nbsp; parseFloat( elems["Prior"+yearMonth].value );<BR>&nbsp; &nbsp; var budget = elems["Budget"+yearMonth];<BR>&nbsp; &nbsp; var dollar = elems["Dollar"+yearMonth];<BR>&nbsp; &nbsp; var percent = elems["Percent"+yearMonth];<BR>&nbsp; &nbsp; var val = budget.value;<BR>&nbsp; &nbsp; if ( isNaN(val) )<BR>&nbsp; &nbsp; {<BR>&nbsp; &nbsp; &nbsp; &nbsp; alert("The value you entered is not a valid number.");<BR>&nbsp; &nbsp; &nbsp; &nbsp; budget.value = "";<BR>&nbsp; &nbsp; &nbsp; &nbsp; dollar.value = "";<BR>&nbsp; &nbsp; &nbsp; &nbsp; percent.value = "";<BR>&nbsp; &nbsp; &nbsp; &nbsp; budget.focus();<BR>&nbsp; &nbsp; &nbsp; &nbsp; return;<BR>&nbsp; &nbsp; }<BR>&nbsp; &nbsp; val = parseFloat(val);<BR>&nbsp; &nbsp; dollar.value = "\$" + formatTwo( val - priorVal );<BR>&nbsp; &nbsp; percent.value = <BR>&nbsp; &nbsp; &nbsp; &nbsp; formatTwo( 100.0 * ( val - priorVal ) / priorVal ) + "%";<BR>}<BR><BR>&#060;/SCRIPT&#062;<BR><BR>&#060;FORM Name="BudgetForm"&#062;<BR>&#060;TABLE Border=1&#062;<BR>&#060;TR&#062;<BR>&nbsp; &nbsp; &#060;TD Align=Right&#062;Month&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;January&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;February&#060;/TD&#062;<BR>&#060;/TR&#062;<BR>&#060;TR&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;2001 Budget&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;INPUT Name=Prior01 Value="37103.95"<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onFocus="this.form.Budget01.focus();"&#062;&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;INPUT Name=Prior02 Value="97331.22"<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onFocus="this.form.Budget02.focus();"&#062;&#060;/TD&#062;<BR>&#060;/TR&#062;<BR>&#060;TR&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;2002 Adjusted Budget&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;INPUT Name=Budget01 Value="" onChange="doCalc(&#039;01&#039;);"&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;INPUT Name=Budget02 Value="" onChange="doCalc(&#039;02&#039;);"&#062;<BR>&#060;/TR&#062;<BR>&#060;TR&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;2002 \$ Variance&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;INPUT Name=Dollar01 Value=""<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onFocus="this.form.Budget01.focus();"&#062;&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;INPUT Name=Dollar02 Value=""<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onFocus="this.form.Budget02.focus();"&#062;&#060;/TD&#062;<BR>&#060;/TR&#062;<BR>&#060;TR&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;2002 % Variance&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;INPUT Name=Percent01 Value=""<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onFocus="this.form.Budget01.focus();"&#062;&#060;/TD&#062;<BR>&nbsp; &nbsp; &#060;TD&#062;&#060;INPUT Name=Percent02 Value=""<BR>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; onFocus="this.form.Budget02.focus();"&#062;&#060;/TD&#062;<BR>&#060;/TR&#062;<BR>&#060;/TABLE&#062;<BR>&#060;/FORM&#062;<BR>&#060;/BODY&#062;&#060;/HTML&#062;<BR><BR>

5. Junior Member
Join Date
Dec 1969
Posts
28