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

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

2. ## 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. ## 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. ## Re: Multiple Max/Min Values (XP SP-2)

Hans,

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

Thanks,

5. ## 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. ## 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. ## 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. ## Re: Multiple Max/Min Values (XP SP-2)

An excellent suggestion.

Steve

9. ## 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. ## 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. ## 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. ## 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. ## 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 B had a pivot table off A
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. ## 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
•