Grand Average in Pivot Table?

Hello,
I'm pretty much a Pivot Table idiot, so I might be overlooking something 
horribly obvious, but I'm trying to have a "Grand Average" column and row in 
a Pivot Table instead of a "Grand Total."  I see the Grand Total checkboxes 
in the Options dialog box, but I've yet to stumble upon a way to specify a 
function other than Sum.  

Is this possible?  If so, can someone let me know how?

Excel2000.

If I didn't explain that clearly enough, let me know and I can give an 
example.  Thanks for the help.

Mike
0
1/13/2006 6:16:02 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
587 Views

Similar Articles

[PageSpeed] 57

When you drag the number header into the data field it defaults to sum,
double click it and select average instead or if you already have sum in
your pivot, right click anywhere in the number column (Total column) and
select field settings, there you can also change to average


-- 

Regards,

Peo Sjoblom


"mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
news:8BB0E89E-9BAC-44B7-9E82-EA1DDFCAFDFB@microsoft.com...
> Hello,
> I'm pretty much a Pivot Table idiot, so I might be overlooking something
> horribly obvious, but I'm trying to have a "Grand Average" column and row
in
> a Pivot Table instead of a "Grand Total."  I see the Grand Total
checkboxes
> in the Options dialog box, but I've yet to stumble upon a way to specify a
> function other than Sum.
>
> Is this possible?  If so, can someone let me know how?
>
> Excel2000.
>
> If I didn't explain that clearly enough, let me know and I can give an
> example.  Thanks for the help.
>
> Mike


0
terre081 (3244)
1/13/2006 7:37:56 PM
Peo,
That will change the function used to calculate the data within the table.  
I want that to be a sum, but I want the "Grand Total" column to be an 
average.  
Right now, I have Month as the row field, Year as the column field, and Sum 
of Milage as the Data Field.  So it looks sort of like this:

                         2003   2004   2005   Grand Total
Jan                     500    400     500        1400
Feb                     400    300     500        1200 
Grand Total          900    700    1000       2600

I want to continue to use the Sum function for calculating the data in the 
table, but instead of summing rows and columns, I want to average the rows 
and columns, so it would look like this:

                         2003   2004   2005   Grand Avg
Jan                     500    400     500        466.67
Feb                     400    300     500        400 
Grand Avg           450    350     500        433.33

That way, the actual rows and columns don't change, but the summary values 
change.

I hope that makes more sense.

Thanks again.

Mike

"Peo Sjoblom" wrote:

> When you drag the number header into the data field it defaults to sum,
> double click it and select average instead or if you already have sum in
> your pivot, right click anywhere in the number column (Total column) and
> select field settings, there you can also change to average
> 
> 
> -- 
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
> "mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
> news:8BB0E89E-9BAC-44B7-9E82-EA1DDFCAFDFB@microsoft.com...
> > Hello,
> > I'm pretty much a Pivot Table idiot, so I might be overlooking something
> > horribly obvious, but I'm trying to have a "Grand Average" column and row
> in
> > a Pivot Table instead of a "Grand Total."  I see the Grand Total
> checkboxes
> > in the Options dialog box, but I've yet to stumble upon a way to specify a
> > function other than Sum.
> >
> > Is this possible?  If so, can someone let me know how?
> >
> > Excel2000.
> >
> > If I didn't explain that clearly enough, let me know and I can give an
> > example.  Thanks for the help.
> >
> > Mike
> 
> 
> 
0
1/13/2006 8:32:02 PM
Hi Mike

I'm assuming that you have more than one entry for each month in your 
source table.
If you don't but have just the one value for each month, then selecting 
Average in place of Sum as Peo suggests will work, because one entry of 
500 is the same as 500/1.

If you have a series of mileages for each month, which are being summed 
by the PT to give the total for each month, I think the only way you 
will get what you want is to drag mileage to the data area a second 
time, and make the second occurrence Average.
On the PT, drag the Data button tot the Total column and you will see 
the values side by side, with totals and averages both appearing in the 
Grand Totals.
If you want the months total and the average for the month to be shown 
in the column next to it, drag the Year button to the left of the Data 
button.

-- 
Regards

Roger Govier


