if A1=Null and B1=A1 why is result in B1=0 ??

if A1=Null and B1=A1 why is result in B1=0 ??
because of this I get wrong result for average calculations: average for 
(6,8,0) <> average for (6,8,null) 
0
WGeorg (1)
3/18/2005 11:37:07 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
1096 Views

Similar Articles

[PageSpeed] 29

Use

=IF(A1="","",A1)

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"WGeorg" <WGeorg@discussions.microsoft.com> wrote in message
news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com...
> if A1=Null and B1=A1 why is result in B1=0 ??
> because of this I get wrong result for average calculations: average for
> (6,8,0) <> average for (6,8,null)


0
bob.phillips1 (6510)
3/18/2005 11:47:30 AM
Use this in B1.........

=IF(OR(A1=0,A1=""),"",A1)

Vaya con Dios,
Chuck, CABGx3


"WGeorg" <WGeorg@discussions.microsoft.com> wrote in message
news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com...
> if A1=Null and B1=A1 why is result in B1=0 ??
> because of this I get wrong result for average calculations: average for
> (6,8,0) <> average for (6,8,null)


0
croberts (1377)
3/18/2005 11:58:50 AM
A formula must return something.  Since Excel has no null value 
(long-standing complaint) that the formula can return, it is coerced to 
zero.  As others have noted, you can use an =IF() formula to handle this 
situation and "" will look blank and be ignored by range functions such 
as AVERAGE(), etc.  Unfortunately it will create an error with binary 
operators such as +-*/ and will plot as zero.  #N/A or equivalently NA() 
will not plot, but will be propagated through formulas.  Many answers, 
depending on what you want to do with it.

Jerry

WGeorg wrote:

> if A1=Null and B1=A1 why is result in B1=0 ??
> because of this I get wrong result for average calculations: average for 
> (6,8,0) <> average for (6,8,null)

0
post_a_reply (1395)
3/18/2005 12:32:28 PM
Reply:

Similar Artilces:

OE 6.0 Question
Recently, OE stopped counting new messages in my Inbox, as well as highlighting all folders with new messages. Any idea? This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x microsoft.public.windows.inetexplorer.ie55.outlookexpress for OE 5.5x microsoft.pub...

How do I set home to A1 instead of K1?
If I press the home key on an existing spreadsheet, it goes to K1 instead of to A1. Is there a way I can fix this? If not, do I have another possible problem on that spreadsheet? I suspect that you have the window 'frozen' at column K. In pre-2007 Excel menu: Window --> Unfreeze Panes Excel 2007: [View] tab, Window Group, in the Freeze Panes pull-down, choose Unfreeze Panes "Julie D" wrote: > If I press the home key on an existing spreadsheet, it goes to K1 instead of > to A1. Is there a way I can fix this? If not, do I have another possibl...

RMS v2.0 causing spontaneous shutdowns?
I don't have any 'hard' evidence but I've heard more complaints about spontaneous shutdowns in the last few days than in my entire life prior to installing RMS v2.0 I've had two of four Store Ops servers (Windows 2003 Server Std Ed) spontaneously shutdown and at least two 'regular' PCs. They give no warning. Screen goes black just like power failure, then imediately the machine is starting up. Anybody else? Tom -- Stop fishing for e-mail This is a multi-part message in MIME format. ------=_NextPart_000_0D8C_01C79D99.154096A0 Content-Type: text/plain; c...

Can't move mailboxes between servers: MAPI 1.0 ID no: 80040111-0286-00000000
How do I move mailboxes between servers? When I try from ESM right clicking on the mailbox and going through the wizard I get this error: <summary isWarning="false" errorCode="0xc1050000"> The information store could not be opened. The logon to the Microsoft Exchange Server computer failed. MAPI 1.0 ID no: 80040111-0286-00000000 - <details> Anyone have any ideas? Do you have Exchange 2003 or Exchange 2000? Your Global Catalog was recently installed? Did you move the Exchange Server from Organization Unit in Active Directory? Please, send me informatio...

MSCRMEmail Event 0 error
We are running CRM 4.0 on a standalone server, and we also have a standalone Exchange 2003 server. On the mail server, we're getting this error, but have no clue as to how to fix it. What's odd is that CRM mail routing seems to be fine. Thoughts? #26090 - An error occurred while opening mailbox crmadmin@domain.com. Microsoft.Crm.Tools.Email.Providers.EmailException: The remote Microsoft Exchange e-mail server returned the error "(401) Unauthorized". Verify that you have permission to connect to the mailbox. ---> System.Net.WebException: The remote server returned a...

