Using lookup correct rate using age to calculate

New Here ,
Jul 01, 2017

Copy link to clipboard

Copied

I need help with a form I am putting together.

The formula i use on excel is:

=IF(AGE>70,0,AmountSelected*VLOOKUP(AGE,RatesBelow$A$15:$E$69,IF(GENDER="Male",1,2),FALSE) /1000)*(EDATE(STARTDATE,12)-TODAY())/365)))))

the table i have for the ages and rates is as below:

Age     Male      Female

18       0.31        0.17

19       0.43        0.21

20       0.49        0.21

and so on and so forth...

so if the user selects an amount of 10,000 and the user is an 18yr old male then => (10,000(18Male=0.31)/1000=3.1) *(01/06/17-30/06/17)/365)

(3.1) *(43252-42916)/365)

(3.1 *336)/365)

(1041.6/365)

=2.85

How do I translate the above into javascript?

Most Valuable Participant
Correct answer by try67 | Most Valuable Participant

The latter option is quite a bit more complicated, as I mentioned. I can explain how to do it in general terms, but if you want me to write the code for you we should discuss it privately.

The former option requires a lot of if-else conditions. Something like this:

event.value = "";

var age = Number(this.getField("Age").value);

var gender = this.getField("Gender").valueAsString;

if (age<=18) {

    if (gender=="Male") event.value = 0.31;

    else if (gender=="Female") event.value = 0.17;

} else if (age<=19) {

    if (gender=="Male") event.value = 0.43;

    else if (gender=="Female") event.value = 0.21;

} else if (age<=20) {

    if (gender=="Male") event.value = 0.49;

    else if (gender=="Female") event.value = 0.21;

} // etc.

TOPICS
Acrobat SDK and JavaScript

Views

218

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Using lookup correct rate using age to calculate

New Here ,
Jul 01, 2017

Copy link to clipboard

Copied

I need help with a form I am putting together.

The formula i use on excel is:

=IF(AGE>70,0,AmountSelected*VLOOKUP(AGE,RatesBelow$A$15:$E$69,IF(GENDER="Male",1,2),FALSE) /1000)*(EDATE(STARTDATE,12)-TODAY())/365)))))

the table i have for the ages and rates is as below:

Age     Male      Female

18       0.31        0.17

19       0.43        0.21

20       0.49        0.21

and so on and so forth...

so if the user selects an amount of 10,000 and the user is an 18yr old male then => (10,000(18Male=0.31)/1000=3.1) *(01/06/17-30/06/17)/365)

(3.1) *(43252-42916)/365)

(3.1 *336)/365)

(1041.6/365)

=2.85

How do I translate the above into javascript?

Most Valuable Participant
Correct answer by try67 | Most Valuable Participant

The latter option is quite a bit more complicated, as I mentioned. I can explain how to do it in general terms, but if you want me to write the code for you we should discuss it privately.

The former option requires a lot of if-else conditions. Something like this:

event.value = "";

var age = Number(this.getField("Age").value);

var gender = this.getField("Gender").valueAsString;

if (age<=18) {

    if (gender=="Male") event.value = 0.31;

    else if (gender=="Female") event.value = 0.17;

} else if (age<=19) {

    if (gender=="Male") event.value = 0.43;

    else if (gender=="Female") event.value = 0.21;

} else if (age<=20) {

    if (gender=="Male") event.value = 0.49;

    else if (gender=="Female") event.value = 0.21;

} // etc.

TOPICS
Acrobat SDK and JavaScript

Views

219

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 01, 2017 0
Most Valuable Participant ,
Jul 01, 2017

Copy link to clipboard

Copied

There are a couple of options of doing it. You can hard-code the lookup table into the code itself, or you can use an external data source (like an attached text file, or even a hidden text field) to read it from and then parse it.

The former requires a more simple script, but more code (as you have to manually type in everything). The latter requires a more complex code, but is easier to maintain and update...

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 01, 2017 0
New Here ,
Jul 01, 2017

Copy link to clipboard

Copied

Thanks try67!

How do i go about doing both? I am very interested in learning both options.

Would you be able to mock up a script for me to follow?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 01, 2017 0
Most Valuable Participant ,
Jul 01, 2017

Copy link to clipboard

Copied

The latter option is quite a bit more complicated, as I mentioned. I can explain how to do it in general terms, but if you want me to write the code for you we should discuss it privately.

The former option requires a lot of if-else conditions. Something like this:

event.value = "";

var age = Number(this.getField("Age").value);

var gender = this.getField("Gender").valueAsString;

if (age<=18) {

    if (gender=="Male") event.value = 0.31;

    else if (gender=="Female") event.value = 0.17;

} else if (age<=19) {

    if (gender=="Male") event.value = 0.43;

    else if (gender=="Female") event.value = 0.21;

} else if (age<=20) {

    if (gender=="Male") event.value = 0.49;

    else if (gender=="Female") event.value = 0.21;

} // etc.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 01, 2017 0
New Here ,
Jul 01, 2017

Copy link to clipboard

Copied

Thanks Try67! I think i can follow that!

How would i incorporate this formula?

=IF(AGE>70,0,AmountSelected*VLOOKUP(AGE,RatesBelow$A$15:$E$69,IF(GENDER="Male",1,2),FALSE) /1000)*(EDATE(STARTDATE,12)-TODAY())/365)))))

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 01, 2017 0
Most Valuable Participant ,
Jul 01, 2017

Copy link to clipboard

Copied

Isn't that what I just answered?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 01, 2017 0
jaimec77 LATEST
New Here ,
Jul 01, 2017

Copy link to clipboard

Copied

Oh yup! Thank you again!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 01, 2017 0