"mikelee101" <mikelee101@discussions.microsoft.com> wrote in message 
news:FC37F86D-744A-4CAA-9674-47AD23C32EB1@microsoft.com...
> Peo,
> That will change the function used to calculate the data within the 
> table.
> I want that to be a sum, but I want the "Grand Total" column to be an
> average.
> Right now, I have Month as the row field, Year as the column field, 
> and Sum
> of Milage as the Data Field.  So it looks sort of like this:
>
>                         2003   2004   2005   Grand Total
> Jan                     500    400     500        1400
> Feb                     400    300     500        1200
> Grand Total          900    700    1000       2600
>
> I want to continue to use the Sum function for calculating the data in 
> the
> table, but instead of summing rows and columns, I want to average the 
> rows
> and columns, so it would look like this:
>
>                         2003   2004   2005   Grand Avg
> Jan                     500    400     500        466.67
> Feb                     400    300     500        400
> Grand Avg           450    350     500        433.33
>
> That way, the actual rows and columns don't change, but the summary 
> values
> change.
>
> I hope that makes more sense.
>
> Thanks again.
>
> Mike
>
> "Peo Sjoblom" wrote:
>
>> When you drag the number header into the data field it defaults to 
>> sum,
>> double click it and select average instead or if you already have sum 
>> in
>> your pivot, right click anywhere in the number column (Total column) 
>> and
>> select field settings, there you can also change to average
>>
>>
>> -- 
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "mikelee101" <mikelee101@discussions.microsoft.com> wrote in message
>> news:8BB0E89E-9BAC-44B7-9E82-EA1DDFCAFDFB@microsoft.com...
>> > Hello,
>> > I'm pretty much a Pivot Table idiot, so I might be overlooking 
>> > something
>> > horribly obvious, but I'm trying to have a "Grand Average" column 
>> > and row
>> in
>> > a Pivot Table instead of a "Grand Total."  I see the Grand Total
>> checkboxes
>> > in the Options dialog box, but I've yet to stumble upon a way to 
>> > specify a
>> > function other than Sum.
>> >
>> > Is this possible?  If so, can someone let me know how?
>> >
>> > Excel2000.
>> >
>> > If I didn't explain that clearly enough, let me know and I can give 
>> > an
>> > example.  Thanks for the help.
>> >
>> > Mike
>>
>>
>> 


0
roger5293 (1125)
1/13/2006 9:19:01 PM
Reply:

Similar Artilces:

Table to feed form instead of lenghty code?
Hello, I a database that is kind of like an Auction type deal. If a user enters their user ID and click the button, it will automatically display their chosen private ID (or Auction ID). What I want to know is, I have 80 people in my department and the code (only way I know how to do it) will become very long. Can I set up a table with the User ID and Private ID and when the button is clicked, have it read from the table and display the Private ID that way? Below is how I am going to do it if no alternative. Private Sub cmdStatusUpdate_Click() Me.NewBid.Locked = False ...

"grouping" a table to prevent change from users
Hi, I would like to group an entire table with a wdContentControlGroup type of Content Control to prevent editing from users except in some wdContentControlRichText type of Content Control. I'm having problem in passing the right Range of the table. If I use: With ActiveDocument Set r = .Tables(1).Range Set objcc = .ContentControls.Add(wdContentControlGroup, r) End With an error "5224" "Wrong selection" arise. If I use: With ActiveDocument .Tables(1).Select Set objcc = .ContentControls.Add(wdCo...

pivot from more than 65K lines: how?
What I am trying to do: have several excel files with data and want to consolidate in a single file What I did: tried to paste all the data in a single file and create a pivot from there... the problem is that I can not copy after reaching 65K lines.... Trying to create a pivot from *multiple* excel does not give me the possibility to put in the layout the fields I want (as if I was using a single sheet)... as it consolidates all the fields in the different tables... Any urgent help please?.. MANY TIA! luis Do you have Access? If so then import all your excel sheets into a singl...

Intelligent Pivot Table with Large Data Sets
Pivot Tables are great with small datasets. Does anyone have suggestions of products that can datasource from a ODBC source and manage large datasets (1M+ rows), visualizing the dat in a crosstab/pivottable format ? Unfortunately the underlying logic in the PivotTable/MSquery link isn' too intelligent in that is always wants to read the whole darn databas table before even presenting a field list. I assume this is due to th abstraction of the data created by sticking MS/Query inbetween th Excel pivot table service and the actual datasource? Seems pretty simply from a conceptual POV: 1. ...

2 pivot tables on one tab
Is it possible to put 2 Pivot tables on the same tab in my workbook? Or does anyone have any ideas on what would be the best way to chart Capital Pircahses? I have 5 coums and 4 rows. The rows are per division, and the columns are for the disposal amount, gain or loss, book value, depreciation & purchase price. I already have one pivot table in my report. Would this be too repeatative? Thanks You can put multiple pivot tables onto a worksheet. If the second one is based on the same data, select and copy the first, select a cell some distance away, and paste it. Each pivot table...

Need help with SQL tables
After running a security reconcile sa was no longer a user in GP. I received the answer below about re-adding it, but I don't know how to add a userid in to a table. I can't find anything on knowledgebase about this. We don't have the SQL Enterprise Manager but use the SQL 2005 Management Studio Express. Can anyone help me? I just need to know the script that will add it to these tables. Thanks. Tracey D "Therefore it will be just a matter of adding 'sa' USERID into the following tables in DYNAMICS database as a minimum. Once added, use ano...

