Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an absolutely empty cell ... but not ""
e.g. b10 =if(isna(vlookup(a10,table,2,false)),"",vlookup(a1 0,table,2,false))
c10 contains 5% d10 = b10*c10 In an "if" formula, I like to use "" to make all #n/a, #value and zero to be disapeared. However, it returns #value when I need to further calculate with reference those cell contain "" (e.g. d10). I've tested the cell with "" by using "isblank", it is a non-blanked cell even it displays nothing. If I do not like to make another "if formula" under colume d to fix it, what formula should I use to return an absolutely empty cell (rather than "" or 0) under column b. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an absolutely empty cell ... but not ""
Why not return a zero and then use a custom number format to suppress this,
such as the format #;#;;@ The ;; contains no format for the zero position so it will disappear Alternatively, keep your "" blank cell, then your formula in D could simply contain =SUM(b10)*c10 since SUM is tolerant of non-numeric entries (or rather it treats them as zero without giving an error) -- Adam Vero MCP, MOS Master, MLSS, CWNA http://veroblog.wordpress.com http://www.meteorit.co.uk "Billy Leung" wrote: e.g. b10 =if(isna(vlookup(a10,table,2,false)),"",vlookup(a1 0,table,2,false)) c10 contains 5% d10 = b10*c10 In an "if" formula, I like to use "" to make all #n/a, #value and zero to be disapeared. However, it returns #value when I need to further calculate with reference those cell contain "" (e.g. d10). I've tested the cell with "" by using "isblank", it is a non-blanked cell even it displays nothing. If I do not like to make another "if formula" under colume d to fix it, what formula should I use to return an absolutely empty cell (rather than "" or 0) under column b. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return an absolutely empty cell ... but not ""
Adam,
I've originally setup a conditional formatting to do it, but your suggestion is definitely a better solution. Thanks a lot. Billy "AdamV" wrote: Why not return a zero and then use a custom number format to suppress this, such as the format #;#;;@ The ;; contains no format for the zero position so it will disappear Alternatively, keep your "" blank cell, then your formula in D could simply contain =SUM(b10)*c10 since SUM is tolerant of non-numeric entries (or rather it treats them as zero without giving an error) -- Adam Vero MCP, MOS Master, MLSS, CWNA http://veroblog.wordpress.com http://www.meteorit.co.uk "Billy Leung" wrote: e.g. b10 =if(isna(vlookup(a10,table,2,false)),"",vlookup(a1 0,table,2,false)) c10 contains 5% d10 = b10*c10 In an "if" formula, I like to use "" to make all #n/a, #value and zero to be disapeared. However, it returns #value when I need to further calculate with reference those cell contain "" (e.g. d10). I've tested the cell with "" by using "isblank", it is a non-blanked cell even it displays nothing. If I do not like to make another "if formula" under colume d to fix it, what formula should I use to return an absolutely empty cell (rather than "" or 0) under column b. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return an empty cell instead of "VALUE" | Excel Worksheet Functions | |||
Can IF function return an empty cell? (not "") | Excel Worksheet Functions | |||
Excel: can "go to adjacent empty cell" double-click be disabled? | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
a formula that leaves cell "empty"? | Excel Worksheet Functions |