Pivot Table Customize functions in the Data Field

Hi all,
I want to find out if it is possible to create custom formulas in the data 
field of a pivot table.

I need to to show:
Fleets of vehicles.
How many of a particular fleet a particular company has on hand.
A percentage of how much of that companies fleet is not operational.
If no maintinane is completed between Now() and the end of the reporting 
period, What the % of maintenance failure we will have for a fleet.  

I think the best way to accomplish this is a pivot table but the built in 
functions are not going to handle the calculations I need.

-- 
Thanks,

Patrick
0
1/4/2005 5:45:08 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
299 Views

Similar Articles

[PageSpeed] 39

PS,
  Don't know what numbers you're working with but in a pivot table, you can 
drag down the same number several times and have it sum, count, average, maxm 
min, product, count numbers, etc. in the 'PivotTable Field' box (to get here, 
drag a number into the data section and double-left-click on that field).
  Also, did you know that in the  'PivotTable Field' box there is a button 
called 'Options'.  This opens a section on the bottom that says 'Show data 
as:'.  If you drop down the listbox (default is 'Normal'), you will see 
selections for 'Difference From', '% of', '% Difference From', 'Running Total 
in', '% of row', % of column', etc..
Hope this helps,
Sincerely,
Gary Brown


"PSKelligan" wrote:

> Hi all,
> I want to find out if it is possible to create custom formulas in the data 
> field of a pivot table.
> 
> I need to to show:
> Fleets of vehicles.
> How many of a particular fleet a particular company has on hand.
> A percentage of how much of that companies fleet is not operational.
> If no maintinane is completed between Now() and the end of the reporting 
> period, What the % of maintenance failure we will have for a fleet.  
> 
> I think the best way to accomplish this is a pivot table but the built in 
> functions are not going to handle the calculations I need.
> 
> -- 
> Thanks,
> 
> Patrick
0
GaryBrown (90)
1/4/2005 6:43:04 PM
Another issue is...
When you first create a Pivot Table with more than one field of data in it, 
the data lists vertically down the table.  My preference is to see this info 
listed horizontally across the table.  To do this, I drag the field that says 
'Data' over to the 'Grand Total' column and release it.
HTH,
Gary Brown

"Gary Brown" wrote:

> PS,
>   Don't know what numbers you're working with but in a pivot table, you can 
> drag down the same number several times and have it sum, count, average, maxm 
> min, product, count numbers, etc. in the 'PivotTable Field' box (to get here, 
> drag a number into the data section and double-left-click on that field).
>   Also, did you know that in the  'PivotTable Field' box there is a button 
> called 'Options'.  This opens a section on the bottom that says 'Show data 
> as:'.  If you drop down the listbox (default is 'Normal'), you will see 
> selections for 'Difference From', '% of', '% Difference From', 'Running Total 
> in', '% of row', % of column', etc..
> Hope this helps,
> Sincerely,
> Gary Brown
> 
> 
> "PSKelligan" wrote:
> 
> > Hi all,
> > I want to find out if it is possible to create custom formulas in the data 
> > field of a pivot table.
> > 
> > I need to to show:
> > Fleets of vehicles.
> > How many of a particular fleet a particular company has on hand.
> > A percentage of how much of that companies fleet is not operational.
> > If no maintinane is completed between Now() and the end of the reporting 
> > period, What the % of maintenance failure we will have for a fleet.  
> > 
> > I think the best way to accomplish this is a pivot table but the built in 
> > functions are not going to handle the calculations I need.
> > 
> > -- 
> > Thanks,
> > 
> > Patrick
0
GaryBrown (90)
1/4/2005 6:51:01 PM
Reply:

Similar Artilces:

TempVars unusable in field default value
Hello, I'm trying to use a temporary variable to keep track of which CSR is inputting data. I have a macro which prompts user for ID code, which is stored in the temp variable TempUser. On a form control default value property, I can use the expression [TempVars]![TempUser], which will populate that user's ID code into the control. However, I cannot use that same expression in the tables field default value property. If I try, when I save the changes to the table, I get the error message "Could not find the field 'TempVars]![TempUser'. " Any ideas why I ca...

send the same e-mail with one or two fields changed.......
I would like to send the same e-mail to many differnet people with one or two fields changed (for example the name of recipient and the date).How canthis be done?? I would also like to be able to save the e-mail and use it again and again. can anyone help cheers john If you have Word installed and it's the same version as Outlook (both 2003, for example), you can do a mail merge between the two. This would allow you to set up the text the way you want it to, and you can save the document for future use. Look at the following page for further information: http://www.slipstick.com/con...

*major* functional issue with MOSS/WSS?
We are looking to move a big site over to using MOSS in a big as an alternative to shared network drives (ugh). One of the biggest issues is sending docs as attachments via email for others to edit. We'd like to get everyone to use the 'send a link' functionality from thew Sharepoint DL BUT when the user receives the email and clinks on the embedded link the doc opens read only. They then cant edit and save their changes. Seems a big an counter intuitive functional flaw. Are we missing something here? Is there any easy (for the end user) way to send those links to a doc ...

