ExcelBanter - View Single Post

:
can you explain CEILING to me, please? I am trying to do the second
formula, and since the limit on compensation is 200000, thought ths would
work...it doesn't.:

IF(AND(DATEDIF(B1,"12/31,2004","y")<21,J1=1000),0,IF(OR(K1=Y,IF(AND(G1< =90000),0,INT(MIN(G1*.03+G!.087))OR(IF(G1=90000), CEILING(g1=200000)MAX(G1*.03*+G1*.087),0))))

--
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).