count distinct column values in pivot table?

[jennxf]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]sentiment score
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]0.9
[/TD]
[TD]0.9
[/TD]
[TD]0.8
[/TD]
[TD]0.8
[/TD]
[/TR]
[TR]
[TD]count
[/TD]
[TD]300
[/TD]
[TD]250
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]16
[/TD]
[/TR]
</tbody>[/TABLE]
I have a pivot table here with columns equal to sentiment scores.

I would like to sump up distinct values for each sentiment score to get something like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]sentiment score
[/TD]
[TD]1
[/TD]
[TD]0.9
[/TD]
[TD]0.8
[/TD]
[/TR]
[TR]
[TD]count
[/TD]
[TD]550
[/TD]
[TD]8
[/TD]
[TD]18
[/TD]
[/TR]
</tbody>[/TABLE]
Is this possible for excel 2011 for Mac?

Thanks!
J

 

[drWatson]

Hello,

Using the "Sentiment score" as a pivot field should do just that (sum up per value).

So if you pull the "Sentiment score" field to the column label section of the pivot table, you should be fine.

If not, it may be that your scores, although displayed as similar values, are in fact slightly different behind the comma and Excel sees them as different values (for instance, the two 0.9 score may be 0.86 and 0.87).
> In number format, try displaying additional positions behind the comma to verify this.

Cheers,
drW

 

[jennxf]

Hi drW,

Yes the numbers are infact something like 0.87 or 0.86 but i already changed the entire column to 1 decimal place before transforming it to a pivot table. How can i ensure that excel doesnt see the hidden values?

Thanks!

Hello,

Using the "Sentiment score" as a pivot field should do just that (sum up per value).

So if you pull the "Sentiment score" field to the column label section of the pivot table, you should be fine.

If not, it may be that your scores, although displayed as similar values, are in fact slightly different behind the comma and Excel sees them as different values (for instance, the two 0.9 score may be 0.86 and 0.87).
> In number format, try displaying additional positions behind the comma to verify this.

Cheers,
drW

 

[drWatson]

Hello,

When you say you have changed the number before assembling them into the pivot table, do you mean that:
- you have changed its number format to display only one decimal?
- or that you have actually rounded up the number so that the number are exactly, mathematically, equal, i.e. they are exactly 0.9?

To verify what Excel "sees" in the pivot table, I'd recommend you change the number format of the sentiment score row in the pivot table (not in the source data) to display 3 decimals - this is to check whether the columns are different or not.

To effectively round up the source number to 1 decimal, use the ROUND function on your source data (in another column) as follows: = ROUND ( Number, 1)
- where 'Number' is your source number, and '1' is the number of decimal positions.

Then use this new column (containing the rounded up values) as the source data for your pivot field.

Does that work for you?

Cheers,
drW