Multiple Max/Min Values (XP SP-2)

Topic

New Reply

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.

Reply | Quote

Replies

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))=1000000,””,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.

Reply | Quote

Hans,

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

Thanks,

Reply | Quote

Hans,

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

Thanks,

Reply | Quote

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

See attached example

Andrew C

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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”.

Reply | Quote

An excellent suggestion.

Steve

Reply | Quote

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,

Reply | Quote

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,

Reply | Quote

An excellent suggestion.

Steve

Reply | Quote

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”.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

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

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

Steve

Reply | Quote

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.

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

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

Steve

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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

See attached example

Andrew C

Reply | Quote