Cell to return null instead of 0

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.
0
dford (5)
11/28/2005 5:07:03 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
414 Views

Similar Articles

[PageSpeed] 2

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)


"dford" <dford@discussions.microsoft.com> wrote in message 
news:09BE2C7D-65BE-4815-BC79-2420CC22C631@microsoft.com...
>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. 

0
terre081 (3244)
11/28/2005 5:11:43 AM
Hi!

Try this:

=IF(your_formula=0,"",your_formula)

Biff

"dford" <dford@discussions.microsoft.com> wrote in message 
news:09BE2C7D-65BE-4815-BC79-2420CC22C631@microsoft.com...
>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. 


0
biffinpitt (3172)
11/28/2005 5:16:58 AM
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" <dford@discussions.microsoft.com> wrote in message news:09BE2C7D-65BE-4815-BC79-2420CC22C631@microsoft.com...
> 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.


0
11/28/2005 5:34:19 AM
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" wrote:

> 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" <dford@discussions.microsoft.com> wrote in message news:09BE2C7D-65BE-4815-BC79-2420CC22C631@microsoft.com...
> > 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.
> 
> 
> 
0
dford (5)
11/28/2005 2:32:03 PM
hopefully just a typo
   =If(sheet1!B138=0."",sheet1!b138)
you have a period instead of a comma
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm


0
11/28/2005 5:27:35 PM
And I would think that if B138 actually was a zero, then 0 should be returned:

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



David McRitchie wrote:
> 
> hopefully just a typo
>    =If(sheet1!B138=0."",sheet1!b138)
> you have a period instead of a comma
> ---
> HTH,
> David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
> My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm

-- 

Dave Peterson
0
petersod (12005)
11/28/2005 5:57:32 PM
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.


0
11/28/2005 6:37:13 PM
Reply:

Similar Artilces:

copy cell from one sheet of worksheet to another sheet
This probably has a simple solution, but I can't find it. I have multiple sheets I'm working with. I want to take the sum of a column in one sheet and post the total both at the bottom on that page as well as my main page. Thanx for any help! Imagine your total is in cell D20 of a sheet called "Data" - its formula will besomething like: =SUM(D1:D19) In your "Summary" sheet, you will need the formula: =Data!D20 Hope this helps. Pete ...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

CRM 4.0 External Connector
Can anyone point me to CRM 4.0 External Connector installation guide? Cheers, Mandy. "Mandy" <Mandy@discussions.microsoft.com> wrote in message news:BF297AE0-9446-46CB-91FA-CFF19A65F1DF@microsoft.com... > Can anyone point me to CRM 4.0 External Connector installation guide? The external connector is a license so nothing you need to install. -- Robert MacLean http://www.sadev.co.za thanks for update. would you know if there's any specs on how it integrates with crm? "Robert MacLean" wrote: > "Mandy" <Mandy@discussions.microsoft.com&g...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

