Please, Is there anybody that knows how to calculate in pivot table, % on grand total column only, not all of cells

type1 type2 type3 type4 Grand Total

agent1 4 4

agent2 1 5 129 135

agent3 6 5 9 904 924

agent4 6 1 3 87 97

Grand Total 12 7 17 1124 1160

Thaks a lot

type1 type2 type3 type4 Grand Total

agent1 4 4

agent2 1 5 129 135

agent3 6 5 9 904 924

agent4 6 1 3 87 97

Grand Total 12 7 17 1124 1160

Thaks a lot

- #DIV/0! Error On Grand Total Column In Pivot Table
- Moving grand total columns in pivot table
- Pivot Table Percentage of Grand Total Column
- Pivot table subtotals on alternate rows...
- Macro to pull data from a pivot table based on certain criteria
- Reference Pivot Tables Grand Total Column
- Splitting Grand Total column in Pivot
- Pivot tables and Grand averages
- How to include the Grand Total Column in a pivot table chart?
- Pivot Table Calculated Field (Grand total question)
- PIVOT TABLE - GRAND TOTAL COLUMN
- Pivot Table - Grand Total for Rows
- Pivot table and VBA help
- Pivot Tables: grandtotal column needs to be for both average
- Pivot Tables: Summary data on same row
- Top 10 viewing in Pivot Tables - only works on row totals
- Pivot Table Grand Total wrong when using Formulas
- Pivot tables and column data
- Need to rename sheets and automate pivot table and summary table
- Pivot Table Grand Total wrong when using Formulas
- Excel 2007 Pivot Table help required to total columns repeated month
- pivot table and multiple dates - same column
- Retrieving data from two pivot tables based on date (grouped by month and year)
- Pivot-Sort by Grand Total column

I have created a calculated field in a Pivot table and I am getting a #DIV/0! error using the formula:

=IF(AND('Actuals'=0,'Budget'=0),0,('Actuals'/'Budget')-1)

Screenshot:

div error pivot example.jpg

I can understand getting the error on line N, and I can remove that by using the 'On Error' within the Pivot table options. However, I would like to get the Grand Total Variance and I am unable to get this because of the error. The Variance columns calculate fine with a #DIV/0! in the column, but the Grand Total Variance ends up with this error - so the Grand Total columns must be calculated differently.

So my question is, does anyone know how to get the Grand Total Variance to calculate correctly please using a Calculated Field? Thank you in advance of any response.

I would like to have both the horizontal and vertical grand total columns in

my pivot table to appear at the top line and left hand line respectively. At

the moment they are at the bottom row of the table and the furthest column to

the right, meaning I have to scroll to see either of them.

I have only managed to move and sort the actual data columns around, but not

these total columns.

Thanks

to preform a margin analysis on it. Currently I receive a dump of data

from the system which turn is put into the pivot table. The way the

pivot table is currently displayed is as follows:

Sum of Amount Account

Customer 40000 43000 50000 Grand Total

ABC $4,000.00 $(50.00) $(3,000.00) $950.00

PDQ $6,000.00 $(350.00) $(4,000.00) $1,650.00

XYZ $5,000.00 $(500.00) $(3,500.00)

$1,000.00

Grand Total $15,000.00 $(900.00) $(10,500.00) $3,600.00

The 40000 and 43000 are the revenue, 50000 being the cost and Grand

Total is the Margin. What I would like it to do is calculate a

percentage of the Grand Total / (40000+43000) and look something like

this:

Sum of Amount Account

Customer 40000 43000 50000 Grand Total

Margin

ABC $4,000.00 $(50.00) $(3,000.00) $950.00 24%

PDQ $6,000.00 $(350.00) $(4,000.00) $1,650.00

29%

XYZ $5,000.00 $(500.00) $(3,500.00)

$1,000.00 22%

Grand Total $15,000.00 $(900.00) $(10,500.00) $3,600.00 26%

Is there an easy way if at all way to do this with the pivot table.

Right now I have to write the formula outside the pivot able to just

reference the individual cells. Problem with this is if the pivot

table changes after refreshing the data then I may lose that formula or

not cover all the new cells. Help please..

ProdA 2008

2009

ProdB 2008

2009

ProdC 2008

2009

