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,I NDIRECT("$D"&(1+MATCH($A262&1,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&2,$C$2:$C$2899,0)),0,INDIRE CT("$D"&(1+MATCH($A262&2,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&3,$C$2:$C$2899,0)),0,INDIRE CT("$D"&(1+MATCH($A262&3,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&4,$C$2:$C$2899,0)),0,INDIRE CT("$D"&(1+MATCH($A262&4,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&5,$C$2:$C$2899,0)),0,INDIRE CT("$D"&(1+MATCH($A262&5,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&6,$C$2:$C$2899,0)),0,INDIRE CT("$D"&(1+MATCH($A262&6,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&7,$C$2:$C$2899,0)),0,INDIRE CT("$D"&(1+MATCH($A262&7,$C$2:$C$2899,0)))),
IF(ISERROR(MATCH($A262&8,$C$2:$C$2899,0)),0,INDIRE CT("$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,I NDIRECT("$D"&(1+MATCH($A262&1,$C$2:$C$2899,0)))),I F(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.
--
Walter Briscoe

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

In message of Wed, 19 Jun 2013 12:03:54
in microsoft.public.excel.worksheet.functions, Claus Busch
writes
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

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

Values are read from D: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 is valid.
is obviously not. ;)
Would you like a confidential look at the data by email?

<news://microsoft.public.excel.worksheet.functions should get your
conclusions, so other readers can learn from your knowledge.
--
Walter Briscoe

Hi Walter,

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

Values are read from D: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,B 2))),F1)
and copy down


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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,B 2))),F1)

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Walter,

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

Values are read from D: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=9378A...121822A3%21326
for the workbook "MaxByGroup"


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

In message of Wed, 19 Jun 2013 15:07:37
in microsoft.public.excel.worksheet.functions, Claus Busch
writes
Hi Walter,

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

Values are read from D: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=9378A...121822A3%21326
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,B 2))),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.
--
Walter Briscoe

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Sun, 23 Jun 2013 09:53:23 +0100, Walter Briscoe wrote:

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.