Enter "1", cell show ".01". Why?
Any number typed into a cell is divided by 100. If proceded by "=" the number is correct. What caused this and how can I fix it? Try this .. Click Tools > Options > Edit tab Uncheck "Fixed decimal" > OK Things should be back to normal now .. (it's a fixed decimal setting !) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Yonian" <Yonian@discussions.microsoft.com> wrote in message news:40499CA4-7FAF-42A6-8B19-A90881735C50@microsoft.com... > Any number typed into a cell is divided by 100. > If p...

Integrating Great Plains 8.0 and CRM 1.2 and Windows Small Busines
Integrating Great Plains 8.0 and CRM 1.2 I run a Windows Small Business Server 2003 Premium box along with a Windows Server 2003 Standard box used for my Great Plains 8.0 and SQL 2000. I’m considering introducing CRM 1.2 Professional into the mix. From a hardware stand point I know I’m fine all around. I’ve been researching how I should configure CRM and am a bit confused. The info I’ve found in KB887153 states “When you install Microsoft Business Solutions CRM Integrations for Great Plains 7.0, 7.5, or 8.0, Microsoft BizTalk Server is also installed. However, BizTalk Server 2000 and B...

excel 97 how can i transform in the same cell 1.256.324 in 125.63
I want to transform in excel 97, a number ( ex: 1.256.324 in 125.63) bu in the same cell. How can i do this? In my country money changed from 10.000 in 1 ; 100.000 , in 10...etc So i want excel to transform for me automatically an old sum of mone in the new one. I want to write 1.256.324, and when i press enter thi number to be transformed in 125.63. Thanx .... And thanx...: -- puiuluipu ----------------------------------------------------------------------- puiuluipui's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2679 View this thread: http://www.excelforum...

cell will not center
Hi. I have a user with an Excel worksheet. There are multiple rows and columns and they are all set on center alignment, (center alignment icon on the toolbar as well as Format Cells --> Horizontal Alignment --> Center.) The alphabetical characters align correctly but the numerical don't, as they will only left align. Format Cells --> Number is set to General, so I don't know why it won't change the alignment. Other than the worksheet being corrupted, I don't know what could be wrong with it. Any suggestions are much appreciated. Thanks! Hilary =?Utf-8?B?SG...

Can Outlook 2003 use MSN Messenger INSTEAD of Windows Messenger?
I didn't get an answer to this question last week so I'm re-posting... I recently purchased a new computer (Windows XP Home Edition w/SP2) and loaded up all of the available updates to the OS, Internet Explorer, etc. Next, I installed Office 2003 Professional. I disabled Messenger integration with Outlook 2003 as discussed in other posts here. Next I installed MSN Messenger 6.2 and it seemed to run properly as a stand-alone application. So I re-enabled Messenger integration on Outlook 2003. The next time I booted up and ran Outlook, the Messenger icon appeared in the taskbar...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

WdfUsbTargetDeviceCreate creates NULL Control Pipe Handle
Hi, We have a usb composite device which has one mass storage interface and another as a network interface. We are developing a WDF driver (NDIS-USB) for the network interface. Immediately after WdfUsbTargetDeviceCreate if I break into the debugger and examine the newly created device, then I see that the Control Pipe Handle is NULL! Here is the actual output: -------- kd> !WDFUSBDEVICE 0x0000057f`fe5905f8 WDFUSBDEVICE 0000057ffe5905f8 ============================= Config descriptor fffffa80037216b0, device descriptor fffffa8001a6fb58 Control USBD_PIPE_HANDLE 000000000000...

How to make return address labels in Publisher?
Can you do the title above in Publisher? Sure. I've done them in different label sizes. What size labels are you interested in? -- Don Vancouver, USA "Robert" <Robert@discussions.microsoft.com> wrote in message news:23DBCD56-027A-406F-B9D1-3EF45B8E33C0@microsoft.com... > Can you do the title above in Publisher? Thank you. I use the 2.5" x 1" size. it is a standard Avery Label size. "Don Schmidt" wrote: > Sure. I've done them in different label sizes. What size labels are you > interested in? > > > -- > Don > Van...

Purchase Order 2.0.0127
Am I the only one having issues with the upgrade? 1) I create a purchase order 2) I click on add item 3) I type in the supplier ILC, and nothing comes up (ONLY on some items)! 4) I click on all items, and find the item, and add it! 5) When I click on OK the item is added to the purchase order, along with the supplier ILC! OR When I click on all items, even though the item may be in the database, I can't find it to add to the PO, unless I close out the PO and re-open it??? This started happening only since I upgraded the the 2.0.0127! -- Thank You Vince :) -- Thank You Vince :) Yes...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the $ symbol at the beginning of the type and add text at the end of the type to look like this $#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the $ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

RMS 2.0 matrix dimensions are annoying, but help is available
For reasons I don't understand, MS saw fit in RMS 2.0 to use dimensions data for matrix components that is far less accessible to users than Sub Descriptions are. For instance, I can't edit assign a dimension value to an existing item I have added to a matrix. I can't see a reason for using Dimensions with limitations like this as using Sub Descs. to describe matrix "dimensions" worked fine previously. Does anyone know why MS did this? It's annoying! Digital Retail Solutions (DRS) has a product called Power Ops (Build 2.2.0003). It's help file mentions (se...

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Upgrade GP 9.0 SP2 to GP 10.0 Advice
Hi, We are planning our upgrade of GP9.0 SP2 to GP10.0. We are running SQL 2000 and would like to also upgrade to SQL 2005. I am using the following url (and read some threads on this forum): https://mbs.microsoft.com/customersource/support/knowledgebase/hottopics/hot_topic_updatingmicrosoftdynamicsgp10.htm?printpage=false (page: Updating to Microsoft Dynamics GP 10.0) The chart Microsoft provides says I can go from 9.00.0281 (which is SP2) to 10.0 or any patch install. What does "any patch install" mean? Does this mean I can't go directly to 10.0 Feature Pack 1? Would i...

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...