Workflow Monitor 3.0 Issues
We upgraded our CRM system from 1.2 to 3.0. We have had workflow in place with 1.2 and now with 3.0. Now (in 3.0) for some reason on select running processes that display in Workflow Monitor we are unable to take any action. We cannot pause, cancel, jump to, etc... Through troubleshooting we determined that the processes that we are unable to perform any action with via Workflow Monitor are the Workflow processes that were currently running during the upgrade to 3.0. Any workflow process that has started POST UPGRADE is functioning fine and we are able to perform any of the actions ...

Cell A1 active cell upon opening
How do I make cell A1 the active cell upon opening a previously saved file without having to ensure that it is the active cell when saving. Are there any default settings or do I need to write a macro, if so how? -- darkcity1965 ------------------------------------------------------------------------ darkcity1965's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15712 View this thread: http://www.excelforum.com/showthread.php?threadid=272389 Hi you need VBA for this. e.g. put the following code in your workbook module (not in a standard module): sub workboo...

Return all sheets to cell A1
How do you return all sheets of a workbook to cell A1 all together? glenlee, one way, group the sheets and select A1, ungroup the sheets -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "glenlee" <glenlee@nospam.com> wrote in message news:4335dc27.14201560@news.verizon.net... > How do you return all sheets of a workbook to cell A1 all together? Grouping the sheets is not working....only one sheet goes back to cell...

The office assistant requires Microsoft Agent 2.0 or later.
Error message: The office assistant requires Microsoft Agent 2.0 or later. This product is available on the office system pack. Outlook 2002 ...

Business Portal 4.0
Has GP sent out the Business Portal 4.0? Myself and my clients have not recieved it. If they haven't sent it out, how can we get a copy? Thanks. Tracey: BP 4 has been out for a while. If you do not have a copy download it from PartnerSource or CustomerSource. Note that there is a service pack for it already as well. "Tracey D" wrote: > Has GP sent out the Business Portal 4.0? Myself and my clients have not > recieved it. If they haven't sent it out, how can we get a copy? > > Thanks. I can find service packs, white papers, install instructions, bu...

Duplicate Detection Issue in CRM 4.0
Why is it that duplicate detection across Accounts and Contacts includes Inactive records in the duplicate search? The situation I have is: Two contacts are merged, the non-master is deactivated. The next time the Master contact is updated the Duplicate Detection comes up showing that it is a duplicate with the deactiveated contact. I am not the only one having this issue: http://social.microsoft.com/Forums/en-US/crm/thread/cda123b0-5276-4694-82b3-1a7e40e0919f Is there a way to stop inactive records from being searched in a Duplicate Detection search? Thanks in advance. TJ On Jun 10, 6:09...

Converting Text to Number by formula (Opposite to Text("000",A1) )
Hi All I have several lists of data which have Centres Numbers, some formatted as Text (i.e 012 format) and others formatted like Numbers (i.e 12 format). Now I know how to convert a number to text by formula ( Text ("000",A1) ) however I do not know how to convert the other way around to make Text a single number. The only way I know to do this is text to columns or by clicking the smart tag to convert them, however I want to try and achieve this by formula as I have several data lists to paste in, I would like it to do it automatically. Hope someone can help! Kind regards Adam...

