Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple Max/Min Values (XP SP-2)

    I was wondering if any of you wizards out there might have a suggestion as to how to do this (sample workbook attached):

    I have a worksheet with several columns. Including Project Month (first date date of project initiation month formatted to display as MMM YYYY) and Days to Complete Project (number of work days between project start date and completion date). The number of projects in any given month, may vary, dramatically.
    A second worksheet (both worksheets combined in attached sample) contains several summary columns of project statistics. It includes, amongst others, columns for Month (MMM YYYY), Minimum Completion Days, and Maximum Completion days (each row is a single month).
    I need a simple method of determining the minimum and maximum completion days for each month when I have no idea how many rows are included in that month. This would be easy if there was a MIN and MAX function that worked like SUMIF.

    Thanks for your help, in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    The following horrible formula in G2 will compute the minimum for January:

    =IF(MIN(IF($A$2:$A$120=F2,$C$2:$C$120,1000000))=10 00000,"",MIN(IF($A$2:$A$120=F2,$C$2:$C$120,1000000 )))

    It is an array formula, it must be confirmed with Ctrl+Shift+Enter. It can be filled down to G13, and a similar formula with MAX instead of MIN and -1000000 instead of 1000000 can be used in H2:H13. The 1000000 and -1000000 are arbitrary values greater then, respectively smaller than any value in C2:C120.

    See attached version.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    You could use a Pivot Table as an alternative to array formulae.

    See attached example

    Andrew C

  4. #4
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    Hans,

    You are right, it looks horrible. But it sure works beautifully.

    Thanks,

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    Andrew,

    I've never used pivot tables, before, since I'm not familiar with their purpose. However, this gives me something to think about and study as an alternative to approaches I am familiar with.

    Thank you,

  6. #6
    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

    Re: Multiple Max/Min Values (XP SP-2)

    If you are going to do a lot of summaries, in my opinion, a pivot table makes much more sense.

    Arrays can be very calculation dependent and they can make the spreadsheet sluggish if you have a lot of them.

    The downside of pivot tables is that they are not live (you have to "refresh" to update them) where the array formulas are live.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    With the data being on one worksheet and the summary on a second worksheet, you could easily refresh the pivot table through a macro when the summary sheet is activated, making it somewhat "live".

  8. #8
    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

    Re: Multiple Max/Min Values (XP SP-2)

    An excellent suggestion.

    Steve

  9. #9
    Star Lounger
    Join Date
    Feb 2003
    Location
    Bend, Oregon
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    Since I do a lot of summary sheets in my work, these suggestions are very helpful. I've begun learning about pivot tables and will probably begin using them very soon (especially, if I can find ways to update them automatically).

    Thanks to all of you,

  10. #10
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    It is relatively simple to make pivot table refreshes live. An event procedure such as the following, works very well when pivot tables are stored on separate sheets from the data.

    Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    For i = 1 To ActiveWorkbook.PivotCaches.Count
    On Error Resume Next
    ActiveWorkbook.PivotCaches(i).Refresh
    Next i
    End Sub

  11. #11
    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

    Re: Multiple Max/Min Values (XP SP-2)

    If you have a lot of pivot tables, to save some time is change only the ones on the sheet that is active. This will refresh only the pivot tables on the sheet you activate, instead of updating all pivot tables whenever a sheet change is made.

    <pre>Option Explicit
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim pt As PivotTable
    If Sh.Type = xlWorksheet Then
    For Each pt In Sh.PivotTables
    pt.RefreshTable
    Next
    End If
    End Sub</pre>


    Like Andrew's code, this code should be placed in the thisworkbook object, not a module.

    Steve

  12. #12
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    I like your code so much better than mine
    I'd always planned to get around to coding it that way but never did because what I'd coded in my early days worked as it was.
    Thanks - Andrew

  13. #13
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple Max/Min Values (XP SP-2)

    I thought about it some more

    I had a reason to use deactivate, and refresh all caches, rather than activate and refresh the workbook - it works more generally in the case I had

    Sheet A had the data
    Sheet B had a pivot table off A
    Sheet C had more data
    Sheet D had a pivot table of C
    Sheet E had a summary extract of pivot data from Sheets B and D

    In the instance where I'd updated data in Sheet A and clicked straight to sheet E, no pivot tables were involved on the sheets I was accessing.

  14. #14
    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

    Re: Multiple Max/Min Values (XP SP-2)

    Yes, in cases like you outline (other cells are dependent on pivots) updating all the pivots is prefered.

    If all the pivot table is based on data in only 1 sheet, you could save time, by only updating all the pivot tables when the datasheet is deactivated (a worksheet_Deactivate in that sheet object) instead of running in when any sheet is deactivated (workbook_Sheetdeactivate) . Again that would depend on your needs.

    The method you outline will ensure the data is updated, the only concern is how sluggish changing sheets will be...
    Steve

Posting Permissions

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