• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to not repeat the formula with checking for blank?

S

shahkalpesh

New Member
Hello there,


Here is the scenario:


I am writing an excel formula which is complicated, such as

Code: 
=OFFSET(INDIRECT(VLOOKUP(....)), 2, 0)


The above will refer to a cell, of which the value could be blank.

I don't know why Excel shows 0 as the value if the cell that is referred doesn't contain any value.


Here is what I don't want to do

=ISBLANK(OFFSET(INDIRECT(VLOOKUP(....)), 2, 0)), "", OFFSET(INDIRECT(VLOOKUP(....)), 2, 0)))


Is there a way to minimize the repeating of the argument to ISBLANK?

Please close this message if such a question is asked & answered before.


Thanks a lot.
 
Hi shahkalpesh,


Firstly Welcome to the Chandoo's forums!!


Are you dead-sure that these Zero are not actually values in your data set but just result of your formula? In case that these are not present in your table, you have all values greater then zero, you can do away with this problem by Custom Number Formats.


Select the Column and Format it as
Code: 
#;#;""
Zero will not be displayed any more. Now just stick to your first formula, there is no need for IF() statement.


It would have been helpful it you had uploaded the sample file. Might this not help you, consider uploading a sample file.


Regards,

Faseeh
 
Thanks Faseeh for replying.

The same problem can be demonstrated using a simple cell reference


For e.g.

On a new workbook, keep cell A1 blank.

On cell A2, type =A1. You'll see that it shows 0 instead of blank.


For cell A2 to not show 0, I could write

=IF(ISBLANK(A1), "", A1)


However, I am repeating A1 in the above formula.

Coming back to my original question, assume that I am writing a formula instead of A1.


=IF(ISBLANK(OFFSET(INDIRECT(VLOOKUP(....)), 2, 0)), "", OFFSET(INDIRECT(VLOOKUP(....)), 2, 0))))


The formatting will hide it from display. However, it still is 0 when retrieved using VBA (
Code: 
Range("A2").Value
)


Is there a way to minimize the repeating of the argument to ISBLANK?


Thanks!!
 
Hi shahkalpesh,


Sorry for keep you waiting, Can you upload a sample file might we by-pass this IF() statement?? There could be some other formula that could serve the purpose. So far there appears none.


Edit: I just tried with sample data, so far IF() is unavoidable.


Regards,
 
Hi Kalpesh ,


I'm not clear on what you wish to do.


Do you want to access some 0 / blank value through VBA or in formulae on the worksheet itself ?


If it is VBA , then why do you need to do anything in the formula ? If it is only on the worksheet itself , then why not hide zeroes through formatting ? Or is it both ?


Can you please explain clearly with formulae and / or VBA code ?


Narayan
 
Thanks NARAYANK991 and Faseeh for writing back.


Here is how it is

On a new workbook, keep cell A1 blank.

On cell A2, type =A1. You'll see that it shows 0 instead of blank.


Using VBA,
Code: 
Range("A2").Value
returns 0. Whereas, it isn't 0 (because A1 is blank & NOT 0).


How can I make it return blank instead of 0 (because A2 refers A1, which is blank)?
 
Hi Kalpesh ,


You are merely reiterating what you said in your first post.


My question was what you wish to do. Do you wish to test for a cell being blank using VBA ? If so , then there is no need for A2 to be displayed as a blank.


If you wish to check whether A2 is really blank or not , check out the following link :


http://www.dailydoseofexcel.com/archives/2008/06/26/identify-empty-cells-in-vba/


Narayan
 
NARAYANK991


Using the example given above, try doing
Code: 
IsEmpty on A1 as well as A2.

And you'll understand that [code]IsEmpty(A1)
returns true whereas IsEmpty(A2)[/code] returns false.


Here is what I am looking for:


If I have a cell (with formula in it) that refers to another cell (which is empty), I don't want it to return 0. Can this be done without the use of formatting?


Can this be done using excel formula? If not, VBA? how?
 
Hi Kalpesh ,


We seem to be going round in circles.


You say you want a formula which refers to a blank cell to return a blank instead of a 0.


You admit that this can be done using formatting.


You also admit that it can be done using formulae , but where an IF is concerned , you don't want to repeat the formula.


I think you need to give others an idea of why you want to take a circuitous route before they will think of investing time and effort in trying to get a solution.


If it a mere academic exercise , you might be better off asking this question in some of the LinkedIn groups.


Narayan
 
Hi, shahkalpesh!

Arriving at dessert time and after reading your original question "Is there a way to minimize the repeating of the argument to ISBLANK?", and considering you discarded the formatting option and the VBA solution, I'd say that the answer to your question is very simple: NO.

Regards!
 
You must log in or register to reply here.
Back
Top