how to compare formula's values?

[Heck]

I have a few columns which contain formulas. These yield results that
I want to compare dynamically, cell by cell, not by copy and pasting,
but the identity operator, "=", does not recognize any of these cells
as equal even where their values are the same because the cells
actually contain various and different functions.

How can you compare the value of one cell to the value of another,
where both values are results of the formulas, without programming?
Can Excel do this?

The data (simplified)
Column A contains a binary value, either "w" or "q".
Column B contains a translation of the w or q to "B" or "A".
Column F's cells each contain a single character extracted from
another column's text, for example, with RIGHT(F1, 2).

The result
Column C's cells are supposed to contain a 1 if the results in the
corresponding cells in columns B and F are identical or a 0 if they
aren't.

Obviously, the cells in B and F contain formulas, not the resultant
values, so, how do I get Excel to compare the results, not the actual
contents of the cells? It does indeed work with actual values and I
figured (hoped) Excel would know what I want in this slightly more
complex case.

I looked fruitlessly for a syntax or a function that would translate
to value from formula. How do you do this simple operation?

Thanks.

 

[Jim Cone]

A formula such as "=A1=D1" does compare cell values and returns FALSE or TRUE.
What you see in the cell is not always the cell value as cell formatting determines what you see.
You need to post examples of your formulas and the results.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

..
..
..

"Heck" <[email protected]>
wrote in message
I have a few columns which contain formulas. These yield results that
I want to compare dynamically, cell by cell, not by copy and pasting,
but the identity operator, "=", does not recognize any of these cells
as equal even where their values are the same because the cells
actually contain various and different functions.

How can you compare the value of one cell to the value of another,
where both values are results of the formulas, without programming?
Can Excel do this?

The data (simplified)
Column A contains a binary value, either "w" or "q".
Column B contains a translation of the w or q to "B" or "A".
Column F's cells each contain a single character extracted from
another column's text, for example, with RIGHT(F1, 2).

The result
Column C's cells are supposed to contain a 1 if the results in the
corresponding cells in columns B and F are identical or a 0 if they
aren't.

Obviously, the cells in B and F contain formulas, not the resultant
values, so, how do I get Excel to compare the results, not the actual
contents of the cells? It does indeed work with actual values and I
figured (hoped) Excel would know what I want in this slightly more
complex case.

I looked fruitlessly for a syntax or a function that would translate
to value from formula. How do you do this simple operation?

Thanks.

 

[Heck]

Thank you, Jim. A friend of mine, a VBA programmer, found my problem.
I had substrings of trailing and intervening spaces in my various
strings. He examined my sheet, saw the behavior, said "What the..?",
and used EXACT() to determine the cause. Everything now works as
expected, after a neat trim.

Interestingly, TRIM() retains all single spaces in a string, even a
single trailing space. Once I was working on spaces, though, I
realized this further detail quickly.

A formula such as "=A1=D1" does compare cell values and returns FALSE or TRUE.
What you see in the cell is not always the cell value as cell formatting determines what you see.
You need to post examples of your formulas and the results.

snip

 

[Jim Cone]

Re: "TRIM() retains all single spaces in a string, even a single trailing space."

Well, not exactly... leading and trailing spaces are removed by Trim().
There is a non-breaking space character (160) that is impervious to the Trim and the Clean functions.
It can be removed using the Substitute function... =SUBSTITUTE(E10,CHAR(160),"")
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(see the "Clean Data" utility in xlCompanion)

..
..
..

"Heck" <[email protected]>
wrote in message
Thank you, Jim. A friend of mine, a VBA programmer, found my problem.
I had substrings of trailing and intervening spaces in my various
strings. He examined my sheet, saw the behavior, said "What the..?",
and used EXACT() to determine the cause. Everything now works as
expected, after a neat trim.

Interestingly, TRIM() retains all single spaces in a string, even a
single trailing space. Once I was working on spaces, though, I
realized this further detail quickly.

 

[Heck]

OK, thanks, I'm glad and relieved to learn that. The TRIM
documentation agrees with what you say, and I expected it to behave
that way, too, and was surprised when it didn't.

The non-breaking space must have been what was trailing the cells in
one of my columns.

Thanks for your help, a big help.