Pivot Table Error

Thanks in advance.

I create PivotTables where one of the datafields is a Calced Field.  
Sometimes the resulting calced field return #DIV/0!  because of this the 
resulting tables in the PivotTable display #DIV/0! also instead of the 
totals.  Any way around this problem?
0
Utf
1/14/2010 8:01:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
942 Views

Similar Articles

[PageSpeed] 0

Fix your source data to remove the #DIV/0.

=if(A1 = 0, "", B1/A1)

divide by zero yeilds an indeterminate mathematical result. As such XL will 
not use it in any calculations as the result of those calcualtions will also 
be indeterminate...
-- 
HTH...

Jim Thomlinson


"RussellT" wrote:

> Thanks in advance.
> 
> I create PivotTables where one of the datafields is a Calced Field.  
> Sometimes the resulting calced field return #DIV/0!  because of this the 
> resulting tables in the PivotTable display #DIV/0! also instead of the 
> totals.  Any way around this problem?
0
Utf
1/14/2010 8:38:01 PM
The error is a result of the following VBA code not a formula in a cell.  any 
suggestions?
 
       ActiveSheet.PivotTables("PivotTable2").CalculatedFields.Add "Weighted 
Avg Price", _
           "=rev / PosSold"
         With ActiveSheet.PivotTables("PivotTable2").PivotFields("Weighted 
Avg Price")
            .Orientation = xlDataField
            .NumberFormat = "$#,##0"
         End With
 

"Jim Thomlinson" wrote:

> Fix your source data to remove the #DIV/0.
> 
> =if(A1 = 0, "", B1/A1)
> 
> divide by zero yeilds an indeterminate mathematical result. As such XL will 
> not use it in any calculations as the result of those calcualtions will also 
> be indeterminate...
> -- 
> HTH...
> 
> Jim Thomlinson
> 
> 
> "RussellT" wrote:
> 
> > Thanks in advance.
> > 
> > I create PivotTables where one of the datafields is a Calced Field.  
> > Sometimes the resulting calced field return #DIV/0!  because of this the 
> > resulting tables in the PivotTable display #DIV/0! also instead of the 
> > totals.  Any way around this problem?
0
Utf
1/14/2010 11:39:01 PM
Reply:

Similar Artilces:

Period Sales Analysis Report Error
I updated to ver 10 from 9 SP2 a couple weeks ago and everything has worked relatively well (Thanks to all newsgroup members who assisted) I am getting an error "Error in equation 'Net Sales'". Has anyone else seen this and what did you do? I'm guessing this is a modified report? Open the report in Report Writer and check out the Calculated field 'Net Sales'. You will probably find an error there. If I can't figure it out just by looking, I'll look at the v 9 report to see what the calc was trying to come up with and then correct the formula in v ...

After Re-Install
HELP!!! After a crash and a re-install of Office we are now getting user name and password errors in Outlook 2002. We can access the same accounts via a www interface with the same account information. PLEASE help. I've downloaded all the service packs for XP and Office and I've tried un-installing and re-installing including deleting all of the profile information I can find and deleting Outlook's registry, but to no avail. I'm obviously missing something. Any help would be fabulous. Please e-mail Monica@ViaSollertia.co.nz Thanks so much!!! Monica ...

Exchange Extension Error
I'm trying update an email address and an error pops up. Microsoft Active Directory - Exchange Extension A Local Error has occured. Facility: Win32 ID no:8007203b Microsoft Active Directory - Exchange Extension Everything else seems to be working ok. Any ideas? Thanks, Q does this apply? http://support.microsoft.com/default.aspx?scid=kb;en-us;329642 "Quan" <anonymous@discussions.microsoft.com> wrote in message news:1a2001c4abc4$e8f2ea30$7d02280a@phx.gbl... > I'm trying update an email address and an error pops up. > > Microsoft Active Directory - Exchange...

Hidden files in Ms-Query cause ODBC connect errors or Query is wac
I have a Query fetch_from_bob that moves data (collapses records,unique key search, etc...) from excel spreadsheet "bob" to excel spreadsheet "fred". Fred and bob live in the same directory" c:\123directory" on the same computer. I allow this query to execute/refresh data automatically when one of the query paramaters is changed. This works great. I also have a macro that invokes the query to refresh the data whenever the user desires. This works great as well. The macro literally has all the VB code to execute the query. But I'm just a part time progra...

PLEASE HELP--ERROR MESSSAGE
I have two error messages. The reason why I am getting this is because I got a new computer and I copied my old outlook information and put it in the new computer. Everytime I open my outlook this message appears and the only thin I have to do is click ok and everything works. But how do I fix it? I did reload outlook serval times and there error message still comes even if I do not put my old information in it. Error 1: "The add-in "C:\Program Files\Microsoft Office\Office\SBCMSYNC.DLL" could not be installed or loaded. This problem may be resolved by using Detect and Repai...

Calculated fields in Pivot Tables
Is it possible to use an "if" formula when creating a calculated field? I have an existing field in my pivot table called commissions and I want to create a new field that will give me a 1 if for each row if commissions are over 4 and a 0 if they are under 4. Is this possible? What is the formula? It seems ok Try something like : =IF(Commissions>=4,1,0) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "lj" <lj@spu.edu> wrote in message news:1132008509.143327.194520@g47g2000cwa.googlegroups.com... > Is it possible to u...

GUID/Outlook format link giving error "Unable to open the seleted folder or item"
Hi, I have created custom forms in multiple public Outlook folders and when I send the GUID type link to the user, they receive an error: "Unable to open the seleted folder or item". The same happens if I send the Outlook type link. However, if I ask the user to go to the folder and find the appropriate posting, then the form opens and after that the link works, too. Is this due to some Exchange server setting? My clients are not the type that tolerate opening the form the long way, they want a one click solution. Please help, this is driving my team crazy. Any ideas are welcome...

550 5.7.1 relay error messae
I have select number of Clients (3) out of 27 who we can not send emails to, they are in a separate domain from ours. Your message did not reach some or all of the intended recipients. Subject: test 4:35pm Sent: 2/27/2007 4:34 PM The following recipient(s) could not be reached: Sean ***** on 2/27/2007 4:34 PM You do not have permission to send to this recipient. For assistance, contact your system administrator. <mail.n*******.org #5.7.1 smtp;550 5.7.1 Unable to relay for tsc16.****.****@***.local> Rhodes.messiah@gmail.com wrote in news:1172615546.953350.313930 @j...

Rules in Error
When I open Outlook 2002 I receive a dialogue box which states Rules in Error - Server Requested Client Action MoveCopy, Unable to create dest msg. I don't know where this came from. Thanks for the help in advance. ...

error message 01-01-10
Unable to send or receive messages for the Hotmail (**********) account. To send and receive messages in your Hotmail account, go to http://hotmail.live.com on the Web, or try again later. To get help from Windows Live Customer Support, go to http://support.live.com and click Windows Live Mail in the list of services. Subject 'Greetings' Windows Live Mail Error ID: 0x80070057 I want to know how to dump this popup. I don't need to send the message and everytime I open windows live mail, this error message regarding an unable to compete task shows up. How do I ge...

Updating large pivot source data
Hello, I'm experiencing the following problem with a very simple macro in Excel 2007 to update pivot table data source. That is, my data has more than 74000 rows and I'm trying to update the source data with the following code: Sub Macro1() Dim rng1 As Range Set rng1 = Sheets("Test_sheet").Range("A1:A74000") ActiveSheet.PivotTables("Pivot-taulukko1").ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng1, _ Version:=xlPivotTableVersion12) End Sub The problem is that the code ...

