Why does one formula work, but another very similar one doesn't? (1 Viewer)

[RWilliams500]

This formula is working fine as far as I can tell.

=FILTER(SORTBY(tblBasketList, tblBasketList[Unit], 1, tblBasketList[Basket], 1), (tblBasketList[Last] <>"") * ((tblBasketList[Crew]= "B") + (tblBasketList[Crew] = "Days")) * (tblBasketList[NL]=""))

Rows without a blank name, in the B and Days crews, and with a blank spot in the NL column, sorted by the Unit and then the Basket.

This formula, similar to the working one, is not working.

=FILTER(SORTBY(tblBasketList, tblBasketList[Last], 1, tblBasketList[First], 1), (tblBasketList[Last] <>"") * (tblBasketList[NL]=""))

The filters just don't work. I'm obviously missing something. Can anyone help by pointing out what in the second one is causing it to not filter?

 

[Pat Hartman]

"" is not the same as Null. Could that be the problem?

 

[Isaac]

this is Excel

 

[RWilliams500]

"" is not the same as Null. Could that be the problem?

But it works in the top formula. Why would it not work in the second formula?

 

[RWilliams500]

this is Excel

Yes, I'm aware of that. Was there supposed to be more to the post?

 

[Pat Hartman]

Yes, I'm aware of that. Was there supposed to be more to the post?

Are you certain that your data has no nulls in one case but has nulls in the other? In Access, I use:
If somefield & "" = "" Then ---- that handles both ZLS and nulls in a single expression.

Excel is not my tool of choice so I'm guessing since it uses VBA and so does Access.

 

[cheekybuddha]

Excel is not my tool of choice so I'm guessing since it uses VBA and so does Access.

They are excel formulas, not VBA, Pat

 

[Isaac]

Yes, I'm aware of that. Was there supposed to be more to the post?

I was directing that at Pat

 

[Isaac]

Are you certain that your data has no nulls in one case but has nulls in the other? In Access, I use:
If somefield & "" = "" Then ---- that handles both ZLS and nulls in a single expression.

Excel is not my tool of choice so I'm guessing since it uses VBA and so does Access.

In most Excel scenarios, there is functionally no difference - a cell is either blank or it isn't, in formulas anyway

 

[Pat Hartman]

They are excel formulas, not VBA, Pat

As I said, I know they are Excel formulas but at some level VBA is VBA. I also know that the interface search treats ZLS as being = Null but VBA should not and that was what I was getting at.

 

[Pat Hartman]

In most Excel scenarios, there is functionally no difference - a cell is either blank or it isn't, in formulas anyway

Given the naming scheme, it looks like tables are involved and there most certainly is a difference between a ZLS and Null if the data is coming from a table and the function should not be masking that.

If the data is coming from tables, it is certainly easy enough to determine if set 1 has no null values and set 2 is a mix of ZLS and Null.

 

[cheekybuddha]

As I said, I know they are Excel formulas but at some level VBA is VBA. I also know that the interface search treats ZLS as being = Null but VBA should not and that was what I was getting at.

Where does VBA come in to this?

 

[arnelgp]

it does work for me, though.

 

[Minty]

@Pat Hartman - In an Excel cell formula there is no NULL only ever "" when a cell is empty.

 

[The_Doc_Man]

From my understanding of the structure of Excel worksheets, which came from an old article that is hard to find these days, everything is sparsely populated. That is, if you have something in cell A7, there is a spreadsheet Cell object identifying itself appropriately. If there is nothing in cell A8, then there is no cell object at all. In this discussion, continue to assume that cell A8 has not been defined yet.

The column headers and row headers are listheads that link to the cells, so there is actually a "mesh" linkage - horizontally AND vertically - but if the contents of the cell have not been defined yet, then neither set of neighboring cells will have links to a Cell object identifying itself as A8. Cell A7 will point to its next column member A9 (assuming it exists) and row 8's header will point to its first row member B8 (assuming THAT exists). When there IS a cell structure for a given cell, its value is never null. Its default value is "" and either has no explicit format or inherits a format based on something done for either the column header or row header. Further, if both column and row headers get formatted, the last format stored is the one that is used. If an individual cell format is defined last, THAT is the format that "wins." That is because other than the things done via group selection (sheet, row, column), each existing cell is its own independent entity, having pointers to its neighbors but otherwise having nothing to do with them that isn't part of a formula stored per-cell.

Take it with a grain of salt because this was based on a very old discussion. But you can get confirmation of at least some of this by asking Google (with Gemini enabled) "internal data structure of an excel cell object". You have to be careful, though, because Gemini is somewhat equivocal regarding whether the cell exists or not. In one answer it claims the cell exists but then also says "but takes up no space in the file." Therefore, its definition of "exists" must include the word "virtually."

 

[CJ_London]

Perhaps the OP can provide some example data and the outcome required from that data and clarify what ‘not working’ means - wrong result? Something else?

 

[Pat Hartman]

@Pat Hartman - In an Excel cell formula there is no NULL only ever "" when a cell is empty.

Someone humor me and check the actual table data if the workbook is in fact linked to a table. The only rational answer is a data difference.

 

[Minty]

I have assumed it's an Excel Table not a linked table, and I'm sure the same applies when using formulas.
There is no such thing as a Null value as far as Excel formulas are concerned. It doesn't have an intrinsic IsNull formula operator.

A null cell may have a formula in it bizzarely...More here:

Excel Blank Vs Null

Understand the difference between Excel blank vs null cells to enhance your data management skills. Learn how to identify and handle each type.

basicexceltutorial.com