Data shows up as 1 or 0 in pivot table

[Chookz]

Hi all,
I am trying to construct a pivot table from an excel database ive made. Ive added my fields the way i want it setup and by default my data is summarised by count. This shows up as 1 in the data field. When I try and change the 'summarise data by' to sum it comes up as 0, and when i try to summarise by average i get #DIV/0! and i dont know how to get my data (most of which i formatted h:mm:ss) to show up. Am I doing something wrong with my pivot table or is anyone able to help me get my data to show up? Im very new with pivot tables and have been thrown in the deep end at work, so any help is greatly appreciated.
Ill add a link to a screen shot of my pivot table if that helps.

Cheers

http://i53.tinypic.com/16banpv.jpg

 

[Glory]

Screenshot of the data itself would help.

 

[RoryA]

To me that would imply that your data is actually stored as text not numeric data

 

[Chookz]

^ Sure.
When i export my data it comes out like this, and ive put it all on one line (row 28,29):

http://i51.tinypic.com/24q32g5.jpg

which I then copy and paste into a new worksheet where im building my database and pivot table from:

http://i55.tinypic.com/2ds1pqc.jpg

Does this help? Im happy to give more info

 

[RoryA]

All the columns that are left aligned by default are text, not numbers. What you can do is copy a blank cell, then select all your data, click the Paste dropdown on the Home tab and choose Paste Special, then choose Values and Add from the options. That should convert anything that can be converted into a numeric value.

 

[Chookz]

All the columns that are left aligned by default are text, not numbers. What you can do is copy a blank cell, then select all your data, click the Paste dropdown on the Home tab and choose Paste Special, then choose Values and Add from the options. That should convert anything that can be converted into a numeric value.

Thanks Rorya, great tip!! That has worked a treat for all cells ive tried it on except for cell E2 in the 3rd link ive posted, which contains the following - 117 (63%). When I do your trick then type =isnumber for that cell it still shows false. Do you or anyone know of a way to convert this cell to a number?

 

[RoryA]

It's not a number - it's two, so treated as text. What would you want it converted to?

 

[Chookz]

It's not a number - it's two, so treated as text. What would you want it converted to?

I just want the value 117 (63%) to show in my pivot table, as at the moment its text and comes up as 0 when i try to summarise by or format. Do you know of a way to get it to show in my pivot table?

 

[RoryA]

No, because it isn't a number. You would have to separate it into two number fields in your data to have it show up in a PT.

 

[Chookz]

OK thanks. I know Ive asked a lot already, but do you know a formula to help me split it into 2 number fields? Without using text to columns because that will screw up other cells in my database. All are 3 numbers i.e. 117 (although there is the posibility of 2 numbers i.e. 97, a space, and then a percentage in brackets i.e. (63%) - the percentage is usually 2 digits, although there is the odd 3 digit (101%) in there.
If this starts to get too tricky, then all i really need for the pivot table is the number (in a number format) and not the percentage.
Cheers