Adding variable range of numbers

[mwavra]

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?

 

[Sheeloo]

Here is one way...
Assuming you have your data in Sheet1 with header row
1. In Sheet 2 A1 enter
=Sheet1!A1 and copy down till end of your data set...
This will give you the Part Nos
2. In Sheet2 C2 ente
=SUM(Sheet1!C2:INDIRECT("Sheet1!"&CHAR(Sheet1!$B2+65+COLUMN(Sheet1!A2))&ROW()))
and copy to U2
This will give you running total for no. of days equal to the value in
Sheet1 B1
(Of course, if you have, say 5 in B2, then last four totals will be partial
for 4,3,2 and 1 day)
3. In Sheet2 B2 enter
=MAX(C2:U2)
and copy down till end of your data set
This will give you the Maximum as required...

Test this out and let me know...

 

[Glenn]

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.

 

[Glenn]

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.

 

[mwavra]

Thank you -- this worked! I got a couple good solutions -- I'm going to try
the one Glenn suggested and don't have to use 2 worksheets.

 

[mwavra]

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]

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.

Glad I could help, although mostly it was just knowing that MMULT was the key to
the answer and then searching for evidence that someone else had already done
the work!