Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    3 Star Lounger
    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.

    dubdub
    Last edited by dubdub; 2011-08-05 at 03:24.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    3 Star Lounger
    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.

    dubdub
    TIA
    dubdub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Do you need to change the actual value or merely the displayed one?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    The displayed one.

    dubdub
    TIA
    dubdub

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Then you could use a custom number format like:
    [>1]"1";General
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    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.

    dubdub
    TIA
    dubdub

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  12. #12
    WS Lounge VIP sdckapr's Avatar
    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

  13. #13
    3 Star Lounger
    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 (phase-1), no analysis of the result yet.

    TIA
    dubdub
    TIA
    dubdub

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  15. #15
    3 Star Lounger
    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.

    dubdub
    TIA
    dubdub

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •