Manipulating totals and columns in Pivot Tables

Hi,

I'm constructing a pivot table that has investors as columns,
investments as rows, and the general data is values.  Trouble is there
is a column which I want to only display half of each value.  I know
that I can make a formula that divides a column "column A/2", and then
hide the unwanted columns.  But I would also like the hidden columns
to not be included in the grand totals.  How do I make this happen?

Thanks!
0
jbschipper (10)
11/11/2008 6:54:44 PM
excel 39879 articles. 2 followers. Follow

6 Replies
989 Views

Similar Articles

[PageSpeed] 42

Hi,

So let me understand what you want, something like this in the Pivot Table:

A                           B                          C
Name
Jp                         100                       50
AA                        200                       100
BB                        300                        150
.....
Grand Total           600                        ---

In the pivot table the Grand Total under column C you want empty?

You could format that cell to the custom format ;;;

-- 
Thanks,
Shane Devenshire


"jadeB" wrote:

> Hi,
> 
> I'm constructing a pivot table that has investors as columns,
> investments as rows, and the general data is values.  Trouble is there
> is a column which I want to only display half of each value.  I know
> that I can make a formula that divides a column "column A/2", and then
> hide the unwanted columns.  But I would also like the hidden columns
> to not be included in the grand totals.  How do I make this happen?
> 
> Thanks!
> 
0
11/11/2008 9:50:01 PM
> If you start with your table here

> A =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 B =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0C         Totals
> Name
> Jp =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 100 =A0 =A0 =A0 =A0 =
=A0 =A0 50        150
> AA =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0200 =A0 =A0 =A0  =A0 =
=A0 100       300
> BB =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0300 =A0 =A0 =A0 =A0 =A0=
 =A0150       450
> ....
> Grand Total =A0 =A0 =A0 =A0 =A0 600 =A0 =A0 =A0 =A0 =A0 =A0 300     900

And I want....
>  A                           B            D      Totals
> Name
> Jp                         100          25      125
> AA                        200          50      250
> BB                        300          75      375
> ....
> Grand Total           600        150       750

So column D is 1/2 of column C.   Column C is either deleted or hidden
(I think it needs to be hidden so I can take out 1/2).  The totals
column is just adding columns B & D  --- completely ignorning Column
C.

Does that make sense?

>
0
jbschipper (10)
11/11/2008 10:40:04 PM
If "D" is always "C"/2 why have both "C" and "D" ?

Move the Totals into "D" and "D"= "B"+"C"/2

Eric



"jadeB" <jbschipper@gmail.com> wrote in message 
news:39d9a6d9-7ca5-451c-93b8-6792600cb581@a26g2000prf.googlegroups.com...
> If you start with your table here

> A B C         Totals
> Name
> Jp 100 50        150
> AA 200   100       300
> BB 300 150       450
> ....
> Grand Total 600 300     900

And I want....
>  A                           B            D      Totals
> Name
> Jp                         100          25      125
> AA                        200          50      250
> BB                        300          75      375
> ....
> Grand Total           600        150       750

So column D is 1/2 of column C.   Column C is either deleted or hidden
(I think it needs to be hidden so I can take out 1/2).  The totals
column is just adding columns B & D  --- completely ignorning Column
C.

Does that make sense?

> 


0
ericNOSPAM (46)
11/11/2008 10:59:56 PM
The data sheet is set up that the full amount imports into Excel.  The
full amount is used on several other sheets - if I change the data to
half of the full amount I'll just have the opposite problems elsewhere
- because the number would then need to be doubled.
0
jbschipper (10)
11/12/2008 2:51:55 AM
Hi,

I note that C=B/2 is this pure accident?  If not and D=C/2 then D=B/4
1. In which case you don't need C at all
2. Right click the grand totals if you have any and choose Hide
3. Now to create the Total it is just B+D = B+B/4 = 5*B/4 another calculated 
field.

