Pivot - Help on Creating Calculated Item

Can anybody show me how to create a Calculated Item in a pivot table?  I
have a field called CLASS with the following values:  Stock, Sales,
Order and Target.  I would want a field that calculates Stock minus
Order.  As per HELP, we have to ungroup each item and enter the
calculation.  I have done that but have never been successful in
creating one.  Either the system hangs up or I get a message that
Calculated Item should be of the same field, which was how I defined
it.

Am I the only one who is having problem with this?


-- 
bchan
------------------------------------------------------------------------
bchan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8274
View this thread: http://www.excelforum.com/showthread.php?threadid=345830

0
2/16/2005 11:32:13 AM
excel 39879 articles. 2 followers. Follow

2 Replies
167 Views

Similar Articles

[PageSpeed] 40

Hi

i've not done a huge amount of calculated fields but they've always worked 
for me ... my approach - put Class in the rows - drag stock & order to the 
data area.

click on the pivot table icon and choose formulas then calculated field
give it a name (dif)
the next line has
= 0
i delete the 0
and in the field list click on stock, type a - and then click on Order
so i now have
= Stock- Order
click on Add

- is this what you're after.

Cheers
JulieD


"bchan" <bchan.1kjo2d@excelforum-nospam.com> wrote in message 
news:bchan.1kjo2d@excelforum-nospam.com...
>
> Can anybody show me how to create a Calculated Item in a pivot table?  I
> have a field called CLASS with the following values:  Stock, Sales,
> Order and Target.  I would want a field that calculates Stock minus
> Order.  As per HELP, we have to ungroup each item and enter the
> calculation.  I have done that but have never been successful in
> creating one.  Either the system hangs up or I get a message that
> Calculated Item should be of the same field, which was how I defined
> it.
>
> Am I the only one who is having problem with this?
>
>
> -- 
> bchan
> ------------------------------------------------------------------------
> bchan's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=8274
> View this thread: http://www.excelforum.com/showthread.php?threadid=345830
> 


0
JulieD1 (2295)
2/16/2005 3:15:39 PM
To create a calculated item in the Class field:

Select the CLASS button in the pivot table, or select one of
    the items in the CLASS field.
 From the Pivot table toolbar, choose
     PivotTable>Formulas>Calculated Item
Type a name for the item, e.g. Stock2
Press the Tab key, to move to the Formula box	
In the Field list, select CLASS
In the Item list, double-click Stock
Type a minus sign
In the Item list, double-click Stock
Click OK


bchan wrote:
> Can anybody show me how to create a Calculated Item in a pivot table?  I
> have a field called CLASS with the following values:  Stock, Sales,
> Order and Target.  I would want a field that calculates Stock minus
> Order.  As per HELP, we have to ungroup each item and enter the
> calculation.  I have done that but have never been successful in
> creating one.  Either the system hangs up or I get a message that
> Calculated Item should be of the same field, which was how I defined
> it.
> 
> Am I the only one who is having problem with this?
> 
> 


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

0
dsd1 (5911)
2/16/2005 6:59:09 PM
Reply:

Similar Artilces:

MRP Regeneration
Hi, We have a problem where when we do a MRP regen items that are inactive or obsolete are still showing up to buy even though they're inactive and 'Calculate MRP' is checked off under the Item Engineering Data interface. Are there other interfaces we could be missing to ensure these items don't continually show up to buy. Thanks, -- Jim Bourque What is the Order Policy set for the item? And while you are at it, give me the other parameters (min, max, days......etc) from the Item Resource Planning window. -- Richard L. Whaley Author / Consultant / MVP Documentatio...

How do I convert formulas to values? Help appreciated!
Is there a way for me to formulas to values or the result? I have to upload this into another program and it does not like the formulas however will take alpha-numeric cells. Any help would be appreciated. Hi Smitty, Press Control + A to select the entire contents of the sheet and then go to Edit-->Copy. Then go to Edit-->Paste Special and paste Values. Please be sure to make a backup copy of your file so that you do have the formulae somewhere in case you need them again. Either that or do the paste special-->Values into a new workbook. HTH, Katherine "Smitty" <...

Grouping in a Pivot Table
I have created a Pivot Table off of a spreadsheet containing sales related information. I am trying to group the "Due Date" field into months and I keep receiving an error that I cannot group the selection. I have used this feature before with success and I am stumped as to why it is not working this time. Help! Most often it has to do with having blank cells or text mixed in with your dates. Check out this link. Once you have changed all of your items into dates you may have to refresh your pivot table twice before you will be able to group by dates. http://www...

Charge for help
When trying to use Email to obtain help the first block on the contact page says there is no charge for unlimited problem submissions. The second block says there is a $35 charge. I can find no way to obtain free help. If you want to use 'chat' the first block says there is no charge. However chat is never available, even during the times shown in the table later on the page. Customer service at Miscrosoft stinks. The next time I want to upgrade my financial software it will be Quicken. Maybe there service is no better, but it cannot be any worse. Where am I going wrong t...

