Thank you, Glenn! The MMULT was just the ticket. My thanks for Harlan Grove
and Leo Heuser also. I like the formula without having to use an extra
worksheet -- much cleaner. You are a lifesaver.

"Glenn" wrote:

Glenn wrote:
mwavra wrote:
I'm not even sure if this is possible, but I've thought about it a
while, cruised through a lot of the posts, and thought I'd go to the
experts and see if they can help.

Part No Leadtime Monday Tuesday Wednesday Thursday Friday
11154001 5 2000 2530 2020 1860 2640
11154002 4 3200 2530 2020 1860 2640
11154003 3 5000 2530 2020 1860 2640
11154004 2 1700 2530 2020 1860 2640
11154005 1 2250 2530 2020 1860 2640

The columns go out for 4 weeks - Monday thru Friday. Based on the lead
time, I need to find the highest demand within the 4-week time frame.
So, if the lead time is 5, I need to add Monday thru Friday, Tuesday
thru Monday, Wednesday thru Tuesday, etc., and find the highest total
demand for that item. If I change the lead time from a 5, to a 4, it
would need to recalculate and give the highest demand over a 4-day
period.

Can anyone help?


Insert two columns between "Leadtime" and "Monday". I will assume going
forward that those are columns C and D and that you have headers in row
1 and data starting in row 2.

Add a blank worksheet and call it "Totals". Put the following in cell
E2 of worksheet "Totals":

=SUM(INDIRECT("SheetName!R"&ROW()&"C"&COLUMN()&":R "&ROW()&"C"&COLUMN()+SheetName!$B2-1,FALSE))


Replace "SheetName" twice with the name of your original worksheet.
Fill this right to column X and down as many rows as necessary to match
the number or parts on your original worksheet.


On your original worksheet, put the following in C2:

=MAX(Totals!E2:X2)


and this in D2 and format as date:

=INDEX($E$1:$X$1,1,MATCH(C2,Totals!E2:X2,0))

assuming you would also like to know the date of the maximum value.

Copy both formulas down to the end of your data.


I did find a solution that doesn't require another worksheet. I knew that MMULT
could do it, but I don't understand it well enough to figure it out on my own.
I searched the archives of this newsgroup and found it. Thanks to Harlan Grove
and Leo Heuser.


C2=MAX(MMULT(E2:X2,--(ABS(TRANSPOSE(COLUMN(E2:X2))-COLUMN(OFFSET(E2:X2,0,0,1,COLUMNS(E2:X2)-B2+1))-(B2-1)/2)<B2/2)))

D2=INDEX($E$1:$X$1,1,MATCH(C2,MMULT(E2:X2,--(ABS(TRANSPOSE(COLUMN(E2:X2))-COLUMN(OFFSET(E2:X2,0,0,1,COLUMNS(E2:X2)-B2+1))-(B2-1)/2)<B2/2)),0))

Both of these are array formulas, so use CTRL+SHIFT+ENTER.