Why CELL("format",A1) doesn't return something like "#.##"? ...and other rants

R

Raymond Zeitler

I'm trying to compare the displayed value of two formatted cells. The
idea is to detect the equality between 1.2345 and 1.23 when the format
is "#.##" without resorting to VBA. This is Excel 2000, SP3.

So I decided that this might work (may be wrapped):

=IF(TEXT(A1,CELL("format",A1))=TEXT(B1,CELL("format",B1)),"equal","unequal")

Unfortunately, CELL("format",A1) returns "F2", which is incompatible
with the format string required by the TEXT() worksheet function. The
above conditional actually evaluates to "equal" --
TEXT(A1,CELL("format",A1)) and
TEXT(B1,CELL("format",B1)) both evaluate to "F2". Is this silly or
what?

So I came up with this rickety workaround (probably wrapped):

=IF(FIXED(A1,MID(CELL("format",A1),2,99))=FIXED(B1,MID(CELL("format",B1),2,99)),"equal","unequal")

I use MID() to return the number that CELL("format") returns after the
"F", although I can't believe I have to resort to this. Isn't there a
better way?

-----------------

While I'm B&M'ing, entering/editing a formula in the Conditional
Formatting FormulaIs TextBox is enough to drive anyone crazy. Why
isn't there a Build button so that arrow keys don't return a cell
reference?
 
B

Bernie Deitrick

Ray,

Tools | Options | Calculation tab, check "Precision as Displayed", then use
the formula

=IF(A1=B1,"Equal","UnEqual")

Use F2 to prevent the arrow keys from performing cell selection when editing
your CF formula.

HTH,
Bernie
MS Excel MVP
 
R

Raymond Zeitler

Thanks, Bernie. "Precision as Displayed" does change the data, which
is something I don't usually like to do. But for this one workbook,
it might be a better option.

As well, your F2 tip helps a great deal. I'd tried it, expecting to
open a build box (or is it F4 that does that), but I didn't notice the
effect that it has on the arrow keys.

All the best!

(Incidentally, my real email address is rzeitler AT phonon DOT com.)

Bernie Deitrick said:
Ray,

Tools | Options | Calculation tab, check "Precision as Displayed", then use
the formula

=IF(A1=B1,"Equal","UnEqual")

Use F2 to prevent the arrow keys from performing cell selection when editing
your CF formula.

HTH,
Bernie
MS Excel MVP

Raymond Zeitler said:
I'm trying to compare the displayed value of two formatted cells. The
idea is to detect the equality between 1.2345 and 1.23 when the format
is "#.##" without resorting to VBA. This is Excel 2000, SP3.
[snip]
-----------------

While I'm B&M'ing, entering/editing a formula in the Conditional
Formatting FormulaIs TextBox is enough to drive anyone crazy. Why
isn't there a Build button so that arrow keys don't return a cell
reference?
Click to expand...
Click to expand...
 
Top