Problem with Money 2005, please help #2
hi all, i have a little problem in Money 2005. i made a budget but apprenly its in doller and not in nis (Israel Shekel) how can i change that? i want all the info to be in nis, how can i change that? thank you all! Tools > Settings > Program Settings > Currencies > Update currencies.Highlight Israel and click on Set as base currency. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.m...

Reports won't open. Please Help
I have just done a clean reinstall of Windows XP Pro and Office 2003 along with the other applications which had been previously installed. The computer is on a simple workstation network with another computer running Vista X64 and the printer is connected to the Vista computer. Since reinstalling I cannot open any reports when the network computer is the default. Printing is fine from Word and can even print the functions in the modules. If I change the default printer to Microsoft XP5 Document writer then the reports open OK. This behaviour has been brought up previously on the Newsg...

Quote field on Item Vendor Maintenance
A client wanted to have a field that auto-populates his POs w/ current quoted pricing from Vendors. The "Originating Invoice Cost" which is filled by last invoice cost and then auto-populates in the next created PO for that vendor was a consideration, however, if he received a quote today and updated that field, but matched an invoice to a shipment the day following, his entry would be overridden. Would you consider adding a field that could permit a quoted cost on this window, possibly even a setup to select either Quoted cost or Originating Invoice Cost. Thanks for your c...

Help with function
Is it possible to make a function, like for example y=x^2+2 and get it into a diagramm? Thanks in advance Hi Jonathan...... Try typing your function into a TEXT box, and then placing the TEXT box where you wish. You can format it to make it transparent and the outer border go away, if desired. Vaya con Dios, Chuck, CABGx3 "Jonathan" wrote: > Is it possible to make a function, like for example y=x^2+2 and get it into a > diagramm? > > Thanks in advance If you're asking how to graph that function, then you'd want to generate a Data Table (Data...Table...

Remove/Hide Navbar items
Hi all, I would like to have my own product page under opportunity products section. I don't want the users to see the Products side nav bar which comes with the default installation. I looked at the file which was loading the page (crmwebsite/sfa/opps/edit.aspx) and found out that it uses a custom web usercontrol to load the left side bar items in a <div> tag. The <div> tag does not even have a name or id. Is there any way I could remove the div tag ( which does not have a name or id) using JavaScript. Moreover I do not find the div tag inside the FORM element. At this poi...

Autoformat in pivot tables
Does anyone know if it is possible to remove an autoformat from a pivot table once the document is saved? I would like to return the pivot table to its original form before the autoformat. Thanks. Select a cell in the pivot table, and choose Format>AutoFormat Scroll to the bottom of the list of AutoFormats, and click on PivotTable Classic Click OK If you had previously applied one of the Report AutoFormats, e.g. Report4, any column headings would have been automatically moved to the Row area. You'll have to manually move those fields back to the Column area. C Tate wrote: > D...

SUMPRODUCT help with dates
I've got a list of dates dd/mm/yyyy in column A and a list of the numbers in column B which is related to the number of occurrences of something on each date listed. There are some gaps in the dates ie. it is not a continuous calendar list. What I need to do is count the total number of occurrences in April 2010 for example. I've tried using SUMPRODUCT but I'm stuck! I've managed to count the number of times a date in April 2010 is listed but not the sum of number of occurrences in April 2010 from the column B? Hopefully that is semi-clear?!! Try this… =SUM...

Help with a macro #2
Hello Friends, I'm trying to create a macro which gets the value from the currently selected cell. For example: if I select cell E6 and run the macro it copies the value from cell E6 and pastes it to cell P5. And next time if I select another cell (let's say cell E9) and run the macro it copies the value FROM THE CURRENTLY SELECTED CELL E9 and copies it to cell P5. How can I do this task using ExcelXP? Thanks again. Tim Sub Macro1() Sheet1.Range("P5").Value = ActiveCell.Value End Sub "Tim" <tihidj@hotmail.com> wrote in message news:0a5101c3baaa$d14...

Date Calculation #3
I have a spread sheet with dates entered in the range K2:K750 and in O2:O750. There are many dates that have not yet been entered. I am calculating the number of weeks between the two dates with the formula =(O2-K2)/7 and then filled the formula down to row 750. This is done in column P. My problem is that I must calculate the average number of weeks. How do I create a formula to calculate the average of values in the range P2:P750, without including those cells in rows where one or both of the dates in column K or O have not yet been entered. The formula should update as date informatio...

how can i calculate formulas automatically
I have a worksheet with three columns. Col1 is item, Col2 is price and Col3 is quantity. I need a column 4 that calculates col2 * col3 and displays total. I tried formulas, but i need the total to be displayed automatically whenever i navigate to that column after entering col 2 and col 3. I dont want to drag the formula manually to se the results. It should be like pure forms. Any advice? Seige Silver wrote: || I have a worksheet with three columns. Col1 is item, Col2 is price || and Col3 is quantity. I need a column 4 that calculates col2 * col3 || and displays total. I tried formula...

how do i create a new e-mail account?
i have an existing hotmail email account. but i want to be able to use outlook. im using out look 2003. what is the incoming server info and where do i find it. same with outgoing mail server. SMTP thanks katherine "atherinek" <atherinek@discussions.microsoft.com> wrote in message news:FA84299B-D64B-4FA2-B379-622FEBD547AC@microsoft.com... >i have an existing hotmail email account. but i want to be able to use > outlook. im using out look 2003. what is the incoming server info and > where > do i find it. same with outgoing mail server. SMTP > thanks > kath...