Average #3
Excell 2003 I'm trying to average times, in minutes, in a single column. The minutes are the result of a formula to find elapsed times (14:10-14:08 etc). There are 1414 cells in the column with entries in about 80% of the cells. In one of the books I have it says Excell will only average 20 numbers. If this is true, how do I average the column? On a relaterd topic, wehn I try to do MAX for the columns I get a number that I know isn't the MAX for that column. I've got the columns formated for times. What am I doin wrong? Thanks, The 'Bonker -- Its better to remain ...

Table object
There is a little table object that shows up on the top left of a table that you can use to move the table to the left or right. But I can't seem to figure out how to get that table object to appear. You have to click around select the whole table/ deselect the table and then at some point it appears. How do you get it to appear so you can move the table? I am getting tired of trying to randomly get it to appear. Thanks, Tom Which version of Word? We support about 20... On 7/04/10 2:14 PM, in article O4J9ajg1KHA.4832@TK2MSFTNGP04.phx.gbl, "tshad"...

Pivot Table Freezes
I have many pivot tables off a large database and they periodically freeze when I refresh the data. Only solution is to crash out. Any ideas, comments, solutions out there. The machine has been checked out OK. I work on a network - checked out OK. Maybe this is the problem: XL2000: PivotTable Updated Slowly with OLAP Data Source http://support.microsoft.com/default.aspx?scid=237469 Jimbo wrote: > I have many pivot tables off a large database and they periodically freeze > when I refresh the data. Only solution is to crash out. Any ideas, comments, > solutions out there....

Copying fields from combo box to a table
Hi, I am creating a database for our Machine Maintence Report (access 2003). I created a combo box from table1 and I want to have the selected fields (i.e. Machine, Technician, Engineer etc...) in table1 to be copied in table2. This table2 has the same fields Machine, Technician, Engineer plus the other field that will be updated when the maintenance is done such as remarks, issue and data readings. Looking forward for your help. If you really have a need to store this information in multiple tables, you can use code in the After Update event of the combo box to push the valu...

Find cell numbers in a table so I can multiply
Hello, I have a word document and in the documents header there is a table. This table a has diferent number of cells in each row like: Row one: 2 cells Row two: 4 cells Row three: 2 cells Row four: 10 cells Row five: 2 cells What I wanted to do is multipy 3 cells together thats in row four and show the total in the same row. I have a number in the 3rd cell that needs * by the number in the fith cell that needs * by the number in the seventh cell and totaled in the 9th cell. I know I have to select the ninth cell and select table/formula then what? -- Thanks, Chad I ...

Pivot Table Data Ranges
I just upgraded to excel2000, and am finding out that keyboard shortcuts used in 97 do not exist in 2000. Does anyone know of a KEYBOARD shortcut to modify, expand and or create data ranges in the dialog box for entering the range for the pivot. I have many data 'lists' which are added to each month, and when I want to update the corresponding pivot table, I used to simply hit "shift" + "end" + "down arrow" to automatically go to the last populated cell in the list. In 2000, I either have to enter in the cell addy's manually or use the mouse...

SQL to get the lowest unique value from table
Hi I'm looking for some SQL to get the lowest unique value in a table: eg, <table> ID BID 1 1 2 1 3 1 4 2 5 2 6 2 7 3 8 4 3 is the lowest unique value. I would need to return the ID. Any suggestions? thanks Lee "Lee" <lee@digital-interactive.com> wrote in message news:a981d263-7f22-4b14-8493-014559da8590@z41g2000yqz.googlegroups.com... > Hi > I'm looking for some SQL to get the lowest unique value in a table: > eg, > > <table> > > ID BID > 1 1 > 2 1 > 3 1 > 4 2 > 5 2 > 6 2 ...

i need to make a pub quiz league fixture table
how do i make a fixture table in excel Ummm? What is a fixture table??? "mike" wrote: > how do i make a fixture table in excel ...

Pivot Tables show zero balance
Hi - I have a pivot table.... I want to show customers that have an outstanding balance. when i sort by month, i see everything, totals of who paid and who ha not. I want to sort to see only who is outstanding. How do I do this? CURRENT SETUP: Sort by page = date of invoice (by month) Rows = client name, then the tota -- Message posted from http://www.ExcelForum.com You could add a Status column to your data source, and calculate the account status. For example, with customer names in column C and amounts in column H: =IF(SUMIF($C$2:$C$39,C2,$H$2:$H$39)>0,"Outstanding&...

