Average in Pivot Tables

Hi,

I have created a table with the following data
student id, exam name, score.

I want to create the following pivot table

            | Exam name 1 | Exam name 2
---------------------------------------
student id1 | Score       | Score
student id2 | Score       | Score
student id3 | Score       | Score
---------------------------------------
             Average        Average

Excel will only let me give the Grand total.
Is it possible to get the average.
The sum of the score is meaningless.

With regards

Constantijn Enders

0
8/2/2005 1:07:24 PM
excel 39879 articles. 2 followers. Follow

5 Replies
407 Views

Similar Articles

[PageSpeed] 13

If you go to Field settings for your data fields and change Sum to Average 
you will get the overall average at the foot of your table.
Clearly with a sample of 1, for any individual student the Sum and Average 
will be the same for each exam result, but the Grand Total will be average 
rather than a Total

-- 
Regards

Roger Govier


"enders" <enders2010.5357414@bloglines.com> wrote in message 
news:1122988044.649674.136070@f14g2000cwb.googlegroups.com...
> Hi,
>
> I have created a table with the following data
> student id, exam name, score.
>
> I want to create the following pivot table
>
>            | Exam name 1 | Exam name 2
> ---------------------------------------
> student id1 | Score       | Score
> student id2 | Score       | Score
> student id3 | Score       | Score
> ---------------------------------------
>             Average        Average
>
> Excel will only let me give the Grand total.
> Is it possible to get the average.
> The sum of the score is meaningless.
>
> With regards
>
> Constantijn Enders
> 


0
roger5293 (1125)
8/2/2005 1:18:29 PM
You could move Exam Name to the first field in the row area.
Then, double-click on its field button, and choose Average for the 
Subtotals.

enders wrote:
> Hi,
> 
> I have created a table with the following data
> student id, exam name, score.
> 
> I want to create the following pivot table
> 
>             | Exam name 1 | Exam name 2
> ---------------------------------------
> student id1 | Score       | Score
> student id2 | Score       | Score
> student id3 | Score       | Score
> ---------------------------------------
>              Average        Average
> 
> Excel will only let me give the Grand total.
> Is it possible to get the average.
> The sum of the score is meaningless.
> 
> With regards
> 
> Constantijn Enders
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
8/2/2005 1:20:10 PM
Hi,

I've been having the same problem, but using the Pivot table view  of a form 
in Access. I was wondering whose answer you used and whether it solved your 
problem. I can't seem to find a place under the field properties of the grand 
total to change Sum to Average.

Thanks

"enders" wrote:

> Hi,
> 
> I have created a table with the following data
> student id, exam name, score.
> 
> I want to create the following pivot table
> 
>             | Exam name 1 | Exam name 2
> ---------------------------------------
> student id1 | Score       | Score
> student id2 | Score       | Score
> student id3 | Score       | Score
> ---------------------------------------
>              Average        Average
> 
> Excel will only let me give the Grand total.
> Is it possible to get the average.
> The sum of the score is meaningless.
> 
> With regards
> 
> Constantijn Enders
> 
> 
0
cerwee (1)
8/3/2005 1:39:07 PM
What about if the source is an OLAP cube?  We are having the same issue...

"cerwee" <cerwee@discussions.microsoft.com> wrote in message 
news:3DA45298-49EA-4D86-BF94-A22FB65FC926@microsoft.com...
> Hi,
>
> I've been having the same problem, but using the Pivot table view  of a 
> form
> in Access. I was wondering whose answer you used and whether it solved 
> your
> problem. I can't seem to find a place under the field properties of the 
> grand
> total to change Sum to Average.
>
> Thanks
>
> "enders" wrote:
>
>> Hi,
>>
>> I have created a table with the following data
>> student id, exam name, score.
>>
>> I want to create the following pivot table
>>
>>             | Exam name 1 | Exam name 2
>> ---------------------------------------
>> student id1 | Score       | Score
>> student id2 | Score       | Score
>> student id3 | Score       | Score
>> ---------------------------------------
>>              Average        Average
>>
>> Excel will only let me give the Grand total.
>> Is it possible to get the average.
>> The sum of the score is meaningless.
>>
>> With regards
>>
>> Constantijn Enders
>>
>> 