(edit: well, crud - it isn't using the spaces; the 2009's there should be aligned under the 2008's, sorry. And those are both the Row Labels - to the right then would be the actual values in subsequent columns).

Now, if I let the pivot table do a grand total at the bottom, it sums through all those cells. What I *need* is two grand/sub total lines the bottom - one that sums all of the 2008 values only, and another that sums all of the 2009 values only (for comparison/growth calculations).

I'm wanting this because the pivot table (and sub-totals) needs to be dynamic; if we introduce a new product line, the sub-totals need to allow it - I've done this right now by just adding a couple lines with SUMIF statements to get the subtotals I want, but if a new product line is introduced, updating the pivot table will wipe out and overwrite the non-pivot table lines below it.

Any suggestions as to how to add these subtotals (at the bottom of the pivot table) and have them be *part* of the table so they expand/contract as the content of the pivot table does?

Thanks!

What I want to do is write a macro that will look at the task ID and Entry ID in the pivot table and retrieve the total. Then I want it to search the summary table for the same combination and find the team that owns that combination. THan I want it to take the total that it retrieved and put it in the correct row for the taskid/time entry column on the specific team worksheet.

reference that complete column in another sheet. (I do some Countif &

Sumif Formulas on that column) Everything works fine until the source

data changes and the pivot gets updated, which either shrinks or

enlarges the pivot table. When this happens my links to the Grand

Totals Column aren’t valid anymore. How do I accommodate for this

occurrence.

Thanks for any help.

I have posted this question in 2 other forums and nobody seems to understand what I am trying to convey.

I have a pivot table and I would like the table to give me the Grand Average just as it gives the Grand Total, i.e. Grand Total divided by the number of column items displayed.

My source data is structed as follows in the PT: ID # in the Row field, Qtr in the column field and Sum of Price in the Data field. The Grand Total displays the Total sum of price for each ID# over all the qtrs displayed. I want the Grand Average to display the total sum of price for each ID# divided by the number of qtrs displayed.

If I use the Average function for the price rather than the Sum function, it finds the average price of each ID# in the Qtr, that's not what I want it to do. Say I have 20 ID 1s with a sum of Price of $200 for qtr1, the average function gives $200/20=$10, instead, I want the PT to give me total sum being $200 and then divide this by the number of qtrs displayed, in this case only qtr1 is displayed. I can decide to display just 2 qtrs. If the total sum for both qtrs is $500, then the Grand average should be $500/2=$250.

I hope someone will be able to help me out. Thanks a lot in advance.

Kemi

of responses. The pivot table also shows the Grand Total Column.

I can graph the pivot table quickly and simply with the chart wizard (a bar

chart in this instance) and it will display a bar for column A and a bar for

B grouped per row response. Great.

I want to add what is effectively the third column in the pivot - Grand

total column, so I would have 3 bars per row response; A, B and Grand Total.

I can then compare A and B to the total bar.

How do I add/show the grand total in the pivot chart?

Thanks!

I'm using Excel 2000. My pivot table contains calculated fields, the problem

I'm experiencing is that the grand total for the field is almost 2 times

higher than it should be. However, if I look at the individuals rows that

calculation is correct, if I sum the calculated field column (with exception

to the grand total) the sum is correct but the highlighted column doesn't

match it's grand total....not sure why.

I'm not user if the problem is with my source data worksheet, the only thing

I'm doing which maybe an issue is- for the rate column, I have it equal a

cell that's resides on another work sheet.

--

Linda

I am unable to get the the grand total column on the right as shown below. I checked the 'Table Options' and the 'Grand totals for columns' is checked. So I am notsure what else to check.

Thanks

SJ

Data

CUSTOMER WO# Sum of LAB COST Sum of LAB OVHD Sum of MAT COST

FIENG 29599 96,745.00 145,114.00 110,742.00

48804 3,864.00 5,796.00 537.00

49537 - - 3,714.00

49626 18,778.00 28,167.00 8,641.00

FIENG Total 119,387.00 179,077.00 123,634.00

I'm using XL 2002 and I have a pivot table that has "Grand totals for rows"

turned on. This works perfect. However, amongst the rows of data, I have

one row that is static...it simply shows the 'goal' for the respective

column. I don't want that row to show anything in the Grand Total column.

Is there a trick way to defeat the appearance of the Grand Total for just

that row. Goal doesn't need to have any type of calculation performed on

it...it's simply a reference number for the related dat in the its respective

column that it appears in.

Example:

Item Q1 Q2 Q3 Q4 Grand Total

West 10 20 30 40 100

South 15 25 35 45 120

Goal 5 5 5 5 <---- Have nothing appear here.

Any help would be greatly appreciated.

I've got the below code that I recorded on the macro recorder (ugh) it worked fine but now it's broken and it totally confuses me! See the bottom of the post for a link to mrexcel where there's a copy of the source data

It's working but it's giving me:Data Export Import Import/Export (blank) Grand Total Count of 00 - 30 Days 3473 3398 1 6872 Count of 31-45 Days 3473 3398 1 915 7787 Count of 46-60 Days 3473 3398 1 915 7787 Count of 61-90 Days 3473 3398 1 915 7787 Count of 91-180 Days 3473 3398 1 915 7787 Count of 180 Days and Over 3473 3398 1 6872

What I need it to do is give me the SUM of the data not the count. I also need it not to have a blank column as that's strange?! I just literally need import and export. This is what I get for using the recorder but I literally had no idea on pivot tables and VBA!

Code:pivot source data.xls

VB:I have posted this on mr excel under: http://www.mrexcel.com/forum/showthr...27#post3001227If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Please help if you can, much appreciated!

I can do this easily with two pivot tables. The first will count how many survey returns by sales office. The second will count how many surveys offered to each sales office. I get the answer by dividing the grand total column of the first pivot table by the grand total of the second pivot table.

However, since I would like to chart this analysis, the optimum solution would be to use a single pivot table so I can then sort the order of sales office by the percent completed.

See below - If I sort Cust# on Sales with Top 10 on, it will sort on Grand Total column. How can I sort on, say 200801 Sales, but also display Grand Total numbers (which is effectively YTD).

Any help would be appreciated, thanks

******** ******************** ************************************************************************>Microsoft Excel - CHC Customer Sales.xls___Running: 11.0 : OS = Windows XP File Edit View Insert Options Tools Data Window Help AboutG2=

BCDEF2Financial YYYY2008 3 4Sales Period 5Customer numberCustomer200801200802Grand Total6HEB100Heb March Joint Venture$50,338$106,933$157,2717CIT110City Care Ltd - Christchurch$55,540$97,729$153,2698SIC300Sicon Ltd Darfield$108,247$26,621$134,8689LAI100Laing Contractors Limited$47,416$58,822$106,23810WOR450Downer EDI Works Ltd Christchurch$39,379$66,296$105,67511TRE250Trenching Dynamix Ltd$16,694$56,341$73,03512TRE251Pegasus Trenching Dynamix Ltd$21,008$51,390$72,39813CRT150CRT Society - Card Division$35,314$34,263$69,57714MIC500Mico Christchurch$19,892$40,651$60,54315THE150The Isaac Construction Co Ltd$13,093$46,328$59,42116Grand Total $406,921$585,374$992,295Sheet1

[HtmlMaker light Ver1.11] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

is INCORRECT as it reflects only one portion of the formula. My formula is

an "if statement" that if one field is "0" use the other preexisting field.

Is there a way to have the total reflelct all amounts and not just the total

of certain amounts??

I have a pivot table and in the column area I have a field called type. I

have two types that appear RD and RQ. My first issue is I don't want them

added together, and want to get rid of the grand total field in each row of

data. My second question is I want to show the results of RD/RQ as a

percentage and want to show the values of RQ and RD as well as the %.

Thanks for any help.

Marc

i am in need of help. i need to automate an excel file so that the sheets that are automatically generated get automatically renamed upon selection and a pivot table has to automatically be generated too and also a table has to be updated too.

please help.

attached to this msg is the coding that has already been done up but i have to put in the other necessary codings.

the instructions on what is supposed to be "automated are listed below:

1.) Double click on grandtotal of the file Nostro Stats(Assignmed Group) - Sheet 1(with details will be shown). Rename to Details.

