2007 Pivot Tables
Can someone advise if it is possible change something in Excel 2007.
Whenever I pivot any data the values fields ALWAYS comes up as count instead of SUM. 99 times out of a 100 I need it to be SUM. I'm currently having to go into each set of data and change to SUM each time. Anyone know if there is a way to get it to automatically come up as SUM? Or is the issue with the data not the pivot functions, however the data does SUM when manually changed. Many thanks in advance for any help provided Tom |
2007 Pivot Tables
Hi Tom
The default behaviour of the PT is If All values in the field are Numeric, then use Sum If Any values in the field are Text, then use Count Although you are forcing the result to be Sum, there "could" be some values which are text representations of numeric values. These will be ignored in the Summation, hence the results you are seeing may well not be correct. You can check your source data by using a spare column and entering =ISNUMBER(D2) change reference to the column of numeric data you are testing. Copy down and you will see FALSE against any entries that are incorrect. -- Regards Roger Govier "Tom Hewitt" wrote in message ... Can someone advise if it is possible change something in Excel 2007. Whenever I pivot any data the values fields ALWAYS comes up as count instead of SUM. 99 times out of a 100 I need it to be SUM. I'm currently having to go into each set of data and change to SUM each time. Anyone know if there is a way to get it to automatically come up as SUM? Or is the issue with the data not the pivot functions, however the data does SUM when manually changed. Many thanks in advance for any help provided Tom |
2007 Pivot Tables
I'm guessing there is no way to force it to SUM.
I've checked the Data and the only fields that show as Text are ones that are blank. All the fields that are meant to be number are number. I could always add a new formula columnt to make even the blanks show as a number, but that'd take just as long as changing the options to SUM. Thanks for your help "Roger Govier" wrote: Hi Tom The default behaviour of the PT is If All values in the field are Numeric, then use Sum If Any values in the field are Text, then use Count Although you are forcing the result to be Sum, there "could" be some values which are text representations of numeric values. These will be ignored in the Summation, hence the results you are seeing may well not be correct. You can check your source data by using a spare column and entering =ISNUMBER(D2) change reference to the column of numeric data you are testing. Copy down and you will see FALSE against any entries that are incorrect. -- Regards Roger Govier "Tom Hewitt" wrote in message ... Can someone advise if it is possible change something in Excel 2007. Whenever I pivot any data the values fields ALWAYS comes up as count instead of SUM. 99 times out of a 100 I need it to be SUM. I'm currently having to go into each set of data and change to SUM each time. Anyone know if there is a way to get it to automatically come up as SUM? Or is the issue with the data not the pivot functions, however the data does SUM when manually changed. Many thanks in advance for any help provided Tom |
All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com