Formatting a formula

[beckys]

Hello,
I have the following formula:

IF(ROUND(J98,2)=ROUND(SUMIF('Sheet2'!$A$8:$A$1000,C97,'Sheet2'!$D$8:$D$1000),2), "OK", "Off By "&J98-SUMIF('Sheet2'!$A$8:$A$1000,C97,'Sheet2'!$D$8:$D$1000))


When the result is false, the text that gets entered in the cell goes out to many decimal points, i.e. "off by 0.0246271358543424". Do you know how to format this so that the text would read "off by 0.02"?

Thanks!!
Becky

 

[RSXchin]

Like this?

=IF(ROUND(J98,2)=ROUND(SUMIF(Sheet2!$A$8:$A$1000,C97,Sheet2!$D$8:$D$1000),2), "OK", "Off By "& format(J98-SUMIF(Sheet2!$A$8:$A$1000,C97,Sheet2!$D$8:$D$1000),"0.00"))

 

[T. Valko]

Like this?
=IF(ROUND(J98,2)=ROUND(SUMIF(Sheet2!$A$8:$A$1000,C97,Sheet2!$D$8:$D$1000),2), "OK", "Off By "& format(J98-SUMIF(Sheet2!$A$8:$A$1000,C97,Sheet2!$D$8:$D$1000),"0.00"))

Never heard of the "format" function.

I'll be you meant to use TEXT(...).

=IF(ROUND(J98,2)=ROUND(SUMIF(Sheet2!$A$8:$A$1000,C97,Sheet2!$D$8:$D$1000),2),"OK","Off By "&TEXT(J98-SUMIF(Sheet2!$A$8:$A$1000,C97,Sheet2!$D$8:$D$1000),"0.00"))

 

[RSXchin]

lol. yeah, I meant "TEXT". I'm so used to VB now... I barely know excel itself anymore.

 

[beckys]

Great! Thanks so much for your help!