In message of Wed, 19 Jun 2013 20:05:05 in
microsoft.public.excel.worksheet.functions, Claus Busch <claus_busch@t-
online.de writes
Hi Walter,
Am Wed, 19 Jun 2013 17:53:25 +0100 schrieb Walter Briscoe:
Both G and H seem to cause volatile recalculation of the sheet.
That conclusion is tentative.
try following function:
Function myMax(rngC As Range) As Double
Dim myRow As Long
Dim myCount As Integer
Dim myRange As Range
myRow = WorksheetFunction.Match(rngC, Range("B1:B3000"), 0)
myCount = WorksheetFunction.CountIf(Range("B2:B3000"), rngC)
Set myRange = Range(Cells(myRow, "E"), _
Cells(myRow + myCount - 1, "E"))
myMax = WorksheetFunction.Max(myRange)
End Function
In F2 write:
=myMax(B2)
and copy down
Regards
Claus Busch
Thanks Claus. That works and the result is not volatile.
I have taken a while to compose the following response.
I have been thinking, but have questions I can't answer.
I scrapped irrelevant data to form the following:
A B C D E F G H I
1 Job Section Need ExpNeed Key Group0 Group1 Group2 Group3
2 1263 5 4 4 4 4 4 4 4
3 1264 1 4 5 5 5 5 5 5
4 1264 2 4 5 5 5 5 5 5
5 1264 3 5 5 5 5 5 5 5
6 1264 4 5 5 5 5 5 5 5
7 1264 5 4 5 5 5 5 5 5
8 1363 1 3 3 3 3 3 3 3
I find myself liking named ranges and have added the following:
Name Refers to
Jobs =Sheet1!$A$1:$A$8
Keys =Sheet1!$C$2:$C$8
A) Column A (Job) is just data.
B) Column B (Section) is just data.
C) Column C (Need) is just data.
D) Column D (ExpNeed) contains the expected values for columns F:I.
E) Column E (Key) has the keys to calculate column F
E2 is =A2&B2 and is copied down.
F) Column F (Group0) has my original work with the named range Keys,
rather than explicitly using $C$2:$C$8. That seems clearer,
easier to modify and shorter.
It is a copy down of
=MAX(IF(ISERROR(MATCH(A2&1,Keys,0)),0,INDIRECT("$E "&(1+MATCH(A2&1,Keys,0)))
),IF(ISERROR(MATCH(A2&2,Keys,0)),0,INDIRECT("$E"&( 1+MATCH(A2&2,Keys,0)))),IF(I
SERROR(MATCH(A2&3,Keys,0)),0,INDIRECT("$E"&(1+MATC H(A2&3,Keys,0)))),IF(ISERROR
(MATCH(A2&4,Keys,0)),0,INDIRECT("$E"&(1+MATCH(A2&4 ,Keys,0)))),IF(ISERROR(MATCH
(A2&5,Keys,0)),0,INDIRECT("$E"&(1+MATCH(A2&5,Keys, 0)))),IF(ISERROR(MATCH(A2&6,
Keys,0)),0,INDIRECT("$E"&(1+MATCH(A2&6,Keys,0)))), IF(ISERROR(MATCH(A2&7,Keys,0
)),0,INDIRECT("$E"&(1+MATCH(A2&7,Keys,0)))),IF(ISE RROR(MATCH(A2&8,Keys,0)),0,I
NDIRECT("$E"&(1+MATCH(A2&8,Keys,0)))))
That formula is grotesquely long and I will happily scrap it,
when I have finished learning from it.
In my original posting, I was looking for
ALT+ENTER Start a new line in the same cell.
I tried that and found that it makes copying down unreasonably hard.
[A formula on n lines in a cell is pasted into n cells.
Paste Special gives a choice of pasting as Unicode Text or Text,
instead of a choice including Formulas. It is possible to avoid
pasting on n lines where the cells to be filled are already big
enough to hold all the data. I concluded it is too hard.;)]
G) Column G (Group1) is a copy down of
=IF(A2<A1,MAX(OFFSET(A2,,3,COUNTIF(Jobs,A2))),G1)
That formula is volatile because it uses OFFSET.
A volatile formula means workbook performance is catastrophic.
H) Column H (Group2) is a copy down of the array formula:
=MAX(IF($A$2:$A$3000=A2,$D$2:$D$3000))
[To enter it, type =MAX(IF($A$2:$A$3000=A2,$D$2:$D$3000))
in H2 and complete the cell with CTRL+Shift+Enter, not Enter
so the cell appears as
{=MAX(IF($A$2:$A$3000=A2,$D$2:$D$3000))}}
This formula is short and not volatile.
However, I do not understand it. ;(
My usual technique for dealing with complicated formulas is to
single step through them with Tools/Formula Auditing./Evaluate Formula.
The data is uncomfortably large to do that. However, trimming it to
=MAX(IF($A$2:$A$8=A2,$D$2:$D$8))
continues to work and I trust I will get to understanding.
I also failed to use named ranges in it. (N/A resulted.) ;(
I) Column I (Group3) is a copy down of =NeedsMax().
NeedsMax is my enhancement of your MyMax.
It uses Application.Caller rather than a parameter to get data.
It uses Debug.Assert to check data consistency.
I do not understand why, although my code works with
Set Job = Range(JobsCol & "$" & Application.Caller.Row),
Debug.Print Application.Caller.Row gets
Run-time error '424': Object required.
My code to check that the sections for a job are together is weak.
When it fails, it gives many Debug.Assert failures.
If I expected failures, I might use
<http://www.cpearson.com/Excel/IsArraySorted.aspx.
Anyway, here is my code:
Option Explicit
Function NeedsMax() As Double
' Job refers to a job number in column JobsCol
' NeedsCol contains needs for each job section.
' NeedsMax return the maximum need for all the sections in the job.
'
' The alternative worksheet formula is volatile.
' =IF(A2<A1,MAX(OFFSET(A2,,3,COUNTIF(Jobs,A2))),G1)
' I do not understand the alternative worksheet array formula.
' =MAX(IF($A$2:$A$3000=A2,$D$2:$D$3000))
' which should appear as
' {=MAX(IF($A$2:$A$3000=A2,$D$2:$D$3000))}
Const JobsCol As String = "$A" ' Jobs are in this column
Const NeedsCol As String = "D" ' Needs are in this column
Dim LastRow As Long ' Last row number containing data
Dim Job As Range ' Caller's row's job cell
Dim Jobs As Range ' All jobs
Dim FirstRow As Long ' Caller's row's job's first row
Dim JobCount As Integer ' Count of Jobs with caller's Job
Dim NeedsRange As Range ' Needs with caller's job number.
' Check data consistency
Debug.Assert Cells(1, JobsCol) = "Job"
Debug.Assert Cells(1, NeedsCol) = "Need"
' Note the range of jobs (adding title avoids offset by 1 calc's)
LastRow = ActiveSheet.UsedRange.Rows.Count
Set Jobs = Range(JobsCol & 1 & ":" & JobsCol & LastRow)
' I don't know why Debug.Print Application.Caller.Row
' gets Run-time error '424': Object required.
' Find Job on calling cell's row
Set Job = Range(JobsCol & "$" & Application.Caller.Row)
' Match first occurrence of Job.Value.
FirstRow = WorksheetFunction.Match(Job, Jobs, 0)
' Count matches of Job.value.
JobCount = WorksheetFunction.CountIf(Jobs, Job)
' Check relevant jobs are together.
Set NeedsRange = Range(JobsCol & FirstRow & _
":" & JobsCol & FirstRow + JobCount - 1)
Debug.Assert JobCount = WorksheetFunction.CountIf(NeedsRange, Job)
' Focus on cells containing relevant needs
Set NeedsRange = Range(NeedsCol & FirstRow & _
":" & NeedsCol & FirstRow + JobCount - 1)
' Get Maximum in that range
NeedsMax = WorksheetFunction.Max(NeedsRange)
End Function
In D2, I wrote:
=myMax(A2)
and copied down.
The module is now:
Option Explicit
Function NeedsMax() As Double
' Return the maximum need for all sections in the caller's job.
'
' The following formulae can be used instead.
' Each produces a volatile result.
' That causes a performance hit.
Const JobsCol As String = "$A" ' Jobs are in this column
Const NeedsCol As String = "C" ' Needs are in this column
Dim LastRow As Long ' Last row number containing data
Dim Job As Range ' Caller's row's job cell
Dim Jobs As Range ' All jobs
Dim FirstRow As Long ' Caller's row's job's first row
Dim JobCount As Integer ' Count of Jobs with caller's Job
Dim NeedsRange As Range ' Needs with caller's job number.
' Check data consistency
Debug.Assert Cells(1, JobsCol) = "Job"
Debug.Assert Cells(1, NeedsCol) = "Need"
' Note the range of jobs (adding title avoids offset by 1 calc's)
LastRow = ActiveSheet.UsedRange.Rows.Count
Set Jobs = Range(JobsCol & 1 & ":" & JobsCol & LastRow)
' I don't know why Debug.Print Application.Caller.Row
' gets Run-time error '424': Object required.
' Find Job on calling cell's row
Set Job = Range(JobsCol & "$" & Application.Caller.Row)
' Match first occurrence of Job.Value.
FirstRow = WorksheetFunction.Match(Job, Jobs, 0)
' Count matches of Job.value.
JobCount = WorksheetFunction.CountIf(Jobs, Job)
' Check relevant jobs are together.
Set NeedsRange = Range(JobsCol & FirstRow & _
":" & JobsCol & FirstRow + JobCount - 1)
Debug.Assert JobCount = WorksheetFunction.CountIf(NeedsRange, Job)
' Focus on cells containing relevant needs
Set NeedsRange = Range(NeedsCol & FirstRow & _
":" & NeedsCol & FirstRow + JobCount - 1)
' Get Maximum in that range
NeedsMax = WorksheetFunction.Max(NeedsRange)
End Function
--
Walter Briscoe