Complicated nested IF statement instructions needed

[rsbisa]

Hello! I'm completely new to forms and JavaScript. I'm struggling to recreate JavaScript IF/THEN statement that is to calculate the following in a single form field "Employer Match" (formatted as Number, Currency, US dollar sign). Employer Match gets its values from two user input fields, "Annual Income" (formatted Number, Currency, US Dollar sign), and, "401k Contribution" (format Number, Currency, US Dollar sign; validation 0 to 18000).

Here's the challenge:

Employer Match = Equal to $1 of every $1 of 401k Contribution up to 3% of Annual Income and .5 (50 cents) of every $1 of up to the remainder 2% of Annual Income.

I'm able to do this easy enough in Excel. But, JavaScript, not so much.

So,

1. Into Annual Income form field, User types their annual income, say, $80,000
2. Into 401k Contribution form field, user types an amount, say, $6,000 (or, 7.5% of Income); or for our purposes any other amount greater than 3% of Income, but less than $18,000
3. The calculation in Employer Match field needs to determine if 401k Contribution is greater than 3% of Income or not.
   1. If 401k Contribution < (Annual Income * .03), then Employer Match = 401k Contribution
   2. If 401K Contribution > (Annual Income * .03), then Employer Match = (((401k Contribution - (Annual Income * .03)) + (Annual Income * .02) * .5))

In the above instance, of the $6,000 401k Contribution, $2,400 (3% of Annual Income) would be matched $1 to $1, and of the remainder $3,600, only $1,600 (2% of Annual Income) would be matched at .50 per $1 (or, $800). So, Employer Match = $3,200. Note, that if user had typed, say, $3,000 into 401k Contribution, where $2,400 is 3% of Annual Income, the remainder $600 would be matched at .50 cents, or $300. So, Employer Match = $2,400 + 300, or $2,700.

I'm looking for a JavaScript complex calculation that does the above. Hope this makes sense.

Any help greatly appreciated.

[George_Johnson]

What should the calculated value be if either the annual salary or 401K contribution are blank?

[rsbisa]

George, thanks for asking. The default values of Annual Income and Employer Match are both 0. So the calculated value is 0 if either field is untouched or left blank.

[George_Johnson]

Some of what you post isn't consistent, so it might be best if you posted the Excel formula that you're using.

[rsbisa]

George, hope this works,

=IF(C4<(C3*0.05),C4,((IF(C4>(C3*0.05),C3*0.05,C4)-(C3*0.03))*0.5)+(C3*0.03))

C4 = 401k Contribution ($6,000, per my example)

C3 = Annual Income ($80,000, per my example)

First we test if the 401k Contribution is > than 3% of Annual Income =IF(C4<(C3*0.05),C4. If not, we simply match 100% of the contribution. But, if the 401k Contribution is > than 3% of annual income, we multiply the difference between 3% and 5% by .5, and then add 3% of Annual Income to the result.

The 18000 maximum contribution limit is actually not relevant. We only need concern ourselves with 5% of Annual Income, as this is the matchable amount. My form won't allow a contribution greater than 18000.

There is probably a cleaner way to write that formula. But, did it on the fly.

[George_Johnson]

> First we test if the 401k Contribution is > than 3% of Annual Income =IF(C4<(C3*0.05)

That formula is calculating 5%, not 3%, so I'm still unsure what's correct.

[rsbisa]

Thank you for your patience! I did make a mess of that last effort.

Here is the updated and [better] proofed and reproofed formula.

=IF(A2<(A1*0.03),A2,((A2-(A1*0.03))*0.5)+(A1*0.03))

A1 = Annual Income

A2 = 401k Contribution

• Test if 401k Contribution is < than 3% of Annual Income. If TRUE, calculated value is equal to 401k Contribution
• If FALSE, calculated value equals ((401k Contribution - (Annual Income * 3%)) * .5) + (Annual Income * 3%)

In short, If the contribution is more than 3% of income, we half difference of the contribution and 3% of income and add back  the 3% of income.

I really appreciate your patience!

[rsbisa]

After sleep, I came up with this. Seems to work.

//A1 = Annual Income

//A2 = 401k Contribution

var A1 = getField("Income").value;

var A2 = getField ("401(k)").value;

var A3 = A1*.03;

var A4 = A1*.05;

var A5 = A1*.02;

//Contribution less than 3% of income?

if(A2<(A1*0.03)) event.value = A2;

//Contribution is less than 5% of income?

else if (A2<A4) event.value=((A2-A3)*.5)+A3;

//Contribution is greater than 5% of income

else if (A2>A4) event.value= (A3)+(A5*.5);

[rsbisa]

I was concerned about that. I will post my Excel workup in a few hours when I'm at my desk.

Thank you for looking into this.