Finding dynamic maxima

[Walter Briscoe]

I have some calculations in a sheet, which make me shudder.

I am hoping for suggestions of simplification from here.

I excerpt from my data
A B C D E
1 Job Section 0Key Need MaxNeed
....
262 1263 5 12635 4 4
263 1264 1 12641 4 5
264 1264 2 12642 4 5
265 1264 3 12643 5 5
266 1264 4 12644 5 5
267 1264 5 12645 4 5
268 1363 1 13631 3 3
....

The data is in ascending order of Job and ascending order of Section.
Each Job consists of 1 to 8 Sections.
0Key data have formulae. e.g. C262 is =A262&B262 so MATCH can be used.
Each Need is a number between 1 and 9.
MaxNeed for all Sections in a Job is the maximum Need for those
sections.
I have a truly horrible formula for MaxNeed.
e.g. E262 is
=MAX(IF(ISERROR(MATCH($A262&1,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&1,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&2,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&2,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&3,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&3,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&4,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&4,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&5,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&5,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&6,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&6,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&7,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&7,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&8,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&8,$C$2:$C$2899,0)))))

The formula is the maximum of 8 values.
The first value is the need for the first section.
....
The eighth value is the need for the eighth section.
If a particular section is not there, its need is calculated as 0.

I don't bother having a different pattern for the first section, to
allow for each job having at least one section.
I don't bother either nesting logic to allow for the fact that the
eighth section can only exist if the seventh section exists, etc.

I have split that formula over 8 lines.

In my Excel 2003 fx field, the formula is wrapped as follows:
=MAX(IF(ISERROR(MATCH($A262&1,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&1,$C$2:$C$2899,0)))),IF(ISERROR(MATCH(
$A262&2,$C$2:$C$2899,0)),0,INDIRECT("$D"&(1+MATCH($A262&2,$C$2:$C$2899,0)))),IF(ISERROR(MATCH($A262&3,$C$2:$C$2899,0)),0,
....
MATCH($A262&8,$C$2:$C$2899,0)))))

Is there any way of controlling the presentation of the formula, so that
the structure is more apparent?

The worst feature of the formula is that INDIRECT is used.
Using INDIRECT causes every change to the sheet to cause recalculation
of all cells.
Using INDIRECT also means that I can't easily switch to R1C1
presentation.
Using "$D" means I can't move Need to another column.
I suppose I could write a UDF called GetNeed, taking values of Job &
Section as parameters.

I would also like to shorten the formula.
A small shortening would be achieved by using a short name for
$C$2:$C$2899.
The formula is currently 725 bytes long.

I hope this long explanation of a problem is of interest. I look forward
to constructive comments and will happily answer any questions, where I
have not been clear.

 

[Claus Busch]

Hi Walter,

Am Wed, 19 Jun 2013 09:07:29 +0100 schrieb Walter Briscoe:

I have some calculations in a sheet, which make me shudder.

I am hoping for suggestions of simplification from here.

I excerpt from my data
A B C D E
1 Job Section 0Key Need MaxNeed
...
262 1263 5 12635 4 4
263 1264 1 12641 4 5
264 1264 2 12642 4 5
265 1264 3 12643 5 5
266 1264 4 12644 5 5
267 1264 5 12645 4 5
268 1363 1 13631 3 3

I don't know, if I understand you correctly.
You need the MAX of Need for each job and each section?
Then try:
=MAX(IF($D$2:$D$100=D2,$E$2:$E$100))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch

 

[Walter Briscoe]

In message <[email protected]> of Wed, 19 Jun 2013 12:03:54
in microsoft.public.excel.worksheet.functions, Claus Busch

Hi Walter,

Am Wed, 19 Jun 2013 09:07:29 +0100 schrieb Walter Briscoe:


I don't know, if I understand you correctly.
You need the MAX of Need for each job and each section?
Then try:
=MAX(IF($D$2:$D$100=D2,$E$2:$E$100))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch

Thanks Claus for the prompt response.
I regret that I did not succeed in explaining my need.

Values are read from D and written to E:E.
E263:E267 are each effectively set to =MAX($D$263:$D$267).

The MAX is applied to between 1 and 8 cells.
In this case the number of cells is 5.

I calculate =MAX($E$263, $E$264, $E$265, $E$266, $E$267, 0, 0, 0).

I hope that is clearer.

I infer mailto://[email protected] is valid.
mailto://[email protected] is obviously not.
Would you like a confidential look at the data by email?

<should get your
conclusions, so other readers can learn from your knowledge.

 

[Claus Busch]

Hi Walter,