Proper Table(s) Layout
I have created a couple of Dbs which the table structure seemed to simply fall into place, it was just logical in my head. That said, I am working on a new db and for some reason I am doubting myself and wanted a second opinion. The db is basically a contract db to input all the info, and there is a lot of info, for each contract. Where I am 'lost' is the fact that the contracts are broken into categories: clients, components,engineering, warantee... For all of the components (with the exception of clients) there are a number of fields but only 1 entry per contract....

Table link documentation
I am having trouble trying to locate A/P check data that has project related costs. I found the check data but it is does not indicate the projects, I found the project data but can not determine thye logical link between the two tables, I may be using the wrong tables the tables I am using are PM80500 and PA31102. Is there any documentation of how all the tables in the system are logically link. I am trying to write reports in MS Access, but there are 1500+ tables in GP (version 10) -- Dave F In an effort to find the correct table you can do a number of things (believe me I do)....

"Customize Outlook Today" does not respond
The "Customize Outlook Today" button no longer functions. I use Outlook 2000 on two separate computers. the Customize feature works on neither one. They both formerly functioned perfectly. I have noted a few other postings with the same issue, but there have been no responses. Is it possible that one of the updates to Outlook 2000 has rendered this feature inoperable? Since I keep my programs up to date, that is the only guess I have. I have the exact same problem on more than one pc. I have been searching for an answer all day. I just decided to start using the opti...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Field Service Calls and Invoices
We use the Service Call modules for repairs in our Stores and have a situation that I'm trying to find a workaround. We have situations where our technicians will pick-up items without prior notification to our dispatchers. All of a sudden we get an invoice and currently we have to track down the charge and then we are forcing a PO into the system from the Service Call in order to properly receive and enter the invoice. Does anyone have other ways to do this and track the information/cost on the Service Call without needing to create a PO after the fact. This is not an applicat...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

Problem with Update function of CRecordSet class
Hi All When I use from Update function of CRecordSet class, it apply to table after a few time(for example 1 second) when I need to see it at time. Thanks ...

Duplicate record in RM tables
We experienced an issue in Apply Sales Document that may have caused a duplicate record somewhere. We found this when running Paid Sales Transaction Removal and received this message: Violation of PRIMARY KEY contraint PKRM3101. Cannot insert duplicate key in object RM30101. I ran the RM duplicate tool found in the automated help area of this website and found the following: --- Begin copy here ---- Duplicates between RM Open and RM History Document #: 07-003021-17 Customer #: 079100 RMDTYPAL #: 7 --- End copy --- It looks like the duplicte tool also logs the qu...

140 MB file went to 5.08 MB after editting 1 table
Hello All - I need some ACCESS insight...please... Several years ago, I built an access db to track my business scheduling and accounts payable/receivable. So this database is EXTREMELY IMPORTANT TO ME. The file has grown to 140 MB. Today I made a copy of the file and then edited my calendar table. I removed all columns which had 2006 data (72 totals columns) - the table had about 144 columns originally. I then added 72 columns with 2008 headers. These columns are now blank since I have not added any 2008 data yet. Afterwards, I looked around and everything looks good - my 2007 data is the...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

date function #3
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C73005.0FA093A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a cell A1 with date 5/2/07. If in cell A2 I do month(A1), I get = 5. Is there a way to return May instead? either through a command or = formatting? I can reference cell A2 with the 5 with a if(cell =3D 5, = "May", ) in cell A3, but the date in cell A1 varies from Jan to Dec and = the 12 embedded if's in cell A3 are too long (that is the error I get = when trying to do so), plus i...

Custom reports #3
Hi, I want to pull a report on how many customers I am getting every hour of the day. Does anyone out there know how to do that? I can try writing a query if someone can tell me which table I can find this information in (timestamp, transaction etc). Any help is appreciated. Regards JD This info is given at the end of the Z report. If you want to use it in a query open the Z report recipt file using the Notepad and see the fields that are used... Report.Hour.HourSales etc... Hope this helps... Carlos "JD" wrote: > Hi, > > I want to pull a report on how many cust...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

Pivot Table Defaults
In the pivot table field list, whenever I create a new pivot table and I am inserting fields into the value area, I generally get as default field setting the 'Count' value. Is there a way to format the spreadsheet to make Excel recognize the data as all numbers so it defaults to the "Sum" function as opposed to "text"? Hi The rule that the PT Wizard adopts is, If all the values in the field being added to the data area are Numeric, then it uses Sum. If any of the values are Text or BLANK, then it uses Count. It sounds as though you have defin...

Like a pivot table
Hello every body I'm first time requesting in this group, so I opologize in advance for any mistakes or something annoying I repeat what I have sent before 10 min because I see it unclear when it goes to news group If any one can help me I'm working with data which most of it comes like a table with feilds as columns and records as rows. I want it to be as many rows with each feild an example what is exist name age Joining Date Tele John 20 Jun-90 4321251 Iqbal 30 Jul-95 6583752 George 40 Sep-85 7843125 What I wa...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

Memo Field Not pasting correctly in XL
I tried a search and couldn't find an answer. If there is an answer to this already please point me to it. I'm simply copying the record in Access and trying to paste it into XL. The problem that I'm having is one of the fields does not fully paste into XL. It is a memo field and has 539 characters. I'm only getting 255 when I try to paste the record in XL. I've tried copying just the contents of the cell and that worked fine. It seems as if I'm running into some sort of limitation. Does anyone know anything about this and is there a way to get around it? I checke...