If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
formula for a pension
"barbarat" wrote:
comparing my formula to yours, i see that i am unsure of commas, and )'s. and the rules for what comes first...is there a rule i could learn, and if so where do i look for it? meaning, what should i reference in the manual index? I doubt that your problem is with understanding "commas and parentheses [and] what comes first". But if that truly is your problem, the best thing is to rely on the Help text for each function. I find the "offline" text better than the online text because of the See Also link. If you agree, click on Help, then click on Online Content Settings in the Help window. Uncheck "Content and links from Microsoft Office Online". After clicking Okay, you will have to exit and re-enter Excel to see the effect. The See Also link might help you with what I suspect is the real problem: how to know what functions and operators exist. If you look at the See Also link on the Help text for the "IF worksheet function", you will discover the AND() and OR() functions, for example. Careful reading of the syntax and a general knowledge about how function arguments and expressions work in programming languages will help you get it right. Sorry, but I don't know of any other magic way to learn a "language" -- which Excel expressions are -- other than finding a good intro book. the date thing really confuses me. Yes, it took me some time to remember DATEDIF() myself. I did not find any Excel documentation. I found it with a google search. if i wanted to use numerical, like 01011984 instead of mm/dd/yyyy, what would i need to change? I believe I answered that in an earlier posting in this thread. Look for it. It isn't pretty. Why do you prefer the "numerical" format? What problem does that solve for you that the normal format does not? |
#22
|
|||
|
|||
formula for a pension
"barbarat" wrote:
you gave me this, which did just what i wanted: =IF(OR([...]),0,ROUND([...],2)) now, trying to add a limit to it that all contributions (employee, match and profit share, cap at 40000, and the reduction is in the profit share. It really is easier (for me) to work with the MIN() form. I believe the answer to you question is simply: MIN(40000, ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2)) You could do something similar with the IF() solution. That is just a cap on profit sharing. But with profit sharing, there is (should be) no employee contribution or match. So I might have misunderstood your question. Are you really trying to refer back to the original pension problem? Are you trying to put a cap on the sum of the profit sharing and pension benefits (employee's contribution and company's match)? That requires more changes to do it right. For example, H1 contained the employee contribution. You will need to add MIN(40000,...) to that column. You had said that the company match would go into I1. You will need to add MIN(40000-H1,...) to that formula. Finally, you will need to add MIN(40000-H1-I1,...) to the profit sharing formula. Although I believe those changes ensure that the sum does not exceed 40000, frankly I think it is strange. As I wrote it, it allows the employee to contribute $40,000, which has the effect of eliminating any company match or profit sharing. Since there is usually a philosophy associated with profit sharing at least (e.g, motivation), you probably do not want to eliminate it completely simply because an employee was over-zealous in the amount of his/her own contribution to the 401(k). Bottom line: perhaps we need a more precise statement of what you intend with regard to these benefits in order to give you a solution. You might also want to study federal law on the subject, if you haven't already, since I believe it addresses how some or all of these benefits interact. |
#23
|
|||
|
|||
formula for a pension
Hi again:
The only way to approach is at the reducing profit sharing. Employees are limited to 14000 contribution (15000 as of next year) by IRS regs. Our match can be as generous as we like...1%, 100%, whatever...but since an employee is at most 14000, I think 26000 total match from an employer is a pretty good incentive! Especially since the matched part is tax deferred. And since even if they contribute less, they can still get a larger profit share to reach 40K, which is why you reduce the profit sharing, which is taxed at today's higher tax rate. I don't want to think about how to adjust fo the change again in July! (Fiscal year). I think I can just replace the 40000 with 41000, but later on that! I do fine with small formulas, but a calculator has always been my tool. And programs that do the formulas, and you just give it the numbers in the blanks...but I am really liking Excel. I have started exploring, and am amazed at what it does. I found statistical functions, which is great! Those I found I can do easily... it is similar to how Fathom works. But I will get there. Thanks so much for all the help. This board has been the greatest tool I have found since I bought Excel (Office). Barbara -- barbarat " wrote: "barbarat" wrote: comparing my formula to yours, i see that i am unsure of commas, and )'s. and the rules for what comes first...is there a rule i could learn, and if so where do i look for it? meaning, what should i reference in the manual index? I doubt that your problem is with understanding "commas and parentheses [and] what comes first". But if that truly is your problem, the best thing is to rely on the Help text for each function. I find the "offline" text better than the online text because of the See Also link. If you agree, click on Help, then click on Online Content Settings in the Help window. Uncheck "Content and links from Microsoft Office Online". After clicking Okay, you will have to exit and re-enter Excel to see the effect. The See Also link might help you with what I suspect is the real problem: how to know what functions and operators exist. If you look at the See Also link on the Help text for the "IF worksheet function", you will discover the AND() and OR() functions, for example. Careful reading of the syntax and a general knowledge about how function arguments and expressions work in programming languages will help you get it right. Sorry, but I don't know of any other magic way to learn a "language" -- which Excel expressions are -- other than finding a good intro book. the date thing really confuses me. Yes, it took me some time to remember DATEDIF() myself. I did not find any Excel documentation. I found it with a google search. if i wanted to use numerical, like 01011984 instead of mm/dd/yyyy, what would i need to change? I believe I answered that in an earlier posting in this thread. Look for it. It isn't pretty. Why do you prefer the "numerical" format? What problem does that solve for you that the normal format does not? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Hide formula | skateblade | Worksheet Functions | 10 | October 15th, 2005 08:36 PM |
adding row to forumla | carrera | General Discussion | 9 | August 23rd, 2005 10:24 PM |
Formula checking multiple worksheets | sonic-the-mouse | Worksheet Functions | 11 | June 6th, 2005 06:37 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 08:51 PM |