Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
I have a formula that I want to use to generate (depending on the situation)
a blank value. I thought a null string ("") was equivalent to a blank but that doesn't seem to be the case. Here's my example: Contents of cell A2: =IF(A1=0,"",A1) I want to be able to do a a Copy...Paste Special...Values with cell A2 and have it copy a blank to the destination cell if cell A1 contains a zero. In the above example, if I copy the value of cell A2 into cell A3, the ISBLANK function tells me that cell A3 is not blank (i.e., it returns FALSE). If I use the LEN string function, it returns a zero (i.e., a string of no length). Can someone explain to me the logic behind this? Does someone know what I can enter into the IF function above so that it return a blank value (at least it's blank when the value is copied to another cell)? Thanks for any and all help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
"LawrenceHG" wrote...
I have a formula that I want to use to generate (depending on the situation) a blank value. I thought a null string ("") was equivalent to a blank but that doesn't seem to be the case. .... Can someone explain to me the logic behind this? Does someone know what I can enter into the IF function above so that it return a blank value (at least it's blank when the value is copied to another cell)? This is one of those things Excel can't do. If a cell contains a formula, ipso facto it can't be blank. There's no value that can be produced by a formula that's equivalent to the value of blank cells (which do have values, apparently the same value as VBA's Empty variant value). Why do you need values equal to truly blank cells? Graphing? If so, #N/A produces the same graphed results as blank cells and can be produced by formulas. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
Thanks for the response, Harlan.
The truth of the matter is that I can work around this problem with minimal difficulty. I just found the problem itself to be curious (and inconvenient) and I wanted to understand what was going on for future reference. (For whatever it's worth, the actual application is as follows. I have a matrix containing numbers greater than or equal to zero. I need another matrix in which each element is "1" if the correspoinding entry in the original matrix is positive. For those entries that are zero, I'd like to have the first matrix have blanks, primarily for readability--the 1's are what's really important. However, to use the matrix in calculations, I will then need to convert the blanks to zeroes. I figured if I used blanks, it would be easy to then use Select Special to highlight all the blanks so I could change them to zero. Of course, I could use a space character instead of a blank and then use the Excel Replace command to replace the space character with a zero, but I preferred using blanks. Apparently no such blank can be generated by a formula. Strange.) "Harlan Grove" wrote: This is one of those things Excel can't do. If a cell contains a formula, ipso facto it can't be blank. There's no value that can be produced by a formula that's equivalent to the value of blank cells (which do have values, apparently the same value as VBA's Empty variant value). Why do you need values equal to truly blank cells? Graphing? If so, #N/A produces the same graphed results as blank cells and can be produced by formulas. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
Tushar Mehta wrote...
If the primary interest is having a zero appear blank use either the format General;General;;@ (select the cells of interest then Format | Cells... | Number tab) or set the overall display to 'no zeros' (Tools | Options... | View tab | Window options section | uncheck 'Zero values'). .... FWIW, I've found it easier to determine intent for zero display supression to use number formats that are more explicit, e.g., [=0]"";#,##0.00. The doubled double quotes after the [=0] condition are necessary for conditional number formats; otherwise, missing formats default to General. However, when negative numbers should display the same as their absolute values except for including a leading hyphen, using this kind of conditional number format means only needing to specify the positive number format, so usually less work when supressing zero display. Clarity, less typing. The only open issue would be execution time differences. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
I have a similar problem: How do I set the text (value) of a cell to default ITSELF to a certain text when it is blank. Something like =IF(ISBLANK(this),"empty cell",this) Or a practical example: Contents of cell A2: =IF(ISBLANK(A2),"empty cell",A2) My current experiments return 0 as result Help? -- danswa ------------------------------------------------------------------------ danswa's Profile: http://www.excelforum.com/member.php...o&userid=31267 View this thread: http://www.excelforum.com/showthread...hreadid=502218 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
danswa wrote:
I have a similar problem: How do I set the text (value) of a cell to default ITSELF to a certain text when it is blank. Something like =IF(ISBLANK(this),"empty cell",this) Or a practical example: Contents of cell A2: =IF(ISBLANK(A2),"empty cell",A2) My current experiments return 0 as result Help? The reason you are getting zero is because you have created a circular reference to yourself Putting your statement in B2 works fine B2: =IF(ISBLANK(A2),"empty cell",A2) This method is used frequently in excel and you base the rest of your calculations on column B not A One common method I use for example is; Column A - contains MANUAL DATA Entry values Column B - contains CALCULATED formulas with possible default values Column C - contains either manual entry or default calculated or blank Then you use column C as you the basis for any further calculations C1: = IF(ISBLANK(A1),IF(ISBLANK(B1),"BLANK",B1),A1) Hope this helps :) George |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
I have a major issue with this different interpretation of "null", relating
to the operation of Paste Special Values. I need a way to make all such cells consistent for behavior of validating formulas and VBA code. I do a lot of string manipulation functions to clean up data. In many cases I am dealing with "empty" cells that are that way because they were unused, and in other cases because a formula returned ="". If you do a copy of these cells, and do a Paste Special Values I would expect the result to be controllable, but it isn't. PSV puts a null string in the target cell if the source was a formula that evaluated to ="", and it will put an "empty" cell if the source was empty (cleared). This causes three problems: 1) These cells do not test the same as a "real" empty cell, 2) the "null string" cell does not allow the preceding cell text to flow into the following cell, and 3) these two types of cells do not Sort together nor do Filters and Pivot Tables treat them the same. If you have cell A1 that you press Delete in, and cell A2 that you do a CopyPasteValues of an empty string in (=""), visually they are identical on the formula bar, but... Type(A1) = 1 Type (A2) = 2 Isblank(A1) = True IsBlank(A2) = False =A1="" = True =A2="" = True Cells like A2 will sort before text and empty cells will sort after text. The final slap in the face is that if you press F2 and then enter on the "pasted null string" cell, it changes to an empty cell! I can accept, begrudgingly, that this is "the way Excel works". But there has to be some type of EQUALIZER -- either function or VBA, that will let me go through 27,000 rows and 38 columns of data and make all the "visually empty" cells act the same. If Paste Values doesn't do it, then the only alternative is to press F2-Enter on every cell. :-O (or read the whole thing in and write it back out to a different sheet with VBA that does the correct checks on each cell. Help! - Mike "Harlan Grove" wrote: "LawrenceHG" wrote... I have a formula that I want to use to generate (depending on the situation) a blank value. I thought a null string ("") was equivalent to a blank but that doesn't seem to be the case. .... Can someone explain to me the logic behind this? Does someone know what I can enter into the IF function above so that it return a blank value (at least it's blank when the value is copied to another cell)? This is one of those things Excel can't do. If a cell contains a formula, ipso facto it can't be blank. There's no value that can be produced by a formula that's equivalent to the value of blank cells (which do have values, apparently the same value as VBA's Empty variant value). Why do you need values equal to truly blank cells? Graphing? If so, #N/A produces the same graphed results as blank cells and can be produced by formulas. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
Mike F. wrote...
.... I am dealing with "empty" cells that are that way because they were unused, and in other cases because a formula returned ="". If you do a copy of these cells, and do a Paste Special Values I would expect the result to be controllable, but it isn't. PSV puts a null string in the target cell if the source was a formula that evaluated to ="", and it will put an "empty" cell if the source was empty (cleared). Converting cells from formulas to values should leave other formulas that refer to those cells unchanged. For example, if A1 contained =2*ROW(), and A2 contained =A1+1, then A2 would evaluate to 3. If A1 is converted to its formula's value, A2 should still evaluate to 3. By the same toekn, if B1 contained =LEFT(A1,0), so evaluated to "", and B2 contained =AND(LEN(B1)=0,COUNTA(B1)=1), B2 would evaluate to TRUE. If B1 were replaced with its formula's value, then B2 should still evaluate to TRUE. However, that requires that B1 *not* be blank ('Empty' means something precise in VBA, but not in cell formulas; in cell formulas, 'blank' means the state of cells that contain nothing), and that requires replacing anything evaluating to "" with something rather than nothing. This is complicated by the fact that Excel converts blank cells to numeric zeros, 0, in numeric contexts and to zero length strings, "", in text contexts. Lotus 123 did much better by *always* treating them as 0 and *never* as "". In programming languages, consistency is always a good thing. Back to pasting ="" and equivalent formulas as values. The result is one of Excel's odd possible values, the zero length text constant. This causes three problems: 1) These cells do not test the same as a "real" empty cell, 2) the "null string" cell does not allow the preceding cell text to flow into the following cell, and 3) these two types of cells do not Sort together nor do Filters and Pivot Tables treat them the same. .... Replace 'empty' with 'blank'. Empty is necessarily ambiguous. You may know what you mean, but your usage isn't tied to Excel's documentation. These 'problems' are all well-known. They're predictable functionality that others may rely on to behave as they currently do. Your #2 is, in fact, sometimes desirable. The final slap in the face is that if you press F2 and then enter on the "pasted null string" cell, it changes to an empty cell! No different than entering the formula ="=1+2" copying, pasting as value on top of itself, then pressing [F2], [Enter]. Or for that matter, entering =RAND() in a cell originally with number format General, then changing the cell's number format to Text and pressing [F2], [Enter]. The point is that re-entering cells *can* change their types and values even if no changes are made to the cell's contents. I can accept, begrudgingly, that this is "the way Excel works". But there has to be some type of EQUALIZER -- either function or VBA, that will let me go through 27,000 rows and 38 columns of data and make all the "visually empty" cells act the same. If Paste Values doesn't do it, then the only alternative is to press F2-Enter on every cell. :-O (or read the whole thing in and write it back out to a different sheet with VBA that does the correct checks on each cell. There's always macros. Sub foo() Dim r As Range Application.Calculation = xlCalculationManual For Each r In ActiveSheet.UsedRange If r.Formula = "" And r.PrefixCharacter = "" _ And Not IsEmpty(r.Value) Then r.ClearContents Next r Application.Calculation = xlCalculationAutomatic End Sub |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
If you want to see what's left in that cell after you convert ="" to values,
try: Tools|Options|Transition Tab Toggle Transition Navigation keys on. Then select on of those cells and look at the formula bar. You'll see an apostrophe. (Don't forget to toggle the setting to off.) When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all If you need to do this lots, you can record a macro when you do it manually. Mike F. wrote: I have a major issue with this different interpretation of "null", relating to the operation of Paste Special Values. I need a way to make all such cells consistent for behavior of validating formulas and VBA code. I do a lot of string manipulation functions to clean up data. In many cases I am dealing with "empty" cells that are that way because they were unused, and in other cases because a formula returned ="". If you do a copy of these cells, and do a Paste Special Values I would expect the result to be controllable, but it isn't. PSV puts a null string in the target cell if the source was a formula that evaluated to ="", and it will put an "empty" cell if the source was empty (cleared). This causes three problems: 1) These cells do not test the same as a "real" empty cell, 2) the "null string" cell does not allow the preceding cell text to flow into the following cell, and 3) these two types of cells do not Sort together nor do Filters and Pivot Tables treat them the same. If you have cell A1 that you press Delete in, and cell A2 that you do a CopyPasteValues of an empty string in (=""), visually they are identical on the formula bar, but... Type(A1) = 1 Type (A2) = 2 Isblank(A1) = True IsBlank(A2) = False =A1="" = True =A2="" = True Cells like A2 will sort before text and empty cells will sort after text. The final slap in the face is that if you press F2 and then enter on the "pasted null string" cell, it changes to an empty cell! I can accept, begrudgingly, that this is "the way Excel works". But there has to be some type of EQUALIZER -- either function or VBA, that will let me go through 27,000 rows and 38 columns of data and make all the "visually empty" cells act the same. If Paste Values doesn't do it, then the only alternative is to press F2-Enter on every cell. :-O (or read the whole thing in and write it back out to a different sheet with VBA that does the correct checks on each cell. Help! - Mike "Harlan Grove" wrote: "LawrenceHG" wrote... I have a formula that I want to use to generate (depending on the situation) a blank value. I thought a null string ("") was equivalent to a blank but that doesn't seem to be the case. .... Can someone explain to me the logic behind this? Does someone know what I can enter into the IF function above so that it return a blank value (at least it's blank when the value is copied to another cell)? This is one of those things Excel can't do. If a cell contains a formula, ipso facto it can't be blank. There's no value that can be produced by a formula that's equivalent to the value of blank cells (which do have values, apparently the same value as VBA's Empty variant value). Why do you need values equal to truly blank cells? Graphing? If so, #N/A produces the same graphed results as blank cells and can be produced by formulas. -- Dave Peterson |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
Dave, Interesting that only the blank and zero length text constants in the used area of the worksheet are impacted by your method - even if the range exceeding the used range is selected (which comprises blanks). Can only attribute this to efficient design in how the find/replace process works (hopefully) Dave Peterson Wrote: When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Dave Peterson Wrote: When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=502218 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
It works just like edit|find works--which is limited to the usedrange--but in
this case, why would you want to fix cells that have never been broken <bg? (So it sounds like that you're happy that MS does something the way you like <vvbg.) John James wrote: Dave, Interesting that only the blank and zero length text constants in the used area of the worksheet are impacted by your method - even if the range exceeding the used range is selected (which comprises blanks). Can only attribute this to efficient design in how the find/replace process works (hopefully) Dave Peterson Wrote: When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all Dave Peterson Wrote: When I want to clean up this detritus, I do this: Select the range (ctrl-a a few times to select all the cells) Edit|Replace what: (leave blank) with: $$$$$ replace all Immediately followed by: Edit|Replace what: $$$$$ with: (leave blank) replace all -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=502218 -- Dave Peterson |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I generate a "blank" value?
Thanks! The Macro is a great idea.
- Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generate random numberes using reference to the other cell. | Excel Worksheet Functions | |||
Generate a graphical formula from a column of data | Excel Discussion (Misc queries) | |||
How to generate a random list of weekDAYS between two dates? | Excel Worksheet Functions | |||
can i use excel to generate web links | Excel Discussion (Misc queries) | |||
generate report thru pracle to excell | Excel Discussion (Misc queries) |