[Solved] Why does display null cell "" value greater than 0

[Hawkster]

I am not understanding why displayed "" empty cell when used in formula is not being treated the same as an actual empty cell. I would expect it to be treated as nothing .. or given 0 numerical value, like which is done when cell is actually empty. But it isnt and can only guess it is giving it either "True" or "False" value when used in formula in another cell.

Please dont give fixes or solutions to following, as I know how I can already. But as reference to help make my question understandable by using an example. In the "G" column I have Success percent chance for an action which only displays if is between 75% - 100%, otherwise it displays nothing. There are other times when no formula is in Success column, since that action is N/A.

A16 cell number of attempts =100
B2 cell positive number results =55
G16 cell Success formula =IF($B$2>=A16;MIN(100;75+($B$2-A16))/100;"")
H16 formula =IF(H16>=0.75;1-H16;0) returns 1. As displayed empty cell is greater or equal to 0.75, so 1 - nothing is 1.

vs

G16 cell Success formula
H16 formula =IF(H16>=0.75;1-H16;0) returns 0. As nothing is not greater or equal to 0.75, so is 0.

My question is why a displayed empty cell using "" is greater or equal to some number, yet an actual empty cell isnt?

[Zizi64]

The Value of an empty cell IS zero.
Use the ISBLANK()function to determine a really blank cells:

=IF(ISBLANK(...the examined cell...);"";...your code here...)

Note:
A cell containing a Formula with result empty string "" is NOT empty, because it contains a FORMULA.

Please upload YOUR example ODF type file here...

[JohnSUN-Pensioner]

May be in H16 must be not formula =IF(H16>=0.75;1-H16;0) but

Code: Select all

=IF(N(G16)>=0,75;1-G16;0)

[RusselB]

In Calc a string, including the "" (though usually referred to as null), for the purposes of formula comparisons, has a value greater than any numeric.
There are several ways of checking if the cell has "" in it (or not) before checking the value.
Options:
IF(<cell>="";"";<check value)
IF(ISNUM(<cell>);<check value>;"")

Another option is to change your formula in G16 to use 0 rather than "" and set Calc to not display 0 values.

[Hawkster]

In Calc a string, including the "" (though usually referred to as null), for the purposes of formula comparisons, has a value greater than any numeric.
There are several ways of checking if the cell has "" in it (or not) before checking the value.
Options:
IF(<cell>="";"";<check value)
IF(ISNUM(<cell>);<check value>;"")

Another option is to change your formula in G16 to use 0 rather than "" and set Calc to not display 0 values.

So a null or "" is sort of treated like infinity in round about way?

In other words, going by replies so far it isnt being treated as TRUE or FALSE, which are values 1 or 0. Nor would be as treated as Text string since that would give #value errors.


@Zizi, sorry mate you are normally awesome help, but dont want to upload an example ODF file, that will not answer my question and will just encourage even more people posting trying to give examples on how to rectify. I am not wanting ways on how to rectify as I already know how to do that. I am trying to understand what is actually happening, how spread sheet is handling and processing it.

[Zizi64]

@Zizi, sorry mate you are normally awesome help, but dont want to upload an example ODF file, that will not answer my question and will just encourage even more people posting trying to give examples on how to rectify. I am not wanting ways on how to rectify as I already know how to do that. I am trying to understand what is actually happening, how spread sheet is handling and processing it.

I understand it, but your textual example contains a circular reference:

H16 formula =IF(H16>=0.75;1-H16;0) returns 1. As displayed empty cell is greater or equal to 0.75, so 1 - nothing is 1.

and I do not know if you want really use such formula in the real spreadsheet...

[keme]

What is happening:

• A cell's data and a cell formula are distinct entities, but connected.
  When you do not enter anything in a cell, the value is "blank". The blank cell will match (test as equal) to number zero, empty string, and other cells containing zero, empty string or nothing.
  When a formula is entered into a cell, the value returned from that formula determines the data value and type of the cell.
  There is no formula entity (function, operator, defined constant) which can return "the value of blank" (or "no value").
  This is different from other programming tools which can have their functions set to return "void" or similar.
  When you use an empty cell in a formula, the default is to assume data type numeric and value zero.

So, alas, the only way to have a cell register as "blank" is to keep it empty.
You could use the error "Not available" instead. The function NA() returns this error, displayed as #N/A. Test for error with ISNA() or ISERROR() functions. This avoids confusion with real data, but looks ugly when error codes are displayed in a cell. Which kind of value you use for "no value" is up to you. Different situations may call for different solutions...

[Hawkster]

Thank you Keme, that does help.

Yea, my example in OP wasnt the greatest, H16 was supposed to be G16 in formula. But still didnt do so great with that whole example anyway. So I will include example spread which is better.
Row 1 has 4 various inputs. Calc formula, empty cell, IF statement gives text string as result, IF statement gives null value as result.
Row 2 has same formula to process each 4 inputs, in particular is how the formula is processing the last couple IF statements.

I guess what I am really not wrapping my head around is fact that null appears to be greater than any numeric, yet at same time can appear as nothing.

[Villeroy]

The empty string "" is not null. It is a string. By mere convention any string is bigger than any number. String "1" is bigger than number 1 or any other number. ="1">99999 returns TRUE
On the other hand, when you compare a truely blank cell with "" ( =A1="" ), the spreadsheet program will convert A1 into the empty string string in order to make it comparable at all. =A1="" returns true for an empty string and a blank cell.
Likewise when you compare a truely blank cell with 0 ( =A1=0 ), the spreadsheet program will convert A1 into zero in order to make it comparable at all (which in my opinion is a very bad thing to do but it is a decades old spreadsheet traditional).
=ISBLANK(A1) returns TRUE if and only if A1 is null/empty/blank/nada/nothing.