Am Wed, 19 Jun 2013 13:12:16 +0100 schrieb Walter Briscoe:

Values are read from D and written to E:E.
E263:E267 are each effectively set to =MAX($D$263:$D$267).

your Need in column E. Then in F2:
=IF(B2<>B1,MAX(OFFSET(B2,,4,COUNTIF($B$1:$B$3000,B2))),F1)
and copy down


Regards
Claus Busch

 

[Claus Busch]

Hi Walter,

Am Wed, 19 Jun 2013 14:27:57 +0200 schrieb Claus Busch:

=IF(B2<>B1,MAX(OFFSET(B2,,4,COUNTIF($B$1:$B$3000,B2))),F1)

sorry above there is a typo. Try:
=IF(B2<>B1,MAX(OFFSET(B2,,3,COUNTIF($B$1:$B$3000,B2))),F1)


Regards
Claus Busch

 

[Claus Busch]

Hi Walter,

Am Wed, 19 Jun 2013 13:12:16 +0100 schrieb Walter Briscoe:

Values are read from D and written to E:E.
E263:E267 are each effectively set to =MAX($D$263:$D$267).

The MAX is applied to between 1 and 8 cells.
In this case the number of cells is 5.

please have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "MaxByGroup"


Regards
Claus Busch

 

[Walter Briscoe]

In message <[email protected]> of Wed, 19 Jun 2013 15:07:37
in microsoft.public.excel.worksheet.functions, Claus Busch

Hi Walter,

Am Wed, 19 Jun 2013 13:12:16 +0100 schrieb Walter Briscoe:


please have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "MaxByGroup"


Regards
Claus Busch

I did and am seriously impressed.
That file, which you obviously created to deal with my problem, contains
the following values:
A B C D E F G H
1 No Job Section 0Key Need Expected Max Need Max Need1 Max Need2
2 262 1263 5 12635 4 4 4 4
3 263 1264 1 12641 4 5 5 5
4 264 1264 2 12642 4 5 5 5
5 265 1264 3 12643 5 5 5 5
6 266 1264 4 12644 5 5 5 5
7 267 1264 5 12645 4 5 5 5
8 268 1363 1 13631 3 3 3 3

F contains my calculated values - using my naive, verbose formula.
=IF(B2<>B1,MAX(OFFSET(B2,,3,COUNTIF($B$1:$B$3000,B2))),G1)
is copied down from G2.
{=MAX(IF($B$2:$B$3000=B2,$E$2:$E$3000))} is an array formula copied down
from H2.

Both G and H seem to cause volatile recalculation of the sheet.
That conclusion is tentative.

 

[Claus Busch]

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

 

[Claus Busch]

Hi Walter,

Am Sun, 23 Jun 2013 09:53:23 +0100 schrieb Walter Briscoe:

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

if you make your names dynamic you don't need to calculate LastRow in
your function.

H) Column H (Group2) is a copy down of the array formula:
=MAX(IF($A$2:$A$3000=A2,$D$2:$D$3000))

You can use it like this:
=MAX(IF(Jobs=A2,Keys))
So you enter it with CTRL+Shift+Enter it is an array formula.
E.g. for 1264 in A3 it will give for Jobs:
False, False, True, True, True, True, True, Falseand so it takes for
Keys only the values with the corresponding True. And Max will calculate
the maximum of this values.

For your function you don't have to declare Jobs because you have a
named range and can use it. LastRow also is not needed. so you can
shorten your function to:

Function NeedsMax() As Double
Const NeedsCol As Integer = 3 ' Needs are in this column
Dim Job As Range ' Caller's row's job cell
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.

Set Job = Cells(Application.Caller.Row, Range("Jobs").Column)
FirstRow = WorksheetFunction.Match(Job, Range("Jobs"), 0)
JobCount = WorksheetFunction.CountIf(Range("Jobs"), Job)

Set NeedsRange = Range(Cells(FirstRow, NeedsCol), _
Cells(FirstRow + JobCount - 1, NeedsCol))

' Get Maximum in that range
NeedsMax = WorksheetFunction.Max(NeedsRange)
End Function


Application.caller.row is the row the calls the function from the
worksheet. That will fail in VBA to DEBUG.PRINT


Regards
Claus Busch

 

[Ron Rosenfeld]

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

Since you like the named ranges, you should be aware you can also set these up to be dynamic, so you don't have to redefine them each time you add a row of data:

Jobs =OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A))
Keys =OFFSET(Job,0,4)

I note that your "key" column in one example is Col E, and in another example is Col C, so you may need to change the ColumnOffset for the definition appropriately.
Also, note that the definitions will be volatile -- this may or may not be a problem for you.