Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Table Sum
I have created a PivotTable using data in another spreadsheet. For some
reason when I try to sum the data in the Data Field, all the values turn to zeros. But if I leave it as a count the information is correct. Is it because the data in the original cells are formulas? Any help or suggestions would be greatly appreciated. Thanks -- RedFive |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Table Sum
One way to test your theory is to take the data source & change them to
values instead of formulas. I've seen this occur when trying to aggregate blank data. -- http://www.ExcelHelp.us 888-MY-ETHER ext. 01781474 "RedFive" wrote: I have created a PivotTable using data in another spreadsheet. For some reason when I try to sum the data in the Data Field, all the values turn to zeros. But if I leave it as a count the information is correct. Is it because the data in the original cells are formulas? Any help or suggestions would be greatly appreciated. Thanks -- RedFive |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Table Sum
Some of your cells look like numbers but are actually text. There are
instructions here for converting those to real numbers: http://www.contextures.com/xlDataEntry03.html After you fix the numbers, refresh the pivot table and the sum should be correct. RedFive wrote: I have created a PivotTable using data in another spreadsheet. For some reason when I try to sum the data in the Data Field, all the values turn to zeros. But if I leave it as a count the information is correct. Is it because the data in the original cells are formulas? Any help or suggestions would be greatly appreciated. Thanks -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Table Sum
Your formulas might be converting numbers to text. For example, if you
use LEFT or TEXT functions in the formula, the result will be a text string, that might look like a number. If that's what's happening, try typing two minus signs at the start of the formula, e.g.: =--RIGHT(A2,5) RedFive wrote: I have created a PivotTable using data in another spreadsheet. For some reason when I try to sum the data in the Data Field, all the values turn to zeros. But if I leave it as a count the information is correct. Is it because the data in the original cells are formulas? Any help or suggestions would be greatly appreciated. Thanks -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Pivot Table Sum
Hi,
It's a little supprising that "all" the sums return 0. It suggests that the entire range is text not numbers. If all the data are formulas in the data area, then they all must be evaluating to text. You should be showing us what the formula in the data area are. If all of the formulas in the data area should be numbers but are text here is one way to clear the problem in one fell swoop 1. Enter 1 in a blank cell 2. Copy that cell 3. Select the entire range of formulas and choose Edit, Paste Special, Multiply. All of your formulas will be retained but their results will all be numbers. If this helps, please click the Yes button Cheers, Shane Devenshire "RedFive" wrote: I have created a PivotTable using data in another spreadsheet. For some reason when I try to sum the data in the Data Field, all the values turn to zeros. But if I leave it as a count the information is correct. Is it because the data in the original cells are formulas? Any help or suggestions would be greatly appreciated. Thanks -- RedFive |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel |