Skip to main content
A
Anonymous
August 16, 2016
Question

Need help with coverting excel if function to Javascript

  • August 16, 2016
  • 2 replies
  • 678 views

I'm working on an expense form where I am having some trouble with figuring out how to code my form to correspond to the excel formulas. I want the GST in the 'Accounting Area' to calculate based on

1. If there is mileage entered above

2. if 'yes' selected in the meal/ent dropdown I want the GST to be 1/2 the actual GST amount.

I hope this makes sense?

The excel equivalent would be as below:

=IF(MEAL/ENT="Yes",1/2*GST/HST,IF(MILEAGE>=1,(TOTAL/1.05)*5%,GST/HST))

Thanks a million in advance!!

This topic has been closed for replies.

2 replies

A
Anonymous
August 18, 2016

I have fixed it but I am still having trouble, the GST box doesn't show anything at this point. I don't know what I am doing wrong, this seems like it should work but it just wont and I'm so frustrated because I have literally spent days doing trial and error on this and getting nowhere. Am I missing something here? Does this make sense? Would anyone be willing to look at a sample? Its a calculation based on whether mileage or a meal is selected in the top portion, the bottom portion is the accounting entry part.

var total = this.getField("TOTAL.0").value;

var subtotal = this.getField("SUBTOTAL0").value;

var mileage = this.getField("MILEAGE.0").value;

var meals = this.getField("MEALS0").value;

var gst = this.getField("GST/HST.0").value;

if( mileage > 1) event.value = ((mileage*0.51) /1.05) - total;

if( mileage < 1) event.value = "";

else if( meals == "YES" ) event.value = total/.5;

else if( meals == "NO" ) event.value = gst;

else event.value = gst;

try67
Community Expert
try67Community Expert
Community Expert
August 18, 2016

Your if-else statements are messed up. You need to clearly define which value to use in each situation and then build the logic based on that.

A
Anonymous
August 18, 2016

I have no idea how to do that, I don't know anything about JavaScript or accounting, so I'm learning as I go here. In my original example I have both items on the same line, so the GST is conditional based on if a number is put in mileage OR if there is an amount put in the receipt total and it is defined as a meal or not. In my mind this looks like the right order, can you give me an example of how its wrong?

I appreciate the help, but I have no clue how to go about fixing it hence this forum post.

try67
Community Expert
try67Community Expert
Community Expert
August 17, 2016

See: https://acrobatusers.com/tutorials/conditional-execution

https://acrobatusers.com/tutorials/how-to-do-not-so-simple-form-calculations

A
Anonymous
August 17, 2016

Following this the best I can, I have come up with the following, but it doesnt work. Can anyone help me with where I have gone wrong?

var mileage = this.getField("MILEAGE.0").value;

var meals = this.getField("MEALS/ENT.0").value;

var gst = this.getField("GST/HST.0").value;

if( meals = "yes" ) event.value = gst / 0.50;

else if( mileage > 1) event.value = (total / 1.05)*0.05;

else event.value = gst;

Thanks again.

try67
Community Expert
try67Community Expert
Community Expert
August 17, 2016

Pretty good... I see one mistake, though. The comparison operator in JS is "==", so change this line:

if( meals = "yes" )

To:

if( meals == "yes" )

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices