Displaying totals of calculated fields in Pivot tables/charts

I find that, when I create a Pivot table, I can only derive totals for
those items that have been entered by hand - wnen I try to get totals
of calculated fields, I only get the count of occurrences of those
fields
Any ideas ?
0
mg11 (1)
4/12/2004 5:38:13 AM
excel 39879 articles. 2 followers. Follow

1 Replies
376 Views

Similar Articles

[PageSpeed] 38

It sounds like your calculation columns may be returning something besides
numbers.

If you have formulas that look like:

=if(something's_true,"",a1*3+5)

Then that string value ("") will make it so that the pivottable wizard will
default to count.

Just double click on that "button" in the Data Area (when you're in the Wizard)
and you can change it to Sum/Average/min/max and lots more.

You could also adjust your formula to return a numeric value:

=if(something's_true,0,a1*3+5)

(But that might mess up the appearance of the original data source.)

MG wrote:
> 
> I find that, when I create a Pivot table, I can only derive totals for
> those items that have been entered by hand - wnen I try to get totals
> of calculated fields, I only get the count of occurrences of those
> fields
> Any ideas ?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
4/12/2004 9:44:38 PM
Reply:

Similar Artilces:

Calculate the number of seconds between two times
I need to calculate how many seconds are between two times, e.g. 8:08 AM start time and 8:44 AM stop time so I can use the result in a formula. The start and stop times are in neighboring columns. The formula would be result= number of counts / (Stop time-Start time) in seconds to give me a result in counts/second. On Tue, 1 Jun 2010 13:24:01 -0700, Budda Belly <Budda Belly@discussions.microsoft.com> wrote: >I need to calculate how many seconds are between two times, e.g. 8:08 AM >start time and 8:44 AM stop time so I can use the result in a formula. > >Th...

excel pivot table with sql query and visual basic macros
I have a pivot table that runs off a sql query, it has macros and parameters built into the report. It worked until the client updated their server and db software. When debugging this, on the module under the closedb it stops here: Sub CloseDB() 'IF CONNECTION IS OPEN IT NEEDS TO BE CLOSED SO AN ERROR IS NOT THROWN If rsData.State = 1 Then rsData.Close Set rsData = Nothing End If cnData.Close Set cnData = Nothing Set Cmd = Nothing End Sub Thanks ...

calculate null text field
Dear Helpers, I would like to creat a calculated field in a query that merch 3 text fields together, like, Index: [field A]&[field B]&[field C]. I don't want to replace the null field with anything so that if [field A] and [field B] are null and only [field C] contains data, the calculated [Index] field will look exactly like [field C]. Is it possible? I hope it is possible coz the Index field is a major field in my database. Thanks a lot to my helpers!! What happens if you type: [field A] & [field B] & [field C] into the Field row in query design, and choose A...

auto display names sent
When I send a message it will only keep the name for auto populating for about a week. How do I change that setting? WYSIWYG. Autocomplete for TO line will only offer the last 29 contacts you'd entered. There are no plans to fix this or any other bug. Darton wrote: > When I send a message it will only keep the name for auto populating for > about a week. How do I change that setting? Windows Mail auto-completes from a separate list of 29 addresses you type in, not from your contacts. This list is stored in the registry. If you wish to remove old or incorrect ad...

Pull part of a field
I have a field that gives data like H-A-0012-02 I would like on the report to just display the A from the string. Michelle If the "A" is ALWAYS in the third position, you could use something like: Mid([YourFieldName],3,1) in your query. Regards Jeff Boyce Microsoft Office/Access MVP "Michelle" <Michelle@discussions.microsoft.com> wrote in message news:E4059557-C5DD-4498-AEA7-4200B4172A89@microsoft.com... >I have a field that gives data like H-A-0012-02 I would like on the report >to > just display the A from the string. ...

Stored procedure wont insert Fields, RadioButtonLists and CheckBox
Hello Community I have a CheckBox and a RadioButtonList and fields on an aspx web page form. I pass the fields on the form to a method that creates the parameters and executes a stored procedure and the stored procedure is supposed to insert this information as a row into a table. For some reason the stored procedure executes but the row does not get inserted. I can’t see why the row doesn’t get inserted especially since I put BreakPoints in the code all along the way and I see the values just as they get passed into the stored procedure but maybe someone looking at this ...

Printing string from string table inside for loop?
#define IDS_STR1 _T("Str1"); #define IDS_STR2 _T("Str2"); #define IDS_STR3 _T("Str3"); #define IDS_STR4 _T("Str4"); #define IDS_STR5 _T("Str5"); for(int i = 0; i < 5; i++) { ............................ ............................ } I am looking for the logic that can be used to print those strings inside the "for" loop. Any help is welcome. The #define's are put into place as part of the pre-compilation process, thats why they are also called pre-processor directive. So, you can not use a variable with a symbol defined ...

How can I pre-filter a table that is used in a query in a live dat
How can I pre-filter a table that is used in a query in a live database? I have about 47 queries (SQL text in a memo field in a table) that all reference a few tables in the same database. I would like to limit the table’s data instead of re-writing all the queries to include further criteria. The queries are very complex and it is not desirable to re-write the queries and use parameters. This database is live on an ASP.NET web page and there may be multiple people viewing the reports which are using these queries. Since there are multiple users I can not simply copy the tables into ...

font color not displayed
When selecting text to display in another color, it still displays in black on my monitor. I have looked at my emails on the recipient's monitor, and the color DOES display - just never on mine. ...

Drive's display name
On my XP machine I have a DVD drive and a CD drive. Windows Explorer displays these as "DVD Drive (E:)" and "CD-RW Drive (F:)". When I try to use SHGetFileInfo and IShellFolder's GetDisplayNameOf(), both return "CD Drive (E:)" and "CD Drive (F:)". How can I get the display name that Windows Explorer uses? ...

add result column to pivot table
I have 2 columns in a pivot table - decription and amount. I need to calc a % of each value of the total. I don't know how to do that. Add another copy of the Amount field to the pivot table's data area. To display, the fields horizontally, drag the Data button, onto the cell that contains the word 'Total' -- there's a picture here: http://www.contextures.com/xlPivot02.html Right-click on the heading for the second Amount column Choose Field Settings Click the Options button Type a Name for the field, e.g. Percent Choose to 'Show Data as' % of column Cli...

Pivot Table Month Order
The month order as a column heading is in the correct order except that Sep is at the very end rather than after Aug. I'm new at working with pivot tables and have been unsuccessful in researching and correcting the problem. Thanks. The problem is you are using month names, which Excel, naturally sorts into alphabetical order. Your choices are: 1. Use month numbers -- at least they will sort properly. 2. Use the full date, which you can format to display just the month name. You the display that you want. and your results will be sorted properly. Regards, Fred &quo...

How keep chart-datapoints displayed when column-width is set to zero
Hello there, my data basis for a chart has one column, that I don't want to be shown in the spreadsheet. However, when I set this column's width to zero, the correspondig data dissapear from the chart. How can I have both, a hidden column and its values shown in the chart at the same time ? Kind regards, H.G. Lamy H.G. - Select the chart, then select Options from the Tools menu, click on the Chart tab, and UNcheck the Plot Visible Cells Only checkbox. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ hglamy wrote: &...

Create a Line Chart
I am having great difficulty trying to create a line chart in Access 2007. I have a report with the following fields: Staff_Component 1QTR 2QTR 3QTR 4QTR Operations 10 28 45 33 Can someone please explain to me how I can create a line chart? Thanks and Happy Holidays! *Glen* The report fields don't mean anything. You can generally add a chart control to your report and use the wizard to set up your chart properties. I expect you will need to update the Row Source property since the wizard often clobbers it. T...

Charting software: what does The Economist use?
Any clue of what software the people at The Economist uses to create charts? I=92m wondering whether there is some software out there better than MS Excel that would give better control without the onerous workarounds. You can do pretty well with Excel, and the "onerous" workarounds aren't too bad once you've become familiar with them. They are generally a bit more onerous in 2007. http://peltiertech.com/WordPress/2008/05/30/magazine-quality-chart-economist/ - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - ...

Layering Chart Types
I would like to layer a line graph on a second y axis on top of a "stacked column" graph with 3 data series in it. Anyone know how to do this? Create a stacked column chart with all 4 series in it. Then, select the series you want to become a line. Go to Chart, Chart type, and pick a line series. Then, again selecting the same series, right-click, format, axis, move to secondary axis -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Jeff" wrote: > I would like to layer a line graph on a second y axis on top of a "stacke...

Sprdsht not calculating
I have a large workbook: many sheets, many calculations, much formatting. (I know, I know, way too much overhead but it's a corporate mandate to use this sprdsht. I'm working on reducing it.) I have manual calculation toggled on. When I press F9 the percentage indicator scrolls up quickly to 100% and the "Calculate" flag in the lower left corner of the screen disappears. But I can see formulas that are not calculating, and the only way to force a calculation is to press F2 to edit the cell and hit enter to derive the true value. This is not a case of the "Calculate...

import pdf table
What is the best way to inmport a table in a pdf document into excel? Check out: http://www.library.mcgill.ca/edrs/services/publications/how to/PDFtoXLS/PDFtoExcel.html#basicexports HTH Jason Atlanta, GA >-----Original Message----- >What is the best way to inmport a table in a pdf document >into excel? >. > Those directions are for university students that have Adobe Acrobat installed (not just the reader). Though there is a reference immediately above that does refer to the acrobat reader Most people do not have the full Adobe Acrobat software. With just the Acrob...

Pivot Charts Display Filter Box
I would like to have the filter box displayed on my pivot chart. My 2003 pivot charts no longer display the drop down box, how do I get it back? ...

Page No display
How to insert page no in a particular cells. I had tried with Header option in Print Preview menu, but after applying the same , Page no is not exactly comes in particular cells,either it may displayed above or below the cells. -- NITESH G ------------------------------------------------------------------------ NITESH G's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=17590 View this thread: http://www.excelforum.com/showthread.php?threadid=497514 ...

charting a single bar graph on one line showing continuous info
I must do a time graph showing total workhours with tick marks showing begin and end of tours on a dialy basis. I cannot get the custom or standard chart graph functions to do this without putting in all of the data. I just want the begin tour and end tour of each person. can you help. thx debi ...

pivot table page field hidden items
Is there a vba code that can determine the "pivot table page field hidden items"? I tried the "visible" property for the page field items. It returned "True" only for the "CurrentPage" item and "False" for all the others. You could set each item as the current page. Those that are hidden will create an error, instead of becoming visible. For example: '======================== Sub PageItemsHidden() Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem Dim piCurr As String Set pt = ActiveSheet.PivotTables(1) Set pf = pt.PivotFiel...

Color for chart series auto-assigned after 56?
Using Excel 2000, I am having a color issue with charts that exceed 5 series. Below is some sample VBA to reproduce the issue. The cod creates a bar chart with 60 series. The RGB color I have chosen i white (255, 255, 255). You will notice that beginning with series 57 the color of the columns no longer gets assigned the color that I want Excel starts auto-assigning colors at this point. I have reproduce this faithfully 100% of the time. Has anyone seen this before? I there a programatic workaround so that I don't have to manually chang the colors after the fact? Thanks- Craig Sub ...

Error bars not displaying properly
Hi, I have plotted my data in a column graph, and added error bars based on custom values. I know how to edit and format the error bars, but nothing I have changed fixes my problem: The error bars "join up" to give diagonal lines between between my columns. I have NEVER had this issue in previous versions of Excel. If I plot the error bars in pre-2007 excel, they're fine, until I open them in excel-2007. If I remove the error bars, and plot them afresh in excel-2007, I get the same problem. It doesn't happen to every error bar, just some of them, for no apparent ...

is there a way to make the bars on a bar chart narrower?
help anyone? -- mcarrington ------------------------------------------------------------------------ mcarrington's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21641 View this thread: http://www.excelforum.com/showthread.php?threadid=498513 Hi mcarrington, Have you tried right clicking a bar then Format Data Series then change the Gap width? Ken Johnson Hi mcarrington, sorry, I left out one step... Right click a bar>Format Data Series>Options>change Gap Width. Ken Johnson ...