Results 1 to 15 of 19

20110805, 01:43 #1
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
reset a pivot value to a specific number
hi to all,
Is there a way to change the actual data within a pivot table, in the attachment which is a sample of pivot table output i want to set any value greater than 1 to 1. i tried to insert a field setting formula but it did show in the data drop down list.
dubdubLast edited by dubdub; 20110805 at 02:24.

20110807, 08:08 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
I don't see a pivot table or the underlyiing data set only a copy of the pivot results
The pivot table is based on the underlying data set. 3 general (since you have no specifics) ways I can think of offhand: you can change the underlying data, change the calculation of the pivot table to yield different results, or on separate sheet have a formula to read the pivot table. [A formula like: =IF(C3>1,1,C3) copied to the same size of the pivot should work]
Steve

20110808, 07:11 #3
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
thanksSteve,
I was able to do that using the formula on a copy of the pivot table data, but what i am after is how can i insert that formula in the pivot table and get the same results.
dubdubTIA
dubdub

20110808, 07:45 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Could you provide an example worksheet with a pivot table and an underlying data set?
Steve

20110808, 08:09 #5
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Do you need to change the actual value or merely the displayed one?
Regards,
Rory
Microsoft MVP  Excel

20110808, 13:23 #6
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
The displayed one.
dubdubTIA
dubdub

20110808, 15:25 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Then you could use a custom number format like:
[>1]"1";GeneralRegards,
Rory
Microsoft MVP  Excel

20110809, 11:15 #8
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
thanks Roy for the help,
it dioes the change but if you do subtotal say for the "NUM 60" in the attachment you got 1 instead of sum of all the 1 in the column. Even if you copy the whole pivot the new pivot data using the custom format to a separate sheet and you try to get the sum i still get 1. any suggestion.
dubdubTIA
dubdub

20110810, 04:31 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
That won't work since:
1. You are not trying to use the displayed value, you are trying to add up the actual values, which are unchanged.
2. You would have to alter the number format for the subtotal rows so that they do not suppress values greater than 1.
Perhaps you could explain why you are trying to do this and we might come up with a workaround.Regards,
Rory
Microsoft MVP  Excel

20110810, 07:49 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Rory,
It would also require that the subtotal only count the numbers >0, not total them....
Steve

20110810, 07:52 #11
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Steve,
My suspicion is that this is an attempt to get a unique count and the numbers should be either 0 or 1, so a total would work for a count.Regards,
Rory
Microsoft MVP  Excel

20110810, 11:39 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Rory,
My suspicion was the individual items wanted the unique count (0,1) but the subtotals were to add the number of 1s...
But I guess we will see when we get more details ...
Steve

20110811, 00:04 #13
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
Thanks Roy & Steve,
let me say first i am encourged and pleased by the attension and the interest you are showing (not unusual) to help me.
Sorry it took me a while to prepare the attachment, it has a brief explination what i am trying to do (phase1), no analysis of the result yet.
TIA
dubdubTIA
dubdub

20110811, 03:38 #14
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 Posts
Can you set the pivot field data function to Max? That way you will get 1 if there's a 1 for the month, or 0 if not.
Regards,
Rory
Microsoft MVP  Excel

20110811, 05:58 #15
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 373
 Thanks
 1
 Thanked 0 Times in 0 Posts
i did and it change the figures to 1 & 0 but you got the total sum with no breakdown by other fields unless you select the sum function for each field sepreately, not the norm when you used the sum insteade of the max. in other words you need to extract and arrange of the pivot table to get the same data layout obtained with sum function.
dubdubTIA
dubdub