+ Reply to Thread
Results 1 to 3 of 3

Return max/min value from array excluding "tota"l value in the column. (Pivot Table)

  1. #1
    Registered User
    Join Date
    11-22-2012
    Location
    Karlsruhe, Germany
    MS-Off Ver
    Excel 2007
    Posts
    9

    Return max/min value from array excluding "tota"l value in the column. (Pivot Table)

    Hi,
    Im trying to apply conditional formatting on my pivot table values' fields in order to get the right data bars set along the column. The thing is, the values are in negative order, because they represent savings... And when I add the data bars it naturally filters from the lowest to the highest value, which means it will get the max abs value as the lowest and the min abs value as the highest, because they are negative... And I want it the other way round instead. ideal.PNG This is the ideal format which is wanted...
    One workaround I've thought about was binding the data bars rule to cells which have formulas getting the minimum and maximum value within the range filtered throughout the pivot, and therefore instead of setting Lowest-Highest rule, I set the number-number and look up the cells where the formulas are, closing the range and formatting... As it is a variable range, I dont know how to come up with a formula that restricts the range until the last value before the Pivot TOTAL is shown.

    I have used this formula =MIN(I14:I33) on a locked range with no criteria and returns the minimum value, which is the highest saving in that case. Another formula im using to get the maximum value, which is the lowest saving is this one =INDEX(I14:I33;MATCH(2;(1/(I14:I33<0)))). I cant fix the last cell as I33, as the pivot is constantly being changed. The first one "I14" is ok...

    If someone could help me Id really appreciate it. Hope I was clear enough!
    Any other workarounds and solutions would be awesome!
    Thanks in advance!

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Return max/min value from array excluding "tota"l value in the column. (Pivot Table)

    Hi hitxrafa,

    Suggest you to upload a sample workbook with you expected results. Thanks.

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Return max/min value from array excluding "tota"l value in the column. (Pivot Table)

    just a shot in the dark here. seeing as how it's the negative numbers that are throwing everything off, and "reversing" max() and min() functions, could you change the sign on the numbers and make the neg's positive? that way, the max savings would in fact be the highest number
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1