Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding variable range of numbers
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding variable range of numbers
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 enter =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... -- Always provide your feedback... "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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding variable range of numbers
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding variable range of numbers
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding variable range of numbers
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. "Sheeloo" wrote: 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 enter =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... -- Always provide your feedback... "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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding variable range of numbers
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding variable range of numbers
mwavra wrote:
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a variable into a range declaration | Excel Worksheet Functions | |||
Adding new variable | Excel Discussion (Misc queries) | |||
adding range of numbers to if statement with three criteria | Excel Worksheet Functions | |||
Adding a range of numbers associated with various dates | Excel Discussion (Misc queries) | |||
adding numbers in a specific range of values | Excel Discussion (Misc queries) |