Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

Excel 2000: Formulas within Pivottables?

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

Excel 2000: Formulas within Pivottables?

Old 01-06-04, 02:11 PM
  #1  
DVD Talk Hall of Fame
Thread Starter
 
Join Date: Aug 2002
Location: Triangle, NC, USA
Posts: 8,810
Received 0 Likes on 0 Posts
Excel 2000: Formulas within Pivottables?

I have a PivotTable which is based on a bunch of data on an Excel sheet [~12000 lines.]
The table has Group as a page pivot, Day as a column pivot, and Time period [00:00, 00:15, etc] as a the Row pivot. The data are numbers, as in, This Group had a quantity of 5 on the Thursdays at 17:30 within the dataset.
Totalling it is no problem.
What I'm wanting is an Average.
But I can't use the Average within the Pivottable, because not every date/time combo is represented in the dataset. So if I said 'Give me the average of Group A, Mon, 00:30', it would say 1. But there was only 1 Monday at 00:30 where Group A had activity, and that activity was 1. So 1/1 = 1. The answer I'm looking for, is 1/6 = .166, because there were six Mondays within the dataset, and I want the average per Monday.

Is there way in the pivot table to say use the Total function of the pivot, and then add a formula to it [divide the total for each day/time/group combo by 6 would give me what I want.] I can do it manually, but there are like 60 different permutations.

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.