Purchase Order Entry Error Message
While in version 8.0g34 and Purchase Order Entry window opened. User accidentally minimized the window instead of closing the window. User closed window while minimized and the following message appeared. Unhandled script exception Object has no reference EXCEPTION_CLASS_SCRIPT_BAD_PARAM OK User clicks ok approx. 10 times and the window finally closes. Why does this happen? Perhaps a fix should be applied. Thanks! ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree...

C++ runtime error
When I open Outlook I get the following error: ______________________________________ Mcrosoft Visual C++ Runtime Libary Runtime Error! Progam: C:\Progra~1\Micros~2\Office10\Outlook.exe This application has requested the runtime to terminate it in an unusual way. Please contact the application's support team for more information. ______________________________________ I have already tried to run a repai and to uninstall and reinstall and I still get the same error. Any ideas? I GET THE SAME MESSAGE, BUT IT HAS ONLY STARTED SINCE I UPGRADED TO NORTON ANTIVIRUS 2004 TRIED NUNERO...

Conversion error
Hi, I have a table called stats that has the following columns: jobname, ahours, aminutes, aseconds, amilliseconds. I need to get an average of each job ran but I need to be able to concatenate ahours, aminutes, aseconds, amillisecons into one column. I am trying to run a simple query: SELECT JobName, CONVERT(NVARCHAR (100),(AVG (AHours) + ':' + AVG (AMinutes) + ':' + AVG (ASeconds) + ':' + AVG (AMilliseconds))) AS 'Average Time' FROM stats GROUP BY JobName I am getting the following error: Msg 245, Level 16, State 1, Line 1 Conversion failed whe...

Visio Error
I was working in Visio on a document and I accidently hit my power switch (the one on the floor on the cord) and my PC turned off. When my machine rebooted and I tried to access the same Visio file, a message tells me that I cannot access the file because it is NOT a Visio file???? I tried renaming it, didn't help. This is the second time this happened to me and I can't understand why the program is not recognizing the file. It's very frustrating....does anyone have any answers to this? - Sally Another option is to turn on "Autorecovery" It will automatic...

pivot table subtotals
I'm stuck trying to develop subtotals in a pivot table. I hav successfully placed Sums of the individual elements, but I'd like "Subtotal" line for specific types of elements i.e.: 1/22 1/23 1/24 Group1 Apples Oranges Pears Group1 Sum Group2 Corn Carrots Beans Group2 Sum Grand Total All of thee above rows/lines calculate just fine except the GroupX Su lines? Does anyone have any suggestions on how I can get subtotals to wor correctly? Do -- Message posted from http://www.ExcelForum.com Double-click on the field button at the top of the Group column. Un...