I tested it and it work just fine

"jadeB" wrote:

> > If you start with your table here
> 
> > A                           B                C         Totals
> > Name
> > Jp                         100             50        150
> > AA                        200            100       300
> > BB                        300            150       450
> > ....
> > Grand Total           600             300     900
> 
> And I want....
> >  A                           B            D      Totals
> > Name
> > Jp                         100          25      125
> > AA                        200          50      250
> > BB                        300          75      375
> > ....
> > Grand Total           600        150       750
> 
> So column D is 1/2 of column C.   Column C is either deleted or hidden
> (I think it needs to be hidden so I can take out 1/2).  The totals
> column is just adding columns B & D  --- completely ignorning Column
> C.
> 
> Does that make sense?
> 
> >
> 
0
11/12/2008 6:02:01 AM
C=B/2 was an accident (I actually just copied your table).  But your
answer gave me an idea that I think I can run with.  Thanks.
0
jbschipper (10)
11/12/2008 2:51:49 PM
Reply:

Similar Artilces:

repost: split excel columns
Thank you for the feed back on the below question. When using "text to columns" it seems to create new columns and the info. in one column can be split but I was looking to essentially create two columns in one. For instance, within column D which was widened, I wanted two columns in rows 5:15. I'm trying to convert something into an excel template that is too long and don't want to change any of the column widths already in place. I know you can do this in Word when transferring an excel table. You would right click on the cell and there is a function called split ...

Sum a column that meets two criteria
I need to sum a column of numbers if it matches two different criteria. I can set up the SUMIF easily for meeting one criteria, but I need to also sum the column if it meets that criteria, and another. For example: A B C 1 150 ABC MS1 2 200 DEF MS0 3 100 LMN MS0 4 125 ABC MS1 5 175 LMN MS1 6 225 DEF MS0 I need to have a formula that would say <<Sum column A IF column B = "DEF" AND column C = "MS0">>. (and so forth for the other combinations). I know there has to be a way to do this, probably using a combination of an IF and SUMIF functions - but i keep...

Lookup two columns
I want to compare the contents of two (adjacent) cells in one sheet with two adjacent cells in another sheet (within one workspace) and if the *pair* of cells are the same, deliver the value in the cell a few columns along (if you know what I mean - like lookup but comparing two cells). The cells are not sorted. Any ideas? Cheers. Bobby If you are comparing A1-B1 sheet 1 to A1-B1 sheet 2, then =IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),"They match","no match") If you have to "lookup" A1-B1 against the whole columns of A and B on sheet2, then kyou co...

Nice Column Graph
So I have data like this: Year Month #start #end 2001 1 4 2 2001 2 6 5 2001 3 7 1 2001 4 5 4 2001 5 2 6 ..... I'd like to make a column chart where the year and month are on one axis and then I have a column for each #start and #end for each month/year pair. Is there a way to tell the chart wizard that I want to use those two columns for the x axis and then the #start and #end columns for the other? Thanks. Hi, Remove the 'Year' and 'Month' text from the 2 cells and then select and use the chart wiza...

Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use the grouping function to group data into monthly basis. But I want to know that can I set the formula to calculate the months difference between, say the sales amount of June & July, and the % of the difference?? Million thanks If you have a grouped field, you won't be able to add a calculated item to the pivot table. In the source data, you could add a column to calculate the month for each record. Refresh the pivot table, and add the new field Add another copy of the Data field to the data area Right-c...

Count column difference
Hi Using MSExcel 97. I have two columns of data e.g. A1: A4, containing values 5,10, 3, 6 B1:B4, containing values 3, 8, 7, 4 I wish to perform a count (e.g. in C5) of the number of rows where the value in column A exceeds the respective value in column B (in this case count = 3, as A1>B1, A2>B2, and A4>B4). Just cannot get my formula right. Tried using an array (but difficult when comparing the difference between two columns), and COUNT. Thanks in advance for any suggestions. Wizzy ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.co...

