Finding dynamic maxima
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
|