Cell to return null instead of 0

D

dford

I have a formula that returns 0 instead of null. When I sort the column in
assending order the cells with 0 show up first. I need the cells to be null
so they do not show up first in the sort.
 
P

Peo Sjoblom

You can't have it both ways if the reason your formula returns zero instead
of blank is because you would get an error if you use "" instead of 0, if
not just change the formula that returns zero

--
Regards,

Peo Sjoblom

(No private emails please)
 
D

David McRitchie

What does your formula have.

Also check your tools, options, view, uncheck zero values
I just had to uncheck that myself, evidently it is a default.
--
 
D

dford

The formula reads =If(sheet1!B138=0."",sheet1!b138) This formula is in Sheet
2. The cell b138 in Sheet 1 is blank. When I sort the cells in Sheet 2 in
ascending order these cells appear first. I have unchecked zero values in
tools/options/view.

David McRitchie said:
What does your formula have.

Also check your tools, options, view, uncheck zero values
I just had to uncheck that myself, evidently it is a default.
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

dford said:
I have a formula that returns 0 instead of null. When I sort the column in
assending order the cells with 0 show up first. I need the cells to be null
so they do not show up first in the sort.
Click to expand...
Click to expand...
 
D

David McRitchie

hopefully just a typo
=If(sheet1!B138=0."",sheet1!b138)
you have a period instead of a comma
 
D

Dave Peterson

And I would think that if B138 actually was a zero, then 0 should be returned:

=If(sheet1!B138="","",sheet1!b138)
 
D

David McRitchie

okay it was strictly a typo, he did not paste the actual formula, because the
error constitutes a syntax error and is not accepted as a formula.
 
Top