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