Skip to main content
J
jaimec77
Participating Frequently
July 1, 2017
Answered

Using lookup correct rate using age to calculate

  • July 1, 2017
  • 2 replies
  • 840 views

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?

This topic has been closed for replies.
Correct answer try67

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.

2 replies

J
jaimec77Author
Participating Frequently
July 1, 2017

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
Community Expert
try67Community Expert
Community Expert
July 1, 2017

Isn't that what I just answered?

J
jaimec77Author
Participating Frequently
July 1, 2017

Oh yup! Thank you again!

try67
Community Expert
try67Community Expert
Community Expert
July 1, 2017

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

J
jaimec77Author
Participating Frequently
July 1, 2017

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
Community Expert
try67Community ExpertCorrect answer
Community Expert
July 1, 2017

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.
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices