Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
how to compare formula's values?
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. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
how to compare formula's values?
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" 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. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
how to compare formula's values?
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. On Wed, 25 Aug 2010 20:49:37 -0700, "Jim Cone" wrote: 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. "Heck" 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)... snip |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
how to compare formula's values?
"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" 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. |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.newusers
|
|||
|
|||
how to compare formula's values?
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. On Thu, 26 Aug 2010 09:44:39 -0700, "Jim Cone" wrote: "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),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change Formula's to Values | Excel Programming | |||
Copy Sheet w/out Formula's, JUST values | Excel Programming | |||
VB for copy & paste values (not formula's) | Excel Programming | |||
How do i compare values from two sheet and copy & paste if values match? | Excel Programming |