Good Morning,

I wanted to see if anyone can assist me or give advice on a javascript issue i'm having. I've looked at this for hours and can't seem to get it work without issues. I've attempted to make sure everything is in sequence and still can't get it right. The interesting thing is I can get things to work correctly but if I change a number that it doesn't change the calculation. I don't have the knowledge to over come this. I would be grateful to anyone that can help.

I will be as detailed as possible. This is a spreadsheet that I’m working on. I cannot change the layout of the form itself. I have Columns D, E, F, H, I and J. Columns D, E, H and I are all non-calculation cells that impact Columns F (Column is a multiplier of .015 based on results from other cell) and J. All of the Cells in the spreadsheet are labeled L and numbered in order. So, first row is L16 (Column D), L17 (Column E), L18 (Column F), L20 (Column H), L21 (Column I) and L22 (Column J) (note L19 is a field that does not impact any of the calculations. The second row is L23, L24, L25, L27, L28 and L29. This works all the way down. There are 17 rows and each column totals all the way down. Column E is important because if no entry is made in this column then Column F should be $0.00.

Column J will is always going to net a result of Column D minus Column H minus Column I. Here is the javascript I wrote for this.

var theField = this.getField("L16");

var theValue = theField.value;

var theField2 = this.getField("L20");

var theValue2 = theField2.value;

var theField3 = this.getField("L21");

var theValue3 = theField3.value;

if (theValue > .01) {

this.getField("L22").value= +theValue - (+theValue2 + +theValue3) ;

}

else {

this.getField("L22").value="";

}

I also wrote a custom script validation:

if (event.value == 0) event.value = '';

The first scenario and most basic entry in the spreadsheet is an entry $10,000 is made in Column D then Column F should be $0.00 and Column J should be $10,000. Column F should be $0. My javascript is for Column F:

var theField = this.getField("L17");

var theValue = theField.value;

var theField2 = this.getField("L22");

var theValue2 = theField2.value;

if (+theValue > 0) {

this.getField("L18").value = (+theValue2 * .015);

}

else {

this.getField("L18").value = 0;

}

I did not write a custom validation

The next scenario is if I enter the same $’s above but add a number to Column E then F should populate Column D multiplied by $0.015 netting a result of $150. This works!

The next scenario is if I enter $5,000 in Column H, Column J does reduce correctly down to $5,000 however Column F does not readjust down to $75.00. Instead it stays $150.

Interestingly, if I go and enter $2,500 in column I then Column F reduces down to $75. The result should be $37.50.

Any help with how to write this I would be so grateful.

Sincerely,

Steve