Table calculations. Season from Date in Hunting Harvest Database.
I am trying to set up a hunting harvest database. I would like to add a column to the original data table that automatically calculates the hunting season, which is based on a specific harvest timeframe. Each harvest record entry has a specific date. For example, when a record of a deer harvested on 12-3-2007 is entered; I want the database to calculate that the date occurred during the fall 2007 season. Thus, I want 2007 to be automatically entered for this record in a separate column. However, our seasons last into the new year, so if for example a record is entered of a ...

Web table to Excel...
Could someone help me exporting the tables from the following web-page into excel ? http://www.ekdd.gr/ESDD/IH/REBBBD-esdd-epityxontwn.htm (use unicode encoding) is this impossible ? ...

MFC String-table in C# ?
Hi, maybe a bit OT for this newsgroup, but I know that people here use C#, too. So, does anyone know how the MFC string-table technique map to C#/WinForm? I initially thought about using an integer->string map in C#, but is there some built-in mechanism to manage that? Thanks, Giovanni "Giovanni Dicanio" <giovanniDOTdicanio@REMOVEMEgmail.com> ha scritto nel messaggio news:ugqw1g7LJHA.1736@TK2MSFTNGP03.phx.gbl... > So, does anyone know how the MFC string-table technique map to C#/WinForm? ....after some web search, it seems that C# has a ResourceManager class ...

Copying and Pasting Excel Tables into Word
We do our financials in Word, and copy and paste special as enhanced metafiles the tables that we do in Excel. This works perfectly in Word 2003. In 2007 however, when we past the table and print it, the table looks different (letter spacing funny, not smooth). I have tried printing to five different printers and it still happens. I'd suggest a slightly different approach. If you insist on using enhanced metafiles, try the following: 1. Select the range that you want to copy. 2. Instead of Copy, select the dropdown under Paste>As Picture>Copy as Picture. 3...

Adding records to table from a form, with pre-populated values
I'm trying to construct a form that will let me translate pre-defined "base" sentences to several languages. I have the following tables: BaseTexts: ID, Text Languages: Language Translations: BaseTextID, Language, Translation but the Translations table only has records if a translation actually exists. So I have a query that gets me all (BaseTextID, Text, Language) triplets for which a translation is needed (Text <-> BaseTextID is 1-1 mapping, but users need to see the text itself in order to translate), and I want to present a multiple-records form with 4...

A save operation on table "PM_Transaction_Work" casused a sharing
Hi, When my user post a PM batch using VPN, the connection is broken and the batch posting is interrupted. The status is "Table Updates Interrupted". When I try to recover the batch through Batch Rocovery, it did not allow to. saying that" Batch NOV INVOICES" failed to complete posting. Use Batch Recovery window to complete the posting process. " (I am at the batch recovery window already!!!!) When I click "more" then I see the following: "A save operation on table 'PM_Transaction_WORK' caused a sharing error." Further invest...

Calculating averages
I have 5 cells that I am trying to average, but I only want to average those cells that contain data. Using the if statement I was able to get two cells to work, but when I tried a nested if statement I was unable to get it to work if there was only 1, 2, or 3 cells with data in them. What do I need to do to resolve this? Thanks in advance. =SUM(A1:A5)/COUNT(A1:A5) Vaya con Dios, Chuck, CABGx3 "Carl Johnson" <bjohnson@woh.rr.com> wrote in message news:hwM4b.25285$l41.7213466@twister.neo.rr.com... > I have 5 cells that I am trying to average, but I only want to average...

Sizing Data Tables on a chart
When I have a data table attached to a chart and size the chart, the data table does not size proportionately ...

Problem inserting calculated pivot fields into Pivot Table (2007)
Working with Excel 2007 and a Pivot Table. Created a Calculated field. The field shows in the Field List, but Excel will not let me drag the Calculated field into the Pivot Table areas (other than into the Values area). What am I doing wrong? Hi A calculated item or field, can ONLY be allocated to the data area. It's position within the data area can be modified, but it cannot be moved to any other area -- Regards Roger Govier wamiller36 wrote: > Working with Excel 2007 and a Pivot Table. Created a Calculated field. The > field shows in the Field List, b...

Inserting New rows into IV00108 Table
I am trying to insert new rows of data into the Iv0018 table I have another table in SQL with tthe data but when i use DTS to append this data it will not let me because of a null value being inserted in the DEX_row_Id column. Is there a SQL script that will allow me to apend this data to this table since the part numbers already exisits this is another pricelevel i am adding. This is SQL 2000 and GP 9.0. Thank you Ken DEX_ROW_ID is an identity column so it will auto populate. In DTS do not map a value for DEx_Row_ID (ignore it) "kfoster@pfeinc.com" <kfosterpfeinccom@disc...