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

  • Thread starter Thread starter Raymond Zeitler
  • Start date Start date
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?
 
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
 
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...
 
Back
Top