Hi Experts,
Is there a way to integrate the following formula inside the EXL2K FORMULA for FLD6...
-*=IF(ROUND(J29-J30,2)=0,"In Balance", "Not in Balance")
I have tried in different way, But getting the cell error...

  
DEFINE FILE CAR
THIRDVAL/P10.2=500.00;
FLD1/A200='<tr><TD>Needs:</TD><TD></TD><TD></TD><TD>0</TD><td>0</td></tr>';
FLD2/A200='<TR><TD>Availability</TD>';
FLD3/A200='<TD></TD><TD></TD>';
FLD4/A250='<TD x:fmla="=OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-3,0)-OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-3,1)+OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-1,0)">0</TD>';
FLD5/A250='<TD x:fmla="=OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),0,-1)-OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-3,1)+OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-1,0)">0</TD>';
FLD6/A250='<TD x:fmla="=IF(ROUND(OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),0,-1)-OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),0,-2),2)=0,True,False)">0</TD></tr>';

-*=IF(ROUND(J29-J30,2)=0,"In Balance", "Not in Balance")

END

TABLE FILE CAR
PRINT RETAIL_COST DEALER_COST THIRDVAL
BY COUNTRY
BY CAR RECOMPUTE MULTILINES
BY MODEL
WHERE COUNTRY EQ 'ITALY'
ON TABLE PCHOLD FORMAT EXL2K FORMULA
FOOTING
"<FLD1<FLD2<FLD3<FLD4<FLD5<FLD6"
END

This message has been edited. Last edited by: Kerry,

Don't know much about this stuff, but I do know that IF statements in excel should be separated by ';' and not by ','. Maybe that's your problem?

I have confused the question Gamp, Let me rephase it, If i replace the True and False as "In balance" and "Not In balance " i am getting an error...

  

DEFINE FILE CAR
THIRDVAL/P10.2=500.00;
FLD1/A200='<tr><TD>Needs:</TD><TD></TD><TD></TD><TD>0</TD><td>0</td></tr>';
FLD2/A200='<TR><TD>Availability</TD>';
FLD3/A200='<TD></TD><TD></TD>';
FLD4/A250='<TD x:fmla="=OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-3,0)-OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-3,1)+OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-1,0)">0</TD>';
FLD5/A250='<TD x:fmla="=OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),0,-1)-OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-3,1)+OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),-1,0)">0</TD>';
FLD6/A250='<TD x:fmla="=IF(ROUND(OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),0,-1)-OFFSET(INDIRECT(ADDRESS(0,0,4,FALSE),FALSE),0,-2),2)=0,"In Balance","Not in Balance")">0</TD></tr>';

-*=IF(ROUND(J29-J30,2)=0,"In Balance", "Not in Balance")

END

TABLE FILE CAR
PRINT RETAIL_COST DEALER_COST THIRDVAL
BY COUNTRY
BY CAR RECOMPUTE MULTILINES
BY MODEL
WHERE COUNTRY EQ 'ITALY'
ON TABLE PCHOLD FORMAT EXL2K FORMULA
FOOTING
"<FLD1<FLD2<FLD3<FLD4<FLD5<FLD6"
END

I dont know how to escape the character '"'.

Thanks

quote:

I dont know how to escape the character '"'.

I think that is THE problem. And that's an Excel thing. I'd try searching the internet for this to see if there is some kind of escape sequence for the fmla strings.