0
jesscobbe (1)
8/3/2005 8:56:14 PM
Access pivot tables are nifty, but Microsoft didn't make it easy to get 
started with them.

First, make sure you've clicked on the "Show details" icon.  (If "Hide 
details" is clicked, the Sigma icon and the Pivot tables/Autocalc option will 
be greyed out.  Frustrated the hell out of me until I discovered this.)

Then click the column you want to average.  Click the Sigma icon, or choose 
Pivot table/Autocalc, and then choose Average.  In Show details view, this 
will add a new "Average of {fieldname}" ROW.  Click on "Hide details", and 
Access will convert it to a column.  

Then right click on the column, choose Properties, and you can change the 
column heading and the format.  You can then drag the Sum column off the 
screen if it's meaningless.

I've got an article scheduled for the September issue of Access Advisor 
which shows how to use Access pivot tables to provide flexible usere-defined 
reporting.  They're incredibly useful once you get your head wrapped around 
how they work.  (And I'm not selling anything here, guys!)

Kevin

"enders" wrote:

> Hi,
> 
> I have created a table with the following data
> student id, exam name, score.
> 
> I want to create the following pivot table
> 
>             | Exam name 1 | Exam name 2
> ---------------------------------------
> student id1 | Score       | Score
> student id2 | Score       | Score
> student id3 | Score       | Score
> ---------------------------------------
>              Average        Average
> 
> Excel will only let me give the Grand total.
> Is it possible to get the average.
> The sum of the score is meaningless.
> 
> With regards
> 
> Constantijn Enders
> 
> 
0
kdw (2)
8/15/2005 2:32:05 PM
Reply:

Similar Artilces:

FP: Couldnt close table
We have been receiving this error on two separate PCs after a recent upgrade to 7.5. Our version is 7.50g43 (service pack 5). At first we thought it was isolated to one PC now a second PC is having the same error. On the first PC, I ran new network cable, installed a different network card with no help. This PC is Win98, 64MB RAM, 600Mhz. The second PC has 128MB RAM also Win98. I don't believe switching to XP is an option right now. This message has appeared in the payables module on both machines. But it has also appeared in receivables as well. Not in GL or Payroll. I...

Pivot tables to count unique records only
Hi All, I have some data which is sometimes repeated. I want this data to feed directly into a pivot table and only count the unique records as theres records in there which appear about 8 times. When you do count on a reference number it counts it each time, show its showing about 131 records when its truly 22 ! Can anyone help please? -- Adam ----------- Windows 98 + Office Pro 97 A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable. For example, if you want to count the customers in column A, use...

Store similar types of records all in one table or separate tables?
Suppose you want to have four different types of records. Each of these records have numerous fields in common, and a few fields that are unique to each type of record. Most of the fields are related to other tables, but a few are simply text fields or Booleans. Which is better?: Keeping track of all 3 types of records in a single table. Or Creating separate tables for each type of record. Is one solution clearly better or is it just a matter of opinion? Thanks in advance, Tom On Fri, 6 Nov 2009 09:13:14 -0800 (PST), tryit <tryit.ca@gmail.com> w...

How to convert existing Excel List into a table in Access?
Is it possible to convert existing Excel List into a table in Access database in 2003 Excel I know it was possible in 2002 version through AccessLinks ? You can import the Excel list into Access, and use it there as a table. In Access, choose File>Get External Data>Import In the Files of Type dropdown, at the bottom of the dialog box, choose Microsoft Excel Locate and select your Excel file, and click Import Step through the Import Wizard, to complete the import. anna wrote: > Is it possible to convert existing Excel List into a table in Access database > in 2003 Excel I know...

Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote: >I have a column of data as illustrated below than contains numbers, blank >cells, dashes and < entries. Is it possible to average only ...

How can deleted data reappear in a refreshed pivot table in Excel
I am using Excel 2003. I have a worksheet full of data and built a pivot table on another sheet (within the existing datasheet). Nothing too complicated. I updated my data by pasting new data over the top of the old data and updated my pivot table. The problem is that in the pivot table one of the fields still allows me to select on data that no longer exists in the data. More specifically, I have a column in the base data entitled "Vendors" and have approximately 20 different vendors that appear throughout the data. When I update my pivot table, in the vendor select...

Average of Averages
Another seemingly simple process which I can't figure. :) I have a list of averages, and I want to get the average of them. As such, A 1 25% 2 42% 3 100% 4 50% I need a formula to average something like the above. Thanks, Alan =AVERAGE(A1:A4) is one possible solution. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=513937 Hi Alan It really depen...

averaging less than values
How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD Assuming source data as posted in A2:A7 Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: > How do I average ...

Relinking Tables with VBA code
Hello together, I got this wonderful code from the internet to relink all tables from a backend database in the same folder. Unfortunatelly its not working: The code gets me the right folder (see MsgBox (strPath) but after that it links only the first table but not the others. Can anybody tell me what's wrong in the code? Thank you very much!!! Andreas Function link() On Error GoTo MyError Dim db As DAO.Database Dim strPath As String Dim i As Integer Set db = CurrentDb() strPath = Left(db.Name, Len(db.Name) - Len(Dir(db.Name))) & "social_performance...

pivot table error (25000 rows!!)
I am trying to run a pivot table on a file containing 25000 rows. I have 5 duplicate (identical structure) files. The pivot works on each but one. On this file I get an error saying it has too many columns or rows in the pivot table. Drag at least one column or row off the table... Does anybody know what the reason for this message is, are their limits to rows or columns? The identical table works on the other files, therefore this message is so strange. Who has an idea? -- Craig_Richards ------------------------------------------------------------------------ Craig_Richards's Profil...

Pivot table move up command
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I need to move up values in a pivot table to custom order them. When I right-click I see the &quot;Move&quot; menu, but the options to move up, move down, move to beginning and move to end are greyed out. Did you find an answer? Katy MelKC wrote: Pivot table move up command 04-Feb-10 Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I need to move up values in a pivot table to custom order them. When I right-click I see the &quot;Move&quot; menu, but the options to move up, move d...

deactivate the get pivot formula when I link a cell to a pivot
Currently I using excel 2003, but every time when I link the cell to a pivot table it will convert to a "Get pivot" formula. Is there any way I could deactivate it? See Debra Dalgleish's website for instructions: http://www.contextures.com/xlPivot06.html Does that help? *********** Regards, Ron XL2002, WinXP-Pro "hwtradezheng" wrote: > Currently I using excel 2003, but every time when I link the cell to a pivot > table it will convert to a "Get pivot" formula. Is there any way I could > deactivate it? ...

Averages
Hello again, I have an interesting situation. I basically inherited an older database that is used to enter dates, usage and charges for accounts. The situation is that we are attempting to do averages filtered by account, and averaged against the number of days that data has been entered. So, for example, we had 7 months of data entered, our query should total the number of days in the seven months (x), then average that with the total usage number (y). This data currently resides in the same table labeled "data" in the form of from and to dates (6/1/1999 to 7/1/1999) and usage (32...

I need to convert a word table to excel
When I try doing this by copying the table it appears in excelwith one or two empty lines in between each of my lines that I needed converting. Why is this? usually means there are superfluous paragraph marks in the Word table. "Aaron" <Aaron@discussions.microsoft.com> wrote in message news:012E3413-1F64-4018-BC14-A2D6AB68B668@microsoft.com... > When I try doing this by copying the table it appears in excelwith one or > two > empty lines in between each of my lines that I needed converting. Why is > this? ...

line column Pivot Charts
Access 2003 does not support combined Line/Column Charts, but Excel does. Has the capability been added to Access 2007? Access does support a series displayed as a bars and another series as line. -- Duane Hookom MS Access MVP Help me support UCP http://www.access.hookom.net/UCP/Default.htm "John" <John@discussions.microsoft.com> wrote in message news:A0DD25F6-D99A-44C3-812B-416776BED8AF@microsoft.com... > Access 2003 does not support combined Line/Column Charts, but Excel does. > Has > the capability been added to Access 2007? ...

Unwanted arrows when "Format As Table"
When I select my Excel 2007 worksheet and format it as a preset table style, the first row always contains dropdown arrows that are totally unneeded for my styling. How can I get rid of those arrows? Thanks. >When I select my Excel 2007 worksheet and format it as a preset table >style, the first row always contains dropdown arrows that are totally >unneeded for my styling. How can I get rid of those arrows? Thanks. Here is a before/after pic to show what a distraction the header arrows are. http://img178.imageshack.us/img178/8017/excelba2.jpg Now I understand what the issue is....

Pivot Charts #4
Is there a way to change the pivot chart without changing the table in the process? ...

Strange Icon
I have noticed a strange picture/icon (Can't paste it here) A small icon with a Blue J and a yellow background. I can identify it or it's purpose. Anyone know what it is? What it does? Thank and Seasons Greeting! What version of Excel are you using? Where/when does the icon appear? HW wrote: > I have noticed a strange picture/icon (Can't paste it here) A small icon > with a Blue J and a yellow background. I can identify it or it's purpose. > > Anyone know what it is? What it does? > > Thank and Seasons Greeting! -- Debra...

Pivot Table Update?
I have an existing pivot table that works OK - but I want it to recognise and automatically update when a new line of data is input - how can I do this? -- ajames ------------------------------------------------------------------------ ajames's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31502 View this thread: http://www.excelforum.com/showthread.php?threadid=514328 Use a dynamic range name--one that grows and contracts with your data. See Debra Dalgeish's site for some nice tips: http://contextures.com/xlNames01.html#Dynamic Then all you'll hav...

enterprise resources in which table of which database
when i add resource from active directory to resource center .and i want to know in which database and in which table it will saved first. -- It should be in the Published and the Reporting database. In the tables labeled "..Resource" Jonathan "Basim" <Basim@discussions.microsoft.com> wrote in message news:B443F9A1-E8FD-4DF5-A347-A13A626EDE67@microsoft.com... > when i add resource from active directory to resource center .and i want > to > know in which database and in which table it will saved first. > > > -- > ...

Opening a window(form) with VBA / storing data in a separate table
Hi all, First Question---- I have a third party application (Additional Inventory Fields in the ActNow series from Hawk Mountain) that currently is accessed from the Extra menu while in item maintenance. I have added a button to the item maintenance form and I am trying to figure our what VBA code I need to put in so that when the user clicks on the button that it launches the form that is currently accessed via the menu. This is not currently a VBA form but the window that comes with the application. I have tried to put an "OnUserChanged" event on the button with a FormName.Open...

Auto filter and Pivot Table
Is it possible to auto filter data, and use it as input to a Pivot table? When I run a test where I first auto filter, I initially had 20000 rows, and the auto filter reduced it to 12000 rows. But when I go to crearte a pivot table on the filtered data, I am back to a total record count of 20000. Am I combining apples and oranges? If so, is there a way to filter data, before it gets inputed to a Pivot table? -- RandyLewisKemp ------------------------------------------------------------------------ RandyLewisKemp's Profile: http://www.excelforum.com/member.php?action=getinfo&use...

Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts weekends, or only business days? The average lead time is calcualted as follows: Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. If you're using Purchase Order Processing, this field will be updated each time a shipment or shipment/invoice is posted in Purchase Order Processing. The average lead time is calculated using the following formula: [(Number of Receipts) * (Average Lead Time) + (Received Date - Ordered Date)] / (...

Average Function
Hi group, I have a formula in a cell that takes the average Sales of the 12 rows (for Jan to Dec) like so: =Average(A1:A12)The problem is that for example, since therea are no sales figures from August to Dec, the average is underestimated because it averages it over the entire range of cells (though they are zero from Augus to Dec). How can I make this Average function to be smarter and only average those months that the sales is not zero (August to Dec is Not BLANK but its zero) Hi Average is a very simple thing: SUM/COUNT. So =SUM(A1:A12)/12 should do for all years with 12 mont...

chart options show data table
Currently using 2 workbooks to produce 1 chart - the source data in workbook 1 and the chart in workbook 2. We also are using the "show data table" option to most easily view all values in the chart. We would like this data to keep it's currency format to best reflect the values in the chart; however, the data table loses this formatting when we close workbook 1. Is there any way to hang onto this formatting after the workbook is closed? Data tables in charts are not particularly flexible or capable. You combine the workbooks. Alternatively, put a table in the chart's...