error message when trying to install WMP 10 or 11
My operating system is Windows XP Home Edition 2002. I'm trying to download either WMP 10 or 11 and I get a message saying it was not possible to complete setup, see webhelp for more assistance, however there is no additional information when I click Web help. the Error message is 8007F0DA. Can anyone help me? Thanks. On Tue, 30 Mar 2010 07:54:01 -0700, Nate <Nate@discussions.microsoft.com> wrote: > >My operating system is Windows XP Home Edition 2002. I'm trying to download >either WMP 10 or 11 and I get a message saying it was not possible to &g...

Error saving Excel files in a network drive
I have a problem saving Excel files onto a network drive. I get an error saying it was imposible to save the file. It creates a temporary file and then I have to open it and save it as a new document. This issue doesn�t occur saving the file in my hard disk. This happens with "Full control" access to the shared folder... I have Windows XP and Office 2000. Thanks in advance Mateo. Hi Mateo, > I have a problem saving Excel files onto a network drive. I get an error > saying it was imposible to save the file. It creates a temporary file and > then I have to open it and sav...

Calculate Checks Error
I have a client that has a problem when calculating payroll checks. They built the batch and everything was fine. They checked the sheet for errors-which there were none. Then they went to Calculate payroll and the screen came up with No warnings and No errors. But when they hit the OK button they received the message "The checks cannot be calculated, errors were found when building the check." The user then logged in as user sa and was able to calculate checks without the error. Any suggestions on why the user receives the error? Thanks, Jocelyn "Jocelyn&q...

error message C00D11B1,original code 80004005
Running WMP 11, Windows Vista Service Pak 2 Home Premium,32 bit operating system, DVD rw file system udf 2.01 and driver version 6.0.6002.18005. All I am trying to do is burn my digital camera videos that I put in WMP 11 to a cd-r . Something is set wrong somewhere because my videos are MOV. and I have to do the right click and Open With classic media player to see them. I have the support info.but it is a bit lengthy to copy to this post.It does have the codecs decoders ect. Don't see MOV. on the list. I have Quick Time also. Thanks for any help! -- Deb I'm pret...

VSTO 2005: Error in Schema Files Generation
In XML schema generation from a WordML document, VSTO 2005 VSTO creates 6 files (general naming pattern): XML.XSD ... XML5.XSD. For our file names we use a special syntax for additional information on the project status. - Syntax for file names: {PRE}={TOPIC}_{POST}.{STATUS}.{FORMAT} Now, VSTO 2005 modifies its general naming pattern to XML.{STATUS}{I}.{FORMAT}. - Example - XML.ABCD.XSD - XML.ABCD1.XSD (instead of XML1.ABCD.XSD) - ... - XML.ABCD5.XSD (instead of XML5.ABCD.XSD) I consider this behavior of VSTO 2005 as a programming error. - Is this consideration correct? - How ...

MSExchangeIS 1021 error repeating every minute
I get about 5 of these errors popping up every minute on my Exchange server. When I open it, they all say: DOMAIN/user was unable to connect as /o=Domain/ou=SAF Administrative Group/cn=Recipients/cn=epage. Error 0x3f2. I have been through the following articles already and no luck: http://support.microsoft.com/default.aspx?scid=kb;en-us;290647 http://support.microsoft.com/default.aspx?scid=kb;en-us;314494 http://support.microsoft.com/default.aspx?scid=kb;en-us;810907 http://support.microsoft.com/default.aspx?scid=kb;en-us;832215 http://support.microsoft.com/default.aspx?scid=kb;en-us;83464...

Counting Blank Cells in Pivot Table
How do I have a Pivot Table count the blank cells as well as the other cells? The Pivot table I created counts everything that had data (A, B, C, D, etc...) but does not count the blank cells. Thank you, Jack As answered in microsoft.public.excel.charting: To count the blanks you could use a space character, or the formula ="", in the source data, instead of leaving the cells blank. The pivot table will be able to count those. Jack wrote: > How do I have a Pivot Table count the blank cells as well > as the other cells? The Pivot table I created counts > everythi...

Specify data for use in FRx pivot tables
How do I specify what data fields appear in an Excel pivot table (or ..cub file) from FRx? I want to export the natural account number into the pivot table to sort on. TIA ...

SQL Error creating new Quote
After a few customizations & data import (with the migration tool), I am stuck with the error underneath. I can see the Quotes that I have imported, but when I try to create a new one, I get the error underneath. I suspect something with the Quote id. but can someone read what exactly causes the error? Thanks, Egbert Stack Trace: [COMException (0x80044150): Exception from HRESULT: 0x80044150.] Microsoft.Crm.Platform.ComProxy.CRMQuoteClass.Create(CUserAuth& Caller, String QuoteXml) +0 Microsoft.Crm.Application.Platform.Quote.InternalCreate(String xml) +48 Microsoft.Crm.Ap...