Text to Columns from drop down list update
Hi I need to perform a Text to Column conversion from a drop down list, but I also need the extracted value to be updated if the value in the list is changed. eg: Drop down list has 2 values: 1. 4x16K 2. 8x8K If the user selects 1 I can easily extract out to another cell the value 1 using Text to columns, however if the user then changes the choice to 2 the text to columns extraction is not updated to 2. Is there a way to update changes in the original cell using text to columns? Or is there another way I can assign a value to a drop down menu choice in a different cell while havi...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

moving columns
Hi all, I have a sheet in which in row 3 there is data like this: ColumnA B C D E F Customer name 1-30 30-60 60-90 90-270 270-300 what is want is that 270-300 should move in front of 1-30, I mean t say that column F should move in front of Column B but after column A movement should be on basis that in column F, it is written 270-300 and columns may change , i mean to say that sometimes there is no 30-6 and 90-270 column columns may increase or decrease do we have a solution to this thank u al -- Message posted from http...

Validation
I would like to have combo box functionality for the data validation feature in Excel 2000. This doesn't seem to be available in the native validation setup dialog box. Am I missing something? I would like to display the validated items list in the leftmost column and have column(s) of description display to the right of each list item (so I can tell which list item I should select). Suggestions? Many Thanks, Bill Vallance Bill Debra Dalgleish has instructions for creating dependent DV lists. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben Excel MVP On Tue, 08 Jun 20...

Create Pivot Table Reports
I just finished installing Analysis Cube on the server, everything seemed working fine until when I try to create the Pivot Table Report (Tools->Analysis Cubes->Create Pivot Table Reprots). After I called up the pre-defined 'Definition ID' and click on the Excel icon, a message popped up saying " The WHTemplate.XLT file was not found." Anybody have any idea what that is, and how to resolve it?? Thanks. 1.) There are two pieces of software. Did you install both the server piece, and the client piece? 2.) Is Excel installed on the machine, on which you'r...

Excel CSV leaving out empty columns from row 17 onwards
Excel omitting commas in random ways !! Anyone come across this ? When I save this file in csv using excel 2003 A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,S,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-06,B,1,2,,3.5,BP,N,O,,,,,,,,,,,, A,,C,D,,21-Nov-06,27-Nov-...

Importing a table from Access query
What is the quickest and easiest way to do the above? Thanks in advance. Hi The way I usually do this is run the select query, click on the top-right box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste into my workbook. An alternative is to right-click on the query in the Database Window and left-click on Export. In the Save As dialog box, select ..xls type. -- Andy. "Trish" <Trish@discussions.microsoft.com> wrote in message news:6BF3DE22-6590-4CAD-9EE1-FC978A3BB63B@microsoft.com... > What is the quickest and easiest way to do the above? >...

I would like to export the data from a drop-down list to a table
I have a form with several drop-down lists, I need to have the information in these lists in another document/spread sheet. Is there an easy way to copy the data in these lists to another location? The long way would be to re-type all of it. ...

Changing Exch5.5 GAL columns in Outlook client view
Sorry if the subject line is a bit cryptic; anyway, in the Outlook client, if you display the 'Address Book' you get the following columns - name, business phone, office, title, company, alias, e-mail type, & e-mail address. How do you change it so you show the display name, business, extension, cell phone, & internet email address? Basically, how/where do you change the columnar data the client sees? Thanks, Mike Lawson Mike Goto View | Columns and there u can manage views... KJ "Mike Lawson" wrote: > Sorry if the subject line is a bit cryptic; anyway, i...

