-
1. Re: Complicated nested IF statement instructions needed
George_Johnson Sep 28, 2016 8:44 AM (in response to rsbisa)What should the calculated value be if either the annual salary or 401K contribution are blank?
-
2. Re: Complicated nested IF statement instructions needed
rsbisa Sep 28, 2016 1:28 PM (in response to George_Johnson)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.
-
3. Re: Complicated nested IF statement instructions needed
George_Johnson Sep 28, 2016 2:15 PM (in response to rsbisa)Some of what you post isn't consistent, so it might be best if you posted the Excel formula that you're using.
-
4. Re: Complicated nested IF statement instructions needed
rsbisa Sep 28, 2016 2:24 PM (in response to 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.
-
5. Re: Complicated nested IF statement instructions needed
rsbisa Sep 28, 2016 3:16 PM (in response to George_Johnson)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.
-
6. Re: Complicated nested IF statement instructions needed
George_Johnson Sep 28, 2016 4:19 PM (in response to rsbisa)> 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.
-
7. Re: Complicated nested IF statement instructions needed
rsbisa Sep 28, 2016 7:25 PM (in response to George_Johnson)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!
-
8. Re: Complicated nested IF statement instructions needed
rsbisa Sep 29, 2016 6:15 AM (in response to George_Johnson)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);