Help me please before I lose my mind 😂

[Cjj1984]

Hi, 

I was wondering if anyone could help. I am trying to create a formula that pulls through on to the attached.

I need the monthly payment amounts to calculate based on different rates. As per the table at the bottom of the document. This is based on the dropdown selected. This would then need to follow with a calculation of Cost*thefield/100.

Are you able to help?

Cheers

Chris

[Nesa Nurani]

Sure, we can help if you can describe how exactly calculation should go?

[Cjj1984]

Hi Nesa,

Thanks for replying. The dropdown has 4 options. I would need the calculation to pull the correct base rate from the table at the bottom of the document. This would then calculate the monthly payment based on the cost inputted. This calculation would be the Cost x Rate (in table at foot of document /1000.

Cheers

Chris

[Nesa Nurani]

How do you select which rate (there is 4 rate for every choice)?

[Cjj1984]

Hi Nesa,

Sorry should have explained that better. I have amended the document for clarity. The rates below would be selected depending on the dropdown.

Cheers

Chris

[Nesa Nurani]

You want rates pulled from their field, or are they fixed?

[Cjj1984]

From the field as they change from time to time

Cheers

Chris

[Nesa Nurani · Accepted solution]

Remove other calculations from 'Monthly fields' and use this script as 'Custom calculation script' of dropdown field (Business_Type):

var y2 = this.getField("2year_Monthly");
var y3 = this.getField("3year_Monthly");
var y4 = this.getField("4year_Monthly");
var y5 = this.getField("5year_Monthly");

var r1_2 = Number(this.getField("NewStartU52Y").valueAsString);
var r1_3 = Number(this.getField("NewStartU53Y").valueAsString);
var r1_4 = Number(this.getField("NewStartU54Y").valueAsString);
var r1_5 = Number(this.getField("NewStartU55Y").valueAsString);

var r2_2 = Number(this.getField("NewStartO52Y").valueAsString);
var r2_3 = Number(this.getField("NewStartO53Y").valueAsString);
var r2_4 = Number(this.getField("NewStartO54Y").valueAsString);
var r2_5 = Number(this.getField("NewStartO55Y").valueAsString);

var r3_2 = Number(this.getField("EstU52Y").valueAsString);
var r3_3 = Number(this.getField("EstU53Y").valueAsString);
var r3_4 = Number(this.getField("EstU54Y").valueAsString);
var r3_5 = Number(this.getField("EstU55Y").valueAsString);

var r4_2 = Number(this.getField("EstO52Y").valueAsString);
var r4_3 = Number(this.getField("EstO53Y").valueAsString);
var r4_4 = Number(this.getField("EstO54Y").valueAsString);
var r4_5 = Number(this.getField("EstO55Y").valueAsString);

var cost = Number(this.getField("Cost").valueAsString);

if(event.value == "1000"){
 y2.value = cost*r1_2/1000;
 y3.value = cost*r1_3/1000;
 y4.value = cost*r1_4/1000;
 y5.value = cost*r1_5/1000;}
else if(event.value == "2"){
 y2.value = cost*r2_2/1000;
 y3.value = cost*r2_3/1000;
 y4.value = cost*r2_4/1000;
 y5.value = cost*r2_5/1000;}
else if(event.value == "3"){
 y2.value = cost*r3_2/1000;
 y3.value = cost*r3_3/1000;
 y4.value = cost*r3_4/1000;
 y5.value = cost*r3_5/1000;}
else if(event.value == "4"){
 y2.value = cost*r4_2/1000;
 y3.value = cost*r4_3/1000;
 y4.value = cost*r4_4/1000;
 y5.value = cost*r4_5/1000;}
else{
 y2.value = "";
 y3.value = "";
 y4.value = "";
 y5.value = "";}

[Cjj1984]

Thanks you so much you are an absolute legend. This has worked perfectly.

So much appreciated.

Cheers

Chris

[Cjj1984]

Just one more thing. To calculate the weekly payment I am trying to use the formula 2year_Monthly*12/52

Is this correct? As it doesn't seem to be returning a result!

Cheers

Chris

[Nesa Nurani · Accepted solution]

Remove simplified field notations from weekly fields, rename field "5year-Weekly" to "5year_Weekly", now put this script in any text field as custom calculation script, it will calculate all four weekly fields if you did changes I mentioned above:

for(var i=2; i<=5; i++){
 var month = Number(this.getField(i+"year_Monthly").valueAsString);
 var week = this.getField(i+"year_Weekly");
if(month != 0)
 week.value = month*12/52;
else
 week.value = "";}

[Cjj1984]

Nesa, I am so sorry to be a pain. I am struggling again with the calculation for the Tax Relief and Net Cost After Tax Relief parts of the documents. I have tried using simplified but this is not working.

Tax Relief

I need the below to calculate.

2year_Weekly*104 then I need the box to show 20% of the value calculated

3year_Weekly*156 then I need the box to show 20% of the value calculated

4year_Weekly*208 then I need the box to show 20% of the value calculated

5year_Weekly*260 then I need the box to show 20% of the value calculated

Net Cost After Tax Relief

This is just Tax Relief - Cost

Im so sorry to keep pestering you. I really appreciate your help.

Cheers

Chris

[PDF Automation Station]

The simplified field notation is not working because you can't use field names that start with numbers.  Open the console ( Ctrl + j) and you will see the errors.  You need custom calculation scripts that name the fields, for example:

event.value = this.getField("2year_Weekly").value * 104 * .2;

[Nesa Nurani · Accepted solution]

Simplified field notations are 'sensitive' to field names, which can't start with a number, contain space character...etc, you need to escape it with a backslash, and to show 20% multiply result by 0.2, try this as simplified field notation: \2year_Weekly*104*0.2

[Cjj1984]

Thanks so much that worked perfectly. What would the correct way of inputting the formula for the Net Cost After Tax Relief

Cheers

Chris

[PDF Automation Station]

Simplified field notation field names can't start with numbers and can't contain spaces.  Either don't name the fields starting with a number, or escape the number with backslash, or use a custom calculation script with event.value=this.getField("2year_Weely").value*104*.2;

[Nesa Nurani]

If it's the same as tax relief, just replace 0.2 with 0.8.

[PDF Automation Station]

You can't use a field name that starts with a number in a simplified field notation.  Use the following script in a custom calculation script:

event.value=this.getField(" 2year_Monthly").value*12/52;

[PDF Automation Station]

First, remove all your simplified field notations.  They have errors.  Next, change the export value of "New Business Under £5K+VAT" in the dropdown from 1000 to 1.  This is script a custom calculation script for the 2-year monthly payment field.  You will have to modify it for the other monthly payment fields:

var type=this.getField("Business_Type").value;
var cost=this.getField("Cost").value;
if(type==1)
{event.value=this.getField("NewStartU52Y").value*cost/1000;}
else if(type==2)
{event.value=this.getField("NewStartO52Y").value*cost/1000;}
else if(type==3)
{event.value=this.getField("EstU52Y").value*cost/1000;}
else if(type==4)
{event.value=this.getField("EstO52Y").value*cost/1000;}
else
{event.value=""}