Write conflict error with ODBC link table
Hi, I have migrate my back end access tables to SQL Server. While editing data (ODBC link) from form, I receive Wirte conflict error 'The record has been changed by other user... Copying the change to the clipboard...'. The error allow me either copy the info to clipboard or drop change. In this case, how can I save my work to the table here? SF � "SF" <xyz@online.com.kh> ������ ��� ������ news:#F7F7OtcIHA.5160@TK2MSFTNGP05.phx.gbl... > Hi, > > I have migrate my back end access tables to SQL Server. While editing data > (ODBC link) from form, I...

Stacked and single column in same chart?
How can I do a chart with a stacked column beside a single column? When I build a stacked column chart, any new source data I add wants to put it in the same stacked column. Use one of the links on this page. You need to set up the data so the single column is in a stacking position with no other columns of data. http://peltiertech.com/Excel/ChartsHowTo/ClusterStack.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Einstine wrote: > How can I do a chart with a stacked column beside a sing...

not repeating text boxes in reports with columns
Hello, I am trying to create a report with columns without repeating certain text boxes. Here is an example of what I would like to create: [Date] "Month1" [Date] "Month2" [Date] "Month3" [Product] "Product1": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product2": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product3": [quantity] [value] [quantity] [value] [quantity] [value] [Product] "Product4": [q...

Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have column that has thousands of rows of information in it, it looks lik this A 40432 432654 3432 532543 32432 523 53425 532532 532 523 532 111 222 333 666 numbers that go on into mabye the 5000-6000 range what i need to do is have that data moved So it looks like this A | B | C 40432 | 32432 | 532 432654 | 523 | 523 3432 | 52432 | 111 532543 | 532532 | 222 So on and so on, so instead of 1 column with 6000 lines it ...

MATCH function; comparing letter grades in columns
I am currently comparing the contents on two cells in columns H & I (for example, cells H10 and I10). The contents are letter grades (i.e., A, A-, B+, B, etc., through E). I have my function tell me if there is any difference between the two columns and report the result (i.e., =93no change,=94 rating decrease,=94 or =93rating increase=94) in th= e adjoining column, J10. I use the MATCH function, and since it can=92t distinguish between the letter grades, I have placed the letter grades in cells M2 through M13. My function is as follows: =3DIF(MATCH(H10,M$2:M$13,0)=3DMATCH(I10,M$2:M$1...

center data labels on my column chart
How do i get my data labels to appear in the center of each column on my chart? Thanks. Click on one of the labels (that will select all the labels in the series) Choose Format>Selected Data Labels Select the Alignment tab From the Label Position dropdown, choose Center Immediately after formatting the first series, if there are other series in the chart -- Select a label in another series Press the F4 button on the keyboard, to repeat the formatting from the previous series Repeat for all series Dan wrote: > How do i get my data labels to appear in the center of > e...

my formats get pushed out when I insert columns
Hi folks, I am having trouble when inserting columns in a large data field. Whenever I insert a column at some point in the data field, th formulas within the data field adjust, or 'stretch', to accomodate th added column, however my conditional formatting gets pushed along t the right and doesn't adapt itself in the same way the formulas do. This means whenever I insert a column, the formulas remain correct bu I have to reset the conditional formatting for the entire data field. I would be extremely grateful for any advice on this situation. Best regards, melvi -- Message pos...

Still column space after making row group hidden
In a SSRS 2008 report I have a number of parent level row groups which the previous group set the hidden value of the child group. I have the canshrink set to True of the cells however this still leaves a blank space where the group data was. Is it possible to remove this blank space from the report when that row group is hidden in a tablix? ...

removing duplicate values from a column
It sounds so simple that I must be overlooking the obvious answer, but I can't seem to find a way to remove duplicate values from a column. The case is simple: I have 600+ emailaddresses in column A, sorted alphabetically and there's a bunch of duplicate addresses in there (some of 'em occur up to 7 times!!). Instead of manually running through the whole list, removing the duplicates, there MUST be some soft of filter/function in excel to do this for me... Could anyone PLEASE help me with this!?!? Cheers! -- NOTE: remove the spamtrap from the emailaddress Jops, Select the em...