Using lookup correct rate using age to calculate

[jaimec77]

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?

[try67]

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...

[jaimec77]

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?

[try67 · Accepted solution]

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.

[jaimec77]

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)))))

[try67]

Isn't that what I just answered?

[jaimec77]

Oh yup! Thank you again!