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?
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<=
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...
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?
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.
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)))))
Copy link to clipboard
Copied
Isn't that what I just answered?
Copy link to clipboard
Copied
Oh yup! Thank you again!