4 Replies Latest reply on Jan 19, 2012 1:22 PM by chrisjones1120

# Excel spreadsheet with formula's that needs to be in PDF format, Help with Javascript commands.

I have a field that has a value of Gross amount of loss.

When a number is entered then the service amount is calculated with the formula below.

=IF(F21<1,MAX(P3:P20),MAX(Q3:Q4))

It refers to the chart below..

so if the Gross amount of loss is less then 500 then the service amount is 148

if it's less than 2500 the service amount is 1,290

and so on.

 0-500 =IF(I21>0,148) =IF(F21=1,90) 500-2500 =IF(I21>500.01,290) =IF(F21=2,125) 2500-5000 =IF(I21>2500.01,420) 5000-10000 =IF(I21>5000.01,585) 10000-20000 =IF(I21>10000.01,790) 20000-50000 =IF(I21>20000.01,965) 50000-100000 =IF(I21>50000.01,1640) over 100000 =IF(I21>100000.01,I21*0.024)

Can i create a script that basically does the same thing?

• ###### 1. Re: Excel spreadsheet with formula's that needs to be in PDF format, Help with Javascript commands.

Yes, but JavaScript is an object orientated language so one needs to identify the object to processed and then a property of method of that object to process. JavaScript also provide more program control options. You will need field names.

Assuming the value you base your results on is a field named "GrossLoss", then for the "Custom Calculation Script" for the field for the service fee could be:

var ServiceAmount = 0; // amount of service amount default

var nGrossLoss = this.getField("GrossLoss").value); // get the value of the gross loss

// chose service amount based on the gross loss amount

switch(true) {

case (nGrossLoss > 100000) :

// greater than 100,000

ServiceAmount = nGrossLoss * 0.024;

break;

case(nGrossLoss > 50000 && nGrossLoss <= 100000):

// greater than 50,000 and less than or equal to 100,000

ServiceAmount = 1640;

break;

case(nGrossLoss >20000 && nGrossLoss <= 50000):

// greater then 20,000 and less than or equal to 50,000

ServiceAmount = 965;

break;

case(nGrossLoss > 10000 && nGrossLoss <= 20000):

ServiceAmount = 790;

break;

case(nGrossLoss > 5000 && nGrossLoss <= 10000):

ServiceAmount = 585;

break;

case(nGrossLoss > 2500 && nGrossLoss <= 5000):

ServiceAmount = 420;

break;

case(nGrossLoss > 500 && nGrossLoss <= 2500):

ServiceAmount = 290;

break;

case(nGrossLoss > 0 && nGrossLoss <= 500):

less than or equal to 500

ServiceAmount = 148;

break;

default:

// all other values

ServiceAmount = 0;

break;

} // end selection

event.value = ServiceAmount;

MDN JavaScript Reference 1.5

• ###### 2. Re: Excel spreadsheet with formula's that needs to be in PDF format, Help with Javascript commands.

ok, i try that and go to my service Amt field and get a syntaxError: missing; before statement 3 at line 4

What could be the issue?

• ###### 3. Re: Excel spreadsheet with formula's that needs to be in PDF format, Help with Javascript commands.

var nGrossLoss = this.getField("GrossLoss").value); // get the value of the gross loss

should be:

var nGrossLoss = this.getField("GrossLoss").value; // get the value of the gross loss

• ###### 4. Re: Excel spreadsheet with formula's that needs to be in PDF format, Help with Javascript commands.

Thank you that did it.