PDA

View Full Version : IsBlank() internal function not returning consistent result



fam3d
10-04-2006, 06:45 AM
Hi All,

I have encountered an anomoly that I simply can't explain.

In the very simple attached workbook I am testing the range B6:C6 is the contents are blank by using the internal IsBlank() function.

As you can see the formula in cell B3 (above the test range) returns the expected result, whereas, the formula in cell D6 (to right of test range) returns an unexpected (by me anyway) result.

Is there something I'm missing here? (obviously!)

I have a work-around, but a technical explanation would be appreciated (maybe some examples of how to deal with this, if not at first glance obvious).

Thank you in advance.
Frank McDonald

mvidas
10-04-2006, 07:15 AM
Hi Frank,

I may have a hard time conveying why this happens, but the primary reason is because ISBLANK is looking for a single cell.

When you have it in column B, it is really only looking at B3 (if you press F2 while on B6, you'll see the blue box around B6 only) since it is in the same column. Likewise if you put that same formula in C3, it will only be looking at C6. If you put it in any other column, it will return false since it will be looking at both columns, which is incorrect syntax.

Instead of using ISBLANK, you can use COUNTA which counts the number of cells that have something in them. Using
=COUNTA(B6:C6)=0
will return true if no cells have anything in them, with any sized range.

Matt

fam3d
10-04-2006, 07:27 AM
Mvidas,

Yes... almost there. I agree all the information functions (ISxxx()) operate on individual cells, not ranges. (Any MS Excel developers out there might want to consider that novice users don't always pay attention/know the difference between cell(s) and ranges).

And, your emperical explanation of what occurs above/below the range is what I saw as well. However, using the same logic, would one not assume that function to left/right would only look at B6 as well (disregarding the extraneous range) - therefore representing consistent results (however, not accurate perhaps).

I agree on work-arounds: I used the CountBlank() - you say tomato, I say tOmAto :)

Thanks for your insight.
Frank

mvidas
10-04-2006, 07:39 AM
would one not assume that function to left/right would only look at B6 as well (disregarding the extraneous range) - therefore representing consistent results (however, not accurate perhaps).
One would hope it works that way, but excel does not always follow logic :) Well, not human logic anyways. Take a look at my attached file, the range you're using in the IsBlank works the same way as a named range. My A1:A6 is called "NamedRange". In column C, I entered =NamedRange and you can see it is pulling the data from the corresponding row. In C7 it is showing value, since that row is not in the range. NamedRange is still a valid name, but this is just how excel works.


I agree on work-arounds: I used the CountBlank() - you say tomato, I say tOmAto :)I was going to suggest CountBlank, but counta=0 works good if you don't know how many cells are going to be included in the given range. If you insert a row/column in the middle of that, the range in the countblank will change to fit, but the =2 would remain the same. In any case, whatever works for you and you understand the best :)

fam3d
10-04-2006, 08:00 AM
Interesting you should bring up 'named range'. In reviewing you attachment I realized that it also appears to 'calculate' column-wise (data in the same column is return). That would of course explain the deviation in my isblank example.

I totally agree that counta is a much better solution (since we all know that chaos rules and the sheet layout WILL be changed). Lot less clean-up in the case of counta when changing layout.

As a side-note: When does a cell reference become a range reference? Certainly we can all agree the reference A5 is to a cell, and A5:G7 is to a range. But, is the reference A5:A5 dimensioned as a cell or range since it refers to a single memory address?

mvidas
10-04-2006, 08:09 AM
Interesting question.. excel philosophy :) A single cell is a range but a range can be more than a single cell.
A range could be "A5", "A5:A5", "A5:B7", "A5:B7,C10:E15", or any combination of continuous or multiple-area ranges.
A cell can only refer to a single-cell.
Using A5:A5 will still only refer to the cell once, so that could be considered a single cell. However since few people will use that format (except for maybe using A$5:A5 so you can fill down and keep the first cell 'anchored'), it wouldn't be much.
An example of how excel refers to a5:a5 is using a formula like =COUNTBLANK(A5:A5)
If a5 is blank, it still only refers to one cell. Countblank only accepts a single range as it's argument, so you couldn't do something like =COUNTBLANK(A5,A5,A5,A5).

However, counta is different, since it allows up to 30 different arguments. If you use =COUNTA(A5:A5), the answer will still be 1 (if there is something there), but using =COUNTA(A5,A5) will return a 2 since it is looking at each argument and adding them up.
That example should show that a5:a5 still is only the single cell A5.

fam3d
10-04-2006, 10:03 AM
Curious? If Excel help states Fx() operates only on single cells (which they rarely state in that fashion, I might add), I would expect that the function internal is coded in such a way that it only excepts cell address not range(s), ie. the dreaded #ref or some such thing. I guess, as you so aptly stated earlier, excel does not follow human logic - live with it die human!

I can see that you are a proponent of the counta because of it's flexibility and you have a new convert! I'll explore how I have 'dummied' my functions and make some smarter use of functions. I wonder now what is payoff in performance in using counta for example as opposed to countblank for 'finding blanks'? (really just a rhetorical question)

Really, thanks alot for your feedback and inspiration.

Best regards,
Frank