2.) Create pivot table based on data from new renamed tab.

Fields to add: OLT_GBM, Assigned Group, Individual Amount, AssignedGroupManager, Ageing, Individual GBP Equiv

3.) Rename sheet with Pivot Table to "Pivot Table".

4.) Based on the Pivot Table details, create and the table in "Summary" sheet.

5.) At Pivot Table sheet, upon double clicking on "grand total", a new sheet will be created which has to be renamed to "Breakdown".

Please help me as i am a noob.

thank you very much

regards

sam

Sub RefreshAllPivots() 'Dim pt As PivotTable '' Variable to store the Pivots in the workbook ' 'Dim ws As Worksheet '' Variable to store the worksheets in the workbook ' ' For Each ws In ActiveWorkbook.Worksheets ' ' we are iterating through all the worksheets in the active workbook ' ' For Each pt In ws.PivotTables ' ' Among the worksheets iterated in the outerloop we are checking for the pivots only ' pt.RefreshTable ' ' if there are any pivot then this line of code will refresh the first pivot stored ' ' in the variable pt ' Next pt ' ' Iterating to the next pivot ' ' Next ws Sheets("Nostro Stats Summary").Select ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh Sheets("Nostro Stats - Prior Day").Select ActiveSheet.PivotTables("PivotTable4").PivotCache.Refresh Sheets("Summary").Select MsgBox "All Pivots Refreshed" End Sub Sub Save_Report() Dim savepath As String Application.DisplayAlerts = False Application.ScreenUpdating = False savepath = Range("svpath") Sheets("Nostro Stats Summary").Select Range("J2:K2").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayHeadings = False Range("A1").Select Sheets("Nostro Stats (Assigned Group)").Select Range("J2:K2").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayHeadings = False Range("A1").Select Sheets("Nostro Stats - Prior Day").Select Range("J2:K2").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayHeadings = False Range("A1").Select Sheets("Internal Control Accounts").Select Range("I2:K2").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ActiveWindow.DisplayHeadings = False Range("A1").Select Sheets("Nostro Stats Summary").Select 'Application.GetSaveAsFilename "Daily_Nostro_Escalation.xls", "Excel files (*.xl*),*.xl*", 1, "Save Today's Nostro File" Sheets("Summary").Delete 'ActiveWorkbook.SaveCopyAs "T:Business Development and MIPersonalSatheeshNostroAnalysisAutomationNostro_OutstandingReports" & _ "Daily_Nostro_Escalation_" & IIf(Len(Day(Date)) = 1, "0" & Day(Date), Day(Date)) & IIf(Len(Month(Date)) = 1, "0" & Month(Date), Month(Date)) & Right(Year(Date), 2) & ".xls" ActiveWorkbook.SaveCopyAs savepath & _ "RBS PLC Daily_Nostro_Escalation_" & IIf(Len(Day(Date)) = 1, "0" & Day(Date), Day(Date)) & IIf(Len(Month(Date)) = 1, "0" & Month(Date), Month(Date)) & Right(Year(Date), 2) & ".xls" ActiveWorkbook.Close saveChanges:=False Application.DisplayAlerts = True Application.ScreenUpdating = True MsgBox "Report Saved" End Sub

