"Lisa" wrote:
I have 2 files with similar formulas. Now i need both those formulas
to be in one cell. I am getting and error

Since you did not show us how you are trying to combine the formulas it is
difficult to say exactly what __your__ error is.

But when __I__ try to combine the formula one way, I get an error because it
exceeds the limit of 7 nested functions (8 including the outermost function)
in Excel 2003. The combined formula, as I did it, would have 9 nested
functions (10 including the outermost function).

I tried to combine the two formulas by dropping "$F6))" at the end of
Formula1 and pasting in Formula2 without the "=" at the beginning, adding
"))" at the end to balance the new logic.

(It would be easier to see, but it would get mangled if I tried to paste it
here.)

By the way, I suspect Formula2 has a defect. Near the end, you have
IF6($J6=10,CEILING($J6+0.01,0.5)-0.01). Note that there is no "false
value" part. It will return FALSE if $J6<10, which is usually undersirable.

I also believe the combined formula would have other defects. For example,
in Formula1, you treat H6 as text, whereas in Formula2, you treat H6 as
numeric.


----- original message -----

"Lisa" wrote in message
...
I have 2 files with similar formulas. Now i need both those formulas to be
in one cell. I am getting and error for the
+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0" part of the formula.
Below are the 2 folrmulas.

Formula 1:
=IF(H6="4",IF($J6<=50,CEILING(($J6)+0.01,1)-0.01,IF(AND($J6=50.01,$J6<=100),(ROUND($J6/5,0)*5)-0.01,IF(AND($J6100.01,$J6<=500),CEILING(($J6)+0.0 1,10)-0.01,IF($J6=500.01,CHOOSE(VLOOKUP($J6,Tbl_Categor y,2,TRUE),MAX(INT($J6-0.49)+0.99),MAX(INT(($J6-2.49)/5)*5+4.99),MAX(INT(($J6-4.99)/10)*10+9.99),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category4,2,TRUE ),INT($J6/100)*100+VLOOKUP(MOD($J6,100),Tbl_Category5,3,TRUE ),CEILING($J6+0.01,50)-0.01),"")))),IF(H6="3",IF(J6<200,INT(J6)+IF(RIGHT( F6*100,2)="00",1,RIGHT(F6*100,2)/100),(INT(J6/10)*10)+RIGHT(F6*100,3)/100),$F6))

Formula 2:
=IF($H6=1,IF($J6<=1,$J6,IF(AND($J61,$J6<=1.05),0. 99,IF(AND($J6=1.06,$J6<=1.09),1.09,IF(AND($J6=1. 1,$J6<=9.99)+IF(RIGHT((ROUNDUP($J6,2))*100,1)="0", ROUNDUP($J6,1)+0.09,IF(RIGHT($J6,2)*100<=9,19,(ROU NDUP($J6*100,-1)-1)/100)),IF($J6=10,CEILING($J6+0.01,0.5)-0.01))))),$F6)

--
lmo