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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Raymond Zeitler - 12 Apr 2004 17:09 GMT

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?

Reply to this Message

Bernie Deitrick - 12 Apr 2004 17:27 GMT

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

> 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
[quoted text clipped - 4 lines]
>
> 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,9
9)),"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
[quoted text clipped - 6 lines]
> isn't there a Build button so that arrow keys don't return a cell
> reference?

Reply to this Message

Raymond Zeitler - 13 Apr 2004 18:30 GMT

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.)

> Ray,
>
[quoted text clipped - 13 lines]
> > 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?

Reply to this Message