Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivottable percentages
I'm using a pivottable to addup two columns of figures, Stock and Orders
pivotted by department. I want to show the percentage (%) of stock ordered, by department by cant do it. Can anyone show me please. Thanks Dean |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivottable percentages
Hi Dean
We need to see what your raw data looks like. Post a small sample of the layout (not an attachment) and help should be available. I have to go off line now, but if there is no answer by the morning, I will pick it up. -- Regards Roger Govier "Dean" wrote in message ... I'm using a pivottable to addup two columns of figures, Stock and Orders pivotted by department. I want to show the percentage (%) of stock ordered, by department by cant do it. Can anyone show me please. Thanks Dean |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivottable percentages
Roger,
an example would be like... Dept Stock On Ord % 01 32332 157 0.485 03 15000 976 6.506 09 34000 25000 73.52 Stock and On Ord are figigures collected from MS Queries on seperate sheets. The pivottable is used to add them up and display them next to the Dept. I also want to show the percentage (%) of each department on order, to what is in stock! Dean "Roger Govier" wrote: Hi Dean We need to see what your raw data looks like. Post a small sample of the layout (not an attachment) and help should be available. I have to go off line now, but if there is no answer by the morning, I will pick it up. -- Regards Roger Govier "Dean" wrote in message ... I'm using a pivottable to addup two columns of figures, Stock and Orders pivotted by department. I want to show the percentage (%) of stock ordered, by department by cant do it. Can anyone show me please. Thanks Dean |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivottable percentages
Hi Dean
In the PT, drag Dept to the Row Area drag Stock to the Data Area drag On Ord to the Data Area Ensure that both data items are set to Sum On the resulting PT, drag the Data button and drop it on the word Total, this will give your data layout side by side as opposed to one under the other. Now, from the PT Toolbar, use the PT dropdown to select FormulasCalculated Field In the name box Type Percent In the formula box use the Insert field button to create = 'On Ord' /'Stock ' This will get added to the Pt as the third column of Data. Go to the PT Wizard, choose Layout, click on the new calculated filed Percent and change the name to % of Stock and change it's number format to Percentage with 2 places of Decimal. Change the field headings from Sum of Stock etc. to what you want, but be aware that you cannot use the same name as the already existing filed name. I sometimes change the headings in my source table to have an exclamation mark after them, as I can then use the real title I want when I format the PT Report. -- Regards Roger Govier "Dean" wrote in message ... Roger, an example would be like... Dept Stock On Ord % 01 32332 157 0.485 03 15000 976 6.506 09 34000 25000 73.52 Stock and On Ord are figigures collected from MS Queries on seperate sheets. The pivottable is used to add them up and display them next to the Dept. I also want to show the percentage (%) of each department on order, to what is in stock! Dean "Roger Govier" wrote: Hi Dean We need to see what your raw data looks like. Post a small sample of the layout (not an attachment) and help should be available. I have to go off line now, but if there is no answer by the morning, I will pick it up. -- Regards Roger Govier "Dean" wrote in message ... I'm using a pivottable to addup two columns of figures, Stock and Orders pivotted by department. I want to show the percentage (%) of stock ordered, by department by cant do it. Can anyone show me please. Thanks Dean |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivottable percentages
Hi
Totally dyslexic this morning!!! Where I used the word "filed" I meant to write "field" -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Dean In the PT, drag Dept to the Row Area drag Stock to the Data Area drag On Ord to the Data Area Ensure that both data items are set to Sum On the resulting PT, drag the Data button and drop it on the word Total, this will give your data layout side by side as opposed to one under the other. Now, from the PT Toolbar, use the PT dropdown to select FormulasCalculated Field In the name box Type Percent In the formula box use the Insert field button to create = 'On Ord' /'Stock ' This will get added to the Pt as the third column of Data. Go to the PT Wizard, choose Layout, click on the new calculated filed Percent and change the name to % of Stock and change it's number format to Percentage with 2 places of Decimal. Change the field headings from Sum of Stock etc. to what you want, but be aware that you cannot use the same name as the already existing filed name. I sometimes change the headings in my source table to have an exclamation mark after them, as I can then use the real title I want when I format the PT Report. -- Regards Roger Govier "Dean" wrote in message ... Roger, an example would be like... Dept Stock On Ord % 01 32332 157 0.485 03 15000 976 6.506 09 34000 25000 73.52 Stock and On Ord are figigures collected from MS Queries on seperate sheets. The pivottable is used to add them up and display them next to the Dept. I also want to show the percentage (%) of each department on order, to what is in stock! Dean "Roger Govier" wrote: Hi Dean We need to see what your raw data looks like. Post a small sample of the layout (not an attachment) and help should be available. I have to go off line now, but if there is no answer by the morning, I will pick it up. -- Regards Roger Govier "Dean" wrote in message ... I'm using a pivottable to addup two columns of figures, Stock and Orders pivotted by department. I want to show the percentage (%) of stock ordered, by department by cant do it. Can anyone show me please. Thanks Dean |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 PivotTable and Chart | Excel Discussion (Misc queries) | |||
Calculate PivotTable Item Totals Based On Cell Contents? | Excel Worksheet Functions | |||
QUESTION: Copy pivottable from excel file to another. | Excel Discussion (Misc queries) | |||
Dynamic parameterized PivotTable update? | Excel Discussion (Misc queries) | |||
Sorting in PivotTable | Excel Discussion (Misc queries) |