"" value created by a formula

[pilotwings64]

Here's something interesting I came across while at work yesterday. I won't bore you with the beginning of the problem. I'll just say that I was very frustrated that excel was not doing what I thought it should do. I finally found the columns responsible for the problem. On the cell in column A I evaluated whether or not the cell in the same row of column B was greater than the cell above it in column B.

As an example A2 asked if B2 was greater that B1. B1 was blank. B2 was 1.04. A2 told me that B2 was less than B1.

I experimented with simpler formulas and found that if a cell is blank because nothing had been entered into it, excel considered its value to be lower than any number (makes sense). If the cell is blank because a formula (or at least all the formulas I tried) made it blank, excel considered it to be higher than all numbers.

This seems very odd to me. Am I missing something, or is this just an excel oddity?

[Shijesh Kumar]

Basically if the cell is originally blank then the cell has null value and NULL VALUE and null values cannot be compared with anything..

On the other hand when you put , and if the formula return blank then it compares with the formula itself.

select any cell. Change its format to text. copy the same formula which is there is B2. now compare (=).. you will see excel return true.

[daddylonglegs]

A truly blank cell acts as if it was zero [ if B1 is blank and B2 contains -1 then B2>B1=FALSE] but excel considers that any text value is greater than any number....and a "formula blank", i.e. "" returned by a formula, acts like a text value

[DRFLETT]

I had almost a similar problem.

I was creating a chart from my table that had blank ("") cells generated by a formula. On the chart, it was clear that the blank cells had generated the number 0 so my curves looked ridiculous.

the solution i used was to generate an error in those cells which were not charted ie. instead of "" as an output to the formula i used NA().

Andy

[anazarian]

I too have had a problem with null cell values. I made a formula that a macro inserted into Column D that read from cells in Row 5. Some had values entered into it, some didnt. In Column D, it said, =if(E5>0, "0305", ""). It would only work for those cells that are blank (i.e. have had a value and than has been deleted or had a " " in it).

The person I made the macro for still had to go into each row (there were 50 rows) and hit the delete key in each null (empty looking) cell. Is there a way to go around this, if, as Shijeh Kumar said, null cells cannot be used in inequality statements ? ? ?

Any help would be great! Thanks
-Arthur

[daddylonglegs]

Add a check to ensure the cell value is numeric, i.e.

=if(AND(E5>0,ISNUMBER(E5)),"0305","")