If formula: if A1 is greater than B1 and A1 is greater than zero..
Hello everyone, this is what I came up with so far: =IF(G2>=F2, "YES", "NO") now I have to add F2 must also be greater than zero before putting a "YES" how do i do that? thank you :) Jason I would add a second IF statement in there: =IF(F2>0, (IF(G2>=F2,"YES","NO"), "NO") ) I think the format is correct, but did not test it..... "Jason" wrote: > Hello everyone, > this is what I came up with so far: > > =IF(G2>=F2, "YES", &quo...

Portal 2.0 export to excel non functional
Has anyone else run into issues with BP where the sharepoint base 'export to excel' functionality does not work ? I have talked to MS product support for BP, and Sharepoint and at the end of much 'try this/try that' they have told me to reinstall. Well, thats all well and fine but the users have in the meantime placed significant content on the system and I am not yet comfortable with backup and restoring this system. More worrisome is the fact that attempting to create a test box installed in the exact same fashion as the first to test restoring resulted in a non-fun...

How can I test for nulls within a range without specifying each ce
This is my (disgustingly beginner's) function: =IF(AND('Worksheet2'!A5:A12="", 'Worksheet2'!B5:B12=""), "Both Columns Null", IF(AND('Worksheet2'!A5:A12<>"", 'Worksheet2'!B5:B12=""), "All of column A range populated", "Some of Column B range populated")) The important part is where I am specifying the range in a different worksheet ('Worksheet2'!A5:A12="") and testing the cells for nulls. The rest of the function is just retesting the same cells for di...

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

Null Is Null
I've got a text box in a report that's not returning properly. Here's its Control Source: =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]! [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]! [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"") This returns #Error. It's definitely my IIf statement as it returns properly when removed however there are times when [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this subreport will not appear if no records exist. I suspect that this is the problem. How can I write this Control Source t...

ADO Find and Bookmark, inconsistent results any suggestions?
Hi All, i'm bit frustrated as all my users are reporting a weird behaviour from one of our access database. It is an ADP application on SQL Express 2005 as backend using Access 2003. No problem of this sort has been reported whatsoever, and no change have been done to either the dbase structure or the vba code since the problem came out. to be very concise, when a form open it will load -using the bookmark method- the first record available. this is the code: Dim rs As New ADODB.Recordset Set rs = Me.Recordset.Clone rs.MoveFirst rs.Find "[IdItemOrdered] = " &...

eConnect 8.0 biztalk adapter 2004
Why doesn't the installation cd come with at least one sample project for eConnect 8.0 BizTalk Adapter 2004? It has a sample project for eConnect 8.0 BizTalk Adapter 2002 and Message Queing but I am not using 2002 and their is a difference between 2002 and 2004. Is there a sample project for eConnect 8.0 using BizTalk Adapter 2004 that I can download? ...

show results of two pivot charts
I have two sets of data ranging from 2000 to 2006 split into monthly totals One set relates to Units produced per month; the other to units sold per month I want to show both sets of data in a Pivot table together on one linear pivot chart. Anybody know how I can manage this ? thanks In the first step of the PivotTable wizard, use the 'Multiple consolidation ranges' option. In the next step you will be asked about page fields. Select the 'I will create the page fields' option. In the next step, add as many ranges as you want -- well, in your case that will be two. ...

Requeriment to client for GP 8.0
Hi, I really aprecciate if anyone can tell me what are the process that run in a client for GP8.0? thanks, -- Evelin. Evelin, Client minimum requirements for GP 8.0 are on page 18 of the Installation Instructions manual - is this what you're asking? Not sure what you mean by 'what are the process that run'....? -- Victoria Yudin Microsoft MVP - Great Plains "Ana Evelin Rosa" <anar@segacorp.com> wrote in message news:6CBB0050-5F13-4A50-BFE6-D664B2A250EC@microsoft.com... > Hi, > > I really aprecciate if anyone can tell me what are the process t...

blank instead of DIV/0!
I have a formula like this =A1/COUNT(F3:F124) When cells F3:F124 are emty, my formula returns #DIV/0! Can that be made a blank cell instead of #DIV/0! ? rgd Bill Gras Hi! Here's one way: =IF(COUNT(F3:F124)=0,"",A1/COUNT(F3:F124)) Biff >-----Original Message----- >I have a formula like this =A1/COUNT(F3:F124) >When cells F3:F124 are emty, my formula returns #DIV/0! >Can that be made a blank cell instead of #DIV/0! ? > >rgd Bill Gras >. > Try this one too. (Assume Cell with formula is A1) Go to 'Conditional Formatting' for this cell , In...

Integration Manager
Hello, I have an integration that uses DTS to load the source file into a temp table for the integration to read and add to the GL. If the source (which contains a SQL query to a view on the temp table) is empty (no results returned from query), how can I catch that to use for error-handling? Thanks You can add the following VBScript code in your Before Integration event script: Option Explicit Dim oConn, oRS, totalRecords set oConn = CreateObject("ADODB.Connection") oConn.Connectionstring = "database=YourCompanyDatabaseHere" GPConnection.Open(oConn) set oRS...

A1 reference
I could swear that I have seen the reference A1 refer to the cell that is currently being evaluated (not cell A1 itself) in some VBA code. Does that sound right? Anybody know what I'm talking about? "A1" in reference to a range means the first cell of the range. For instance, if the range C1:F10 were selected when this code is run: Selection.Range("A1").Select C1 would be selected. The macro recorder frequently produces code with the A1 reference. I don't think most humans do it that way; I know I don't. -- Jim Rech Excel MVP "mark1" &l...

econnect POS integration w GP 9.0 troubleshooting
We have engaged a 3rd party to write an integration from POS to GP 9.0. They are experiencing difficulties getting purchases and orders to integration (customers and vendors integrate fine). Anyone experience this before? Know any econnect gurus that we could contract with to troubleshoot? Thanks Donna I recently completed a successful eConnect implementation on GP 9 for a client in which an external application creates sales orders and purchase orders in GP. I'd be happy to talk to you to see if I could provide any assistance. Sincerely, David Rueter drueter@assyst.com "d...