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