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 |
#11
|
|||
|
|||
formula for a pension
what does that mean, please? are you referring to the first formula, and the
INT, or something about the second formula that I need to correct...well, i know it needs correcting; it doesn't work. help please thanks! -- barbarat "Peo Sjoblom" wrote: It is not truncating, check with a negative value -- Regards, Peo Sjoblom (No private emails please) " wrote in message ... "barbarat" wrote: Hello again...yes, I think I understand it. Great. The INT is for rounding, right? Well, for truncating. It will match $1 to $1 through $1.99. If you want to round (match $2 to $1.50 through $2.49), change INT(...) to ROUND(...,0). If you want to match $2 to $1.01 through $2 -- i.e, to any dollar or part thereof -- use ROUNDUP(...,0) or CEILING(...,1). |
#12
|
|||
|
|||
formula for a pension
I meant with regards, to the post saying "Well, for truncating"
it is incorrect, INT rounds down to the nearest integer, TRUNC truncates 5.5 -5.5 INT returns 5 -6 while TRUNC returns 5 -5 my post was not in response to your post having said that without checking your formula if you want to use AND you use it as =IF(AND(DATEDIF(B1,"12/31/2004","y")21,J1=1000),0,etc meaning that if younger than 21 and if J1 is greater or equal to 1000, then return 0, else do this or that -- Regards, Peo Sjoblom (No private emails please) "barbarat" wrote in message ... what does that mean, please? are you referring to the first formula, and the INT, or something about the second formula that I need to correct...well, i know it needs correcting; it doesn't work. help please thanks! -- barbarat "Peo Sjoblom" wrote: It is not truncating, check with a negative value -- Regards, Peo Sjoblom (No private emails please) " wrote in message ... "barbarat" wrote: Hello again...yes, I think I understand it. Great. The INT is for rounding, right? Well, for truncating. It will match $1 to $1 through $1.99. If you want to round (match $2 to $1.50 through $2.49), change INT(...) to ROUND(...,0). If you want to match $2 to $1.01 through $2 -- i.e, to any dollar or part thereof -- use ROUNDUP(...,0) or CEILING(...,1). |
#13
|
|||
|
|||
formula for a pension
|
#14
|
|||
|
|||
formula for a pension
okay...so i tried changig the format in the Date of termination, instead of
Terminated, Y or N, I put 0 if not, and a date (numerical) if yes. still can't make it work. I think I have too many ifs...it keeps saying "to many arguments", but thought we could use 7?? help, please? conditions again: employee must not be terminated on 12/31/04 (now column D), be 21 (DOB column B), worked 1000 hrs (column E), be employed for one year (date of hire is column C) and then up to 90000 is a profit share match of 3%, plus 8.7% of total compensation. no share over 200000 of compensation. I changed my formerly posted formula (which did not work) to numerical for date of termination, and used Ceiling to try to force the 200000 limit. I have tried adding commas, removing commas, adding/removing parentheses...help, please! thanks. -- barbarat "barbarat" wrote: got it...that is going to come in really handy for spreadsheets in the future. I had an IF statement a mile long, and it would not work! so, what if i wanted to do a similar calc; a profit sharing based on 3% of compensation up to 90,000 plus 8.7% of all compensation? no contribution is allocated above $200000, but there are really no employees at that level, anyway. On 12/31/04 they must be 21 yrs old, have worked 1000 hrs for that year and still be employed with the company . would something like this work: hrs worked is column J, and terminated Y/N is K: =IF(DATEDIF(B1,"12/31/2004","y")21,0,ANDIF(J1=1000),0,IF(G1=90000,(G1 *.03+G1*.087)OR(IF(G190000,90000*.03+G1*.087)AND( IFG1=2000000,(90000*.03+200000*.087),0))) please...no laughing. i would love to find out where in this formula i've gone wrong. thanks! -- barbarat " wrote: "barbarat" wrote: Hello again...yes, I think I understand it. Great. The INT is for rounding, right? Well, for truncating. It will match $1 to $1 through $1.99. If you want to round (match $2 to $1.50 through $2.49), change INT(...) to ROUND(...,0). If you want to match $2 to $1.01 through $2 -- i.e, to any dollar or part thereof -- use ROUNDUP(...,0) or CEILING(...,1). |
#15
|
|||
|
|||
formula for a pension
"Peo Sjoblom" wrote:
I meant with regards, to the post saying "Well, for truncating" it is incorrect, INT rounds down to the nearest integer, TRUNC truncates Technically correct. My comment was made in context. The discussion is about compensation etc, which is always positive. The OP said that INT() would "round". I said it "truncates". Arguably, I should have said "rounds down". I was tempted to write that, but I thought "truncate" might be clearer, since it is the same thing for positive numbers. Having said all that, I will try to be more careful in the future, since "int" does mean truncate (the unsigned magnitude) in some programming languages. |
#16
|
|||
|
|||
formula for a pension
i understood that the INT worked to rund because it was positive. i should
have stated that i went to the manual, and under help, so i got what you said. can you help me with the second part? also, i had a thought. on the first formula, could i have used a separate sheet, then filtered out the no's, then done a formula that referenced the prior sheet? or is that more complicated (it's all complicated to me!) the second one, i keep getting error messages, and when i try to get out of the ell, lose what i have entered...help, please -- barbarat " wrote: "Peo Sjoblom" wrote: I meant with regards, to the post saying "Well, for truncating" it is incorrect, INT rounds down to the nearest integer, TRUNC truncates Technically correct. My comment was made in context. The discussion is about compensation etc, which is always positive. The OP said that INT() would "round". I said it "truncates". Arguably, I should have said "rounds down". I was tempted to write that, but I thought "truncate" might be clearer, since it is the same thing for positive numbers. Having said all that, I will try to be more careful in the future, since "int" does mean truncate (the unsigned magnitude) in some programming languages. |
#17
|
|||
|
|||
formula for a pension
"barbarat" wrote:
can you explain CEILING to me, please? Forget about Excel's CEILING() function. It is poorly defined, in my opinion. Besides, I really did mean ROUNDDOWN(...,0), even for negative numbers, although that should not be an issue for you. I am trying to do the second formula, and since the limit on compensation is 200000, thought ths would work...it doesn't.: IF([...] CEILING(g1=200000)MAX(G1*.03*+G1*.087),0)))) I am not exactly sure what you are trying to write. The above is syntactically incorrect. Even if you meant: CEILING(g1=200000,1)*MAX(...) that is non-sensical. CEILING(x,1), in this case, returns the largest integer value of x rounded up, away from zero. The expression "g1 = 200000" is a boolean (true, false), not a number that we would normally apply CEILING() to. |
#18
|
|||
|
|||
formula for a pension
"barbarat" wrote:
so, what if i wanted to do a similar calc; a profit sharing based on 3% of compensation up to 90,000 plus 8.7% of all compensation? no contribution is allocated above $200000 If I understand correctly, that could be written: ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2) That rounds to cents. Replace ",2" with ",0" to round to dollars. On 12/31/04 they must be 21 yrs old, have worked 1000 hrs for that year and still be employed with the company . would something like this work: hrs worked is column J, and terminated Y/N is K: =IF(DATEDIF(B1,"12/31/2004","y")21,0,ANDIF(J1=1000),0,[...] I assume you mean "at least 21 yr old" and "at least 1000 hr". That could be written: IF(OR(DATEDIF(B1,"12/31/2004","y")21, J11000, K1="Y"), 0, ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2)) or equivalently: IF(AND(DATEDIF(B1,"12/31/2004","y")=21, J1=1000, K1="N"), ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2), 0) =IF(DATEDIF(B1,"12/31/2004","y")21,0,ANDIF(J1=1000),0,IF(G1=90000, (G1*.03+G1*.087)OR(IF(G190000,90000*.03+G1*.087)A ND(IFG1=2000000, (90000*.03+200000*.087),0))) please...no laughing. i would love to find out where in this formula i've gone wrong. Lots of syntax errors, too many to comment individually. But there is no harm in wanting to use IF() functions, if that makes the logic clearer to you. I might write: IF(OR(DATEDIF(B1,"12/31/2004","y")21, J11000, K1="Y"), 0, ROUND(IF(G1 = 90000, 11.7%*G1, IF(G1 = 200000, 3%*90000 + 8.7%*G1, 3%*90000 + 8.7%*200000)), 2)) |
#19
|
|||
|
|||
formula for a pension
that worked perfectly! and is much shorter than my sad attempt! after
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? the date thing really confuses me. if i wanted to use numerical, like 01011984 instead of mm/dd/yyyy, what would i need to change? thanks so much for your help. -- barbarat " wrote: "barbarat" wrote: so, what if i wanted to do a similar calc; a profit sharing based on 3% of compensation up to 90,000 plus 8.7% of all compensation? no contribution is allocated above $200000 If I understand correctly, that could be written: ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2) That rounds to cents. Replace ",2" with ",0" to round to dollars. On 12/31/04 they must be 21 yrs old, have worked 1000 hrs for that year and still be employed with the company . would something like this work: hrs worked is column J, and terminated Y/N is K: =IF(DATEDIF(B1,"12/31/2004","y")21,0,ANDIF(J1=1000),0,[...] I assume you mean "at least 21 yr old" and "at least 1000 hr". That could be written: IF(OR(DATEDIF(B1,"12/31/2004","y")21, J11000, K1="Y"), 0, ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2)) or equivalently: IF(AND(DATEDIF(B1,"12/31/2004","y")=21, J1=1000, K1="N"), ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2), 0) =IF(DATEDIF(B1,"12/31/2004","y")21,0,ANDIF(J1=1000),0,IF(G1=90000, (G1*.03+G1*.087)OR(IF(G190000,90000*.03+G1*.087)A ND(IFG1=2000000, (90000*.03+200000*.087),0))) please...no laughing. i would love to find out where in this formula i've gone wrong. Lots of syntax errors, too many to comment individually. But there is no harm in wanting to use IF() functions, if that makes the logic clearer to you. I might write: IF(OR(DATEDIF(B1,"12/31/2004","y")21, J11000, K1="Y"), 0, ROUND(IF(G1 = 90000, 11.7%*G1, IF(G1 = 200000, 3%*90000 + 8.7%*G1, 3%*90000 + 8.7%*200000)), 2)) |
#20
|
|||
|
|||
formula for a pension
I am trying to understand the differences in how i wrote the IFs; you gave me
this, which did just what i wanted: =IF(OR(DATEDIF(B2,"12/31/04","y")21,E21000,D20),0,ROUND(IF(G2=90000,11. 7%*G2,IF(G2=200000,3%*90000+8.7%*G2,3%*90000+8.7% *200000)),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. since this formula is in the column (H) for the profit share, can it be amended to do the reduction? Or do I make a new column, add this result to match (column I), and limit that to 40000? sorry, this is all very new to me. thanks so much for your assistance, Barbara -- barbarat "barbarat" wrote: that worked perfectly! and is much shorter than my sad attempt! after 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? the date thing really confuses me. if i wanted to use numerical, like 01011984 instead of mm/dd/yyyy, what would i need to change? thanks so much for your help. -- barbarat " wrote: "barbarat" wrote: so, what if i wanted to do a similar calc; a profit sharing based on 3% of compensation up to 90,000 plus 8.7% of all compensation? no contribution is allocated above $200000 If I understand correctly, that could be written: ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2) That rounds to cents. Replace ",2" with ",0" to round to dollars. On 12/31/04 they must be 21 yrs old, have worked 1000 hrs for that year and still be employed with the company . would something like this work: hrs worked is column J, and terminated Y/N is K: =IF(DATEDIF(B1,"12/31/2004","y")21,0,ANDIF(J1=1000),0,[...] I assume you mean "at least 21 yr old" and "at least 1000 hr". That could be written: IF(OR(DATEDIF(B1,"12/31/2004","y")21, J11000, K1="Y"), 0, ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2)) or equivalently: IF(AND(DATEDIF(B1,"12/31/2004","y")=21, J1=1000, K1="N"), ROUND(3%*MIN(G1,90000) + 8.7%*MIN(G1,200000), 2), 0) =IF(DATEDIF(B1,"12/31/2004","y")21,0,ANDIF(J1=1000),0,IF(G1=90000, (G1*.03+G1*.087)OR(IF(G190000,90000*.03+G1*.087)A ND(IFG1=2000000, (90000*.03+200000*.087),0))) please...no laughing. i would love to find out where in this formula i've gone wrong. Lots of syntax errors, too many to comment individually. But there is no harm in wanting to use IF() functions, if that makes the logic clearer to you. I might write: IF(OR(DATEDIF(B1,"12/31/2004","y")21, J11000, K1="Y"), 0, ROUND(IF(G1 = 90000, 11.7%*G1, IF(G1 = 200000, 3%*90000 + 8.7%*G1, 3%*90000 + 8.7%*200000)), 2)) |
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 |