All 0's for Sums in Pivot Table

[Angela Giuliano]

I have a large data set which I am trying to summarize. Basically I am
trying to sum the weight of fish caught by site. It shows the counts
correctly but everytime I try to switch the calculation to sums, it displays
0 for all of the cells which I know not to be correct. Does anyone know what
is going on? I have been trying to figure it out all day.

If it helps, I am using Excel 2002.

[Peo Sjoblom]

Most likely the weights are seen as text by Excel, go to source code and use

=ISTEXT(A2)

replace A2 with the first cell in weight column, copy down, if you get TRUE
the values are text, one remedy would be to copy an empty cell, select all
the weights and do edit>paste special and select add. If that does not work
you must have hidden characters in your range.

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Angela Giuliano" <Angela [email protected]> wrote in
message news:[email protected]...
>I have a large data set which I am trying to summarize. Basically I am
> trying to sum the weight of fish caught by site. It shows the counts
> correctly but everytime I try to switch the calculation to sums, it
> displays
> 0 for all of the cells which I know not to be correct. Does anyone know
> what
> is going on? I have been trying to figure it out all day.
>
> If it helps, I am using Excel 2002.

[CaptainQuattro]

Could it be that your source data looks like numbers but is actually a collection of text strings?

Try the following:

Right-click on the area below your spreadsheet where it says "Ready" and make sure that "Sum" is checked on the pop-up menu that appears.

Select a few of the numbers in your data that you want to sum, and see if the sum appears on the right, below your spreadsheet.

If not, you need to convert strings to numbers as follows:


Select the column that contains your numbers.
Click Data > Text to Columns > Next > Next > General > Finish

[Angela Giuliano]

Nope. All came up as FALSE. Guess I have the hidden characters option . . .
Any way around that?

"Peo Sjoblom" wrote:

> Most likely the weights are seen as text by Excel, go to source code and use
>
> =ISTEXT(A2)
>
> replace A2 with the first cell in weight column, copy down, if you get TRUE
> the values are text, one remedy would be to copy an empty cell, select all
> the weights and do edit>paste special and select add. If that does not work
> you must have hidden characters in your range.
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> Excel 95 - Excel 2007
> Northwest Excel Solutions
> www.nwexcelsolutions.com
> "It is a good thing to follow the first law of holes;
> if you are in one stop digging." Lord Healey
>
>
> "Angela Giuliano" <Angela [email protected]> wrote in
> message news:[email protected]...
> >I have a large data set which I am trying to summarize. Basically I am
> > trying to sum the weight of fish caught by site. It shows the counts
> > correctly but everytime I try to switch the calculation to sums, it
> > displays
> > 0 for all of the cells which I know not to be correct. Does anyone know
> > what
> > is going on? I have been trying to figure it out all day.
> >
> > If it helps, I am using Excel 2002.
>
>
>

[Angela Giuliano]

Thank you! That worked!!!

"CaptainQuattro" wrote:

>
> Could it be that your source data looks like numbers but is actually a
> collection of text strings?
>
> Try the following:
>
> Right-click on the area below your spreadsheet where it says "Ready"
> and make sure that "Sum" is checked on the pop-up menu that appears.
>
> Select a few of the numbers in your data that you want to sum, and see
> if the sum appears on the right, below your spreadsheet.
>
> If not, you need to convert strings to numbers as follows:
>
>
> Select the column that contains your numbers.
> Click Data > Text to Columns > Next > Next > General > Finish
>
>
> --
> CaptainQuattro
> ------------------------------------------------------------------------
> CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
> View this thread: http://www.excelforum.com/showthread...hreadid=544938
>
>