formula help needed
I am working in to seperate work books(call them wrk1 and wrk2), I a wanting to reference back to wrk1, say cell J2. If the value is 0 o larger I want to add 1 to it number from the wrk1 to give a runnin total in wrk2. Can someone please give me a little advise on this -- Message posted from http://www.ExcelForum.com If both workbooks are open, the following formula in wrk2 will deliver the value in wrk1 sheet1 cell J2 =[wrk1.xls]Sheet1!$J$2 then you can do anything you want to with it......... this formula will retrieve it, and add one to the number if it's 0 or more........ =IF(...

Remove Favorite Folders item from Outlook 2003
I know there's a technote stating that it can't be removed, but is there any third party 'hack' that can remove it? Thanks, Jeff >I know there's a technote stating that it can't be >removed, but is there any third party 'hack' that can >remove it? I've been searching for such a 'hack' for a while now without any luck. Anybody know why Microsoft chose not let us turn it off? Mark ? Because the vast majority of folks find this option extremely useful and only a few don't want it visible?? --� Milly Staples [MVP - Outlook] Post...

How can I create a Read-only field?
In one of my forms there is a textfield that should be either enabled or disabled, depending on the value of a picklist. With a Jscript in the onChange event I can do this, but how can I make sure the textfield still is disabled/enabled when I re-open the form? It should be some procedure that is called when loading the form, but how do I create such a procedure, and how do I insert it in form.load? I hope somebody can help me with this, and that he/she can explain it in newbie-terms... Thanks in advance, Simon Hi Simon, I'm trying to do the same - here is some code from a system where ...

Calculating auto filtered data
how do I calculate the median of filtered data without counting the hidden columns? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 Take a look at the SUBTOTAL worksheet function. = SUBTOTAL(1, A1:A500) would give you the Average of all non-filtered data in the specified range. (yeah, I know you asked for Median. Average is the best I can do.) HTH, -- George Nicholson Remove 'Junk' from return address. "Patty via OfficeKB.com" <forum@OfficeKB.com> wrote in message news:534776E33C7FB@OfficeKB.com... > how d...

Need help with Excel and database...
Hi, I have a spreadsheet with data that I need to get into a SQL database. What is the best way to do that? Thanks, Dan Might not be the best way, but it's certainly a way: It includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table and add data to it, * select data from a table, * delete a table, * delete a databas...

Need help using pull down menus for a grid
Perhaps someone has a solution to my problem. I'm trying to use a pull down menu for a grid of information so I can only see one product's information at a time. I have the grid set-up so Column A has products, Column B starts a list of demographic breakouts for each product and the headers (starting at column C-L) are for distribution groups. I have approx 8 products, so the grid is large. But, I only want to view one products and its' corresponding demos and distributions at a time. I'd like to use a pull down menu so I can select the product and the grid will fill in - any i...

How can I create a chart using stacked & reg columns together?
I need to create a chart using 2 axis (which I can do already). The part I am struggling with is on the primary axis, I need 1 standard column and 1 stacked column side-by-side. I can do a dual axis with 1 stacked column and 1 line on the primary, but not a column. Any help is greatly appretiated. Jon Peltier has information and links for clustered stacked columns on his web site: http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html DMenegay wrote: > I need to create a chart using 2 axis (which I can do already). The part I > am struggling with is on the primary axis...

How do I set up an calculation to work out somebody's age from th.
i need to work out somebody's age from their birthday at different points in time. Please help if you have an existing formula/speadsheet I've used Chip Pearson's excellent site for this. Try http://cpearson.com/excel/datedif.htm Steve In cell A1 put the birthdate of the person you;r analyzing In cell B1 put the date of interest In cell C1 put the formula =B1-A1 and then format this cell be doing Right-click > Format cells > Number tab > Custom > and type this in the Type: window yy "years, " mm "months" Vaya con Dios, Chuck, CABGx3 &q...

How to create a report RDL
Hi. I'm new in CRM, and i don't now what i need to create a report. I use a CRM 3.0 on a SQL 2000 server and I installed VS 2005, but i can't see the business intellegence project. what i need to do? thank to all. ----------------------------------------------------------------------------- Less Spam Better enjoyable experience Visit : news://spacesst.com You must install the SQL reporting components on your development system. For this you will need Visual studio 2003. -- Patrick Verbeeten (MCPD) CRM/.NET Consultant Aviva IT Tools for CRM Developers and Administrat...

VB error
Hi, I have the code below that will create a new worksheet and rename it after todays date. However if the user trys to create a log again , ie for the same day, I get this error:- Run-time error 1004 cannot rename a sheet to the same name as another sheet, a referenced object or a workbook referenced by visual basic. Oh course what I want is a msg box advising the user that one already exsists ans asking if they wanna view it. the code I have Sub Create_log() Application.ScreenUpdating = False With Sheets("Log master") .Visible = True .Copy After:=She...