is INCORRECT as it reflects only one portion of the formula. My formula is

an "if statement" that if one field is "0" use the other preexisting field.

Is there a way to have the total reflelct all amounts and not just the total

of certain amounts??

ie

col a col b col c col d col e col f

NOV 08 NOV 08 NOV 08 DEC 08 DEC 08

BA101 22 50 40 22 24

BD509 42 48 30 12 20

What I need is a way to total up the month as a summary and hide or remove the duplicate information. for example for BA101 I want it to say BA101 NOV08 total of 112 (22+50+40) and for DEC08 46 (22+24)

I cant make the columns total a summary and remove the duplicate columns. I just want the totals of the month as a summary with the part number to display on a graph later.

Any help would be greatfully appreciated. I have attached a copy of the file, with the sheet 1 containing the original data. Sheet 3 contains the current pivot table created. I want it like this, but to summarise the months as a whole ie row 7 for product titled 17 - has order of 446+38=484, I need some VBA coding to make it display summary totals for product ordered in same month. I had shaded examples where multiple orders show, but need it to count summary for each month for each product.

Thanks in advance.

1. One column with MATERIAL (Part #)

2. Another column could have THREE different dates corresponding to that same material (within three different fiscal years to complicate matters).

3. Column with reason codes used for filtering in pivot table.

*** see columns marked in RED in sheet named "Data" ***

What I need:

1. The existing pivot table is filtered by column "Reason Code" and needs to remain so.

2. I need to have three columns (2008, 2009 and 2010) for the column with the dates "VALID TO". Whereas the fiscal year goes from Sep to Oct. Meaning if a VALID TO date for a materials states "12/31/9999" this belongs in fiscal year 2010. "VALID TO" 09/30/09 belongs in 2009, "VALID TO" 09/30/2008 belongs in 2008 and so forth. There is sheet for this to be added. I played with various scenarios but these are just not working.

3. I need to be able to have the three different dates on the SAME row, not in staggered rows. Meaning: If there was a change made in 08, 09 and 10 then all three years should have the LAST DATE of "VALID TO" in the field - not ALL dates available. As I said, some materials may have multiple dates - I only want the last one UP TO 09/30 of THAT fiscal year. Sometimes there will only be a change in one or two fiscal years.

4. The Pivot Table needs to be dynamic so that if I make a new download of new data it should automatically adjust to the different size of the new database.

For an Excel guru with expert pivot table skills this should be cinch to do in an hour or less. I am (obviously!) no expert and boss man is driving me nuts to make this happen today.

I had to zip the file because it's over 2 MB's, sorry. Uploading doesn't seem to work right now but I'd be happy to send the file to you if you PM me.

If anyone wants to earn a quick buck today I'd even be willing to pay for the help!!

I have a couple of pivot tables on individual worksheets.

1. Sales forecast with item number and description as rows, columns are date grouped by month and year. The values are units forecast to be sold. The user currently enters a date range in a message box and I then run a sql query using the data as criteria. The forecast covers a period of about 6 months in the past up to about 18months in the future.

2. Actual Sales. Same data but obviously we only have actual sales up to the current month.

I need to retrieve the unit sales figure and unit forecast figure for each month and copy them across to a summary sheet one under the other. I cant for the life of me figure out an easy way to do this.

The summary sheet needs 2 rows for each item. One with the forecast figure from the forecast pivot table for that month and one with the actual sales figure from the sales pivot table. there would then be columns for each month of data that I have.

Can someone point me in the right direction of a function I can use to achieve this.

Thanks in advance

Your help is greatly appreciated.

Thanks,

John