4 Replies Latest reply on Feb 16, 2012 12:41 PM by me@gbsi

# I need help converting an excel formula to javascript for a fillable form.

Here is the scenario,

If a person travels for 1 or 2 days we pay out a mealsrate of 75% per day. If 3 or more days we pay # days less 2 days * 25%. I hope that makes sense.

V7 = Days, F14 = Mealsrate

Formula in Excel

=IF(\$V\$7 = 1,\$F\$14*0.75,(\$V\$7*\$F\$14)-((\$F\$14*0.25)*2))  and it works.

I am VERY NEW to javascript (like yesterday) so don't laugh. I really have no idea what I am doing, but I was trying. Here is what i got. (by the way, It doesn't work)

if(this.getField( ("Days").value=="1")){

var f =this.getField("Mealsrate");

event.value= f.value * .75;

}else event.value = (Days * f.value)- (f.value *.25)*2;

I appreciate all the help i can get.

Tracie

I'll be back on Monday, I hope I can find my post.

• ###### 1. Re: I need help converting an excel formula to javascript for a fillable form.

You said "1 or 2 days", but your Excel forumula and JavaScript looks like it only does the 75% if 1 day. Can you clarify what you want?

• ###### 2. Re: I need help converting an excel formula to javascript for a fillable form.

1 day pays @ 75% of mealsrate, 2 days pay @ 75% mealsrate for each day

Travel for 3 days or more, = 1 day @ 100%, 2 days @ 75%

or #days at 100% less 2 days @ 25%.

I hope that helps. The excel formula works, javascript not so good. It needs alot of work.

• ###### 3. Re: I need help converting an excel formula to javascript for a fillable form.

OK, that Excel formula can be reduced a bit. The following custom Calculate scipt will do what the formula does:

```// Custom Calculate script
(function () {

// Get the inputs
var days = getField("Days").value;
var rate = getField("Mealsrate").value;

// Set this field value
if (days == 1) {
event.value = 0.75 * rate;
} else {
event.value = (days - 0.5) * rate;
}
}
```

It would make sense to add additional code to deal with negative numbers in either of the input fields.

Edit: corrected a typo

• ###### 4. Re: I need help converting an excel formula to javascript for a fillable form.

Sorry it has taken me a week to get back to you. Thank you so much for your help. It works great!