Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
custom percentage calculation
any ideas why i would get #n/a values down the column for a custom
calculation in a pivot table? column a shows the total amount of customers, column b shows the number of customers that belong to a subset of column a, and column c (custom calculation) is supposed to show the percentage that column b is of a. when i attempt, i get #n/a values. what are the proper steps? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
custom percentage calculation
Did you create your calculation using the Pivot TableFormulasCalculated
Field option? Sounds like the pivot table can't find the field names to calculate from and so is giving you the ol' #N/A. -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "joemeshuggah" wrote: any ideas why i would get #n/a values down the column for a custom calculation in a pivot table? column a shows the total amount of customers, column b shows the number of customers that belong to a subset of column a, and column c (custom calculation) is supposed to show the percentage that column b is of a. when i attempt, i get #n/a values. what are the proper steps? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
custom percentage calculation
no; i inserted a duplicate column for column b (number of customers that
belong to a subset of column a), right clicked in one of the cells, went into field settings and then options, selected "% Difference From" from the Show Data As drop down box, chose column a (total amount of customers) as the Base Field and chose 0 as the Base Item. Where am I going wrong? "Gary Brown" wrote: Did you create your calculation using the Pivot TableFormulasCalculated Field option? Sounds like the pivot table can't find the field names to calculate from and so is giving you the ol' #N/A. -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "joemeshuggah" wrote: any ideas why i would get #n/a values down the column for a custom calculation in a pivot table? column a shows the total amount of customers, column b shows the number of customers that belong to a subset of column a, and column c (custom calculation) is supposed to show the percentage that column b is of a. when i attempt, i get #n/a values. what are the proper steps? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
custom percentage calculation
In the original Data, assume...
Col A is 'Store Name' Col B is 'Total Customers' Col C is 'My Subset' Create the Pivot Table with 'Store Name' as Row and 'Total Customers' and 'My Subset' as Data. Drag the 'Data' cell to the 'Total' Cell and drop it. The pivot should now have 3 columns...'Store', 'Sum of Total Customers' and 'Sum of Subset of Customers'. - Right Click inside the pivot table. - Select 'Show Pivot Table Toolbar' - On the Pivot Table Toolbar, select 'Pivot Table' - Select 'Formulas' - Select 'Calculated Field' - In the 'Name' box, Type 'My Calculation' - Delete whatever is in the 'Formula' box - Double click on 'Subset of Customers' in the 'Fields' box - Go up to the 'Formula' box and put a '/' after 'Subset of Customers' - Double click on 'Total Customers' - The 'Formula' box should now contain the formula... ='Subset of Customers'/ 'Total Customers' - Select 'Add' - Select 'OK' - Right click in the 'Sum of My Calculation' column - Select 'Field Settings' - Change the 'Name' field to '% of Total' -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "joemeshuggah" wrote: no; i inserted a duplicate column for column b (number of customers that belong to a subset of column a), right clicked in one of the cells, went into field settings and then options, selected "% Difference From" from the Show Data As drop down box, chose column a (total amount of customers) as the Base Field and chose 0 as the Base Item. Where am I going wrong? "Gary Brown" wrote: Did you create your calculation using the Pivot TableFormulasCalculated Field option? Sounds like the pivot table can't find the field names to calculate from and so is giving you the ol' #N/A. -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "joemeshuggah" wrote: any ideas why i would get #n/a values down the column for a custom calculation in a pivot table? column a shows the total amount of customers, column b shows the number of customers that belong to a subset of column a, and column c (custom calculation) is supposed to show the percentage that column b is of a. when i attempt, i get #n/a values. what are the proper steps? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
custom percentage calculation
i tried this previously, however whenever i attempt to add the field, nothing
happens, which is what led me to try the steps in my original post. note, despite the calculated field not appearing, if i go into the "insert calculated field" menu and hit the drop down, the name of the field i attempted to create is there...its just not appearing in the spreadsheet. "Gary Brown" wrote: In the original Data, assume... Col A is 'Store Name' Col B is 'Total Customers' Col C is 'My Subset' Create the Pivot Table with 'Store Name' as Row and 'Total Customers' and 'My Subset' as Data. Drag the 'Data' cell to the 'Total' Cell and drop it. The pivot should now have 3 columns...'Store', 'Sum of Total Customers' and 'Sum of Subset of Customers'. - Right Click inside the pivot table. - Select 'Show Pivot Table Toolbar' - On the Pivot Table Toolbar, select 'Pivot Table' - Select 'Formulas' - Select 'Calculated Field' - In the 'Name' box, Type 'My Calculation' - Delete whatever is in the 'Formula' box - Double click on 'Subset of Customers' in the 'Fields' box - Go up to the 'Formula' box and put a '/' after 'Subset of Customers' - Double click on 'Total Customers' - The 'Formula' box should now contain the formula... ='Subset of Customers'/ 'Total Customers' - Select 'Add' - Select 'OK' - Right click in the 'Sum of My Calculation' column - Select 'Field Settings' - Change the 'Name' field to '% of Total' -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "joemeshuggah" wrote: no; i inserted a duplicate column for column b (number of customers that belong to a subset of column a), right clicked in one of the cells, went into field settings and then options, selected "% Difference From" from the Show Data As drop down box, chose column a (total amount of customers) as the Base Field and chose 0 as the Base Item. Where am I going wrong? "Gary Brown" wrote: Did you create your calculation using the Pivot TableFormulasCalculated Field option? Sounds like the pivot table can't find the field names to calculate from and so is giving you the ol' #N/A. -- Hope this helps. If this post was helpfull, please remember to click on the ''''YES'''' button at the bottom of the screen. Thanks, Gary Brown "joemeshuggah" wrote: any ideas why i would get #n/a values down the column for a custom calculation in a pivot table? column a shows the total amount of customers, column b shows the number of customers that belong to a subset of column a, and column c (custom calculation) is supposed to show the percentage that column b is of a. when i attempt, i get #n/a values. what are the proper steps? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage calculation | Excel Discussion (Misc queries) | |||
percentage calculation | Excel Discussion (Misc queries) | |||
'of' percentage with calculation | Excel Discussion (Misc queries) | |||
Percentage calculation | Excel Discussion (Misc queries) | |||
Percentage Calculation | Excel Worksheet Functions |