Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
I have a list of contracts with daily volumes and dates which start and end
at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
In my math class your example would equate to 30 units per day average. So
you need to clarify how you got to an average of 1. "Cdn Gas Guy" wrote: I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
30 units per day for one day = 30 units. Divided by 30 days in November =
average 1 unit per day "JLGWhiz" wrote: In my math class your example would equate to 30 units per day average. So you need to clarify how you got to an average of 1. "Cdn Gas Guy" wrote: I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
30 units per day x 30 days = 900/30 = 30 units per day.
30 units per month @ 30 days in mo/30 = 1 unit per day. Ending date per contract - Starting Date per contract = total time in days Sum(Delivery by Contract)/total time in days = average units per day The terminology needs to match the expected outcome or you will get some erroneous answers. "Cdn Gas Guy" wrote: 30 units per day for one day = 30 units. Divided by 30 days in November = average 1 unit per day "JLGWhiz" wrote: In my math class your example would equate to 30 units per day average. So you need to clarify how you got to an average of 1. "Cdn Gas Guy" wrote: I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
Just to jump into this thread, I think JLGWhiz's comment centered around
this.... your original message said your contract started November 30th which means your contract only had one day in it before the month ended... 30 units per day divided by the one day in the month your contract was active for equals 30 units per day average. Rick "Cdn Gas Guy" wrote in message ... 30 units per day for one day = 30 units. Divided by 30 days in November = average 1 unit per day "JLGWhiz" wrote: In my math class your example would equate to 30 units per day average. So you need to clarify how you got to an average of 1. "Cdn Gas Guy" wrote: I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
Cdn Fas Guy,
My Assumptions: A1:C1 have labels, cells D1, E1, and so on to the right have dates like 11/1/2007, 12/1/2007, etc. Cell A2 has the start date for that row (and other start dates are below) Cell B2 has the end date for that row (and other end dates are below) Cell C2 has the daily units for that row (and other daily units are below) In cell D2, enter the formula (this is a long one, so remove the line breaks): =IF(DATE(YEAR(D$1),MONTH(D$1),1)$B2,0,IF(DATE(YEA R(D$1),MONTH(D$1)+1,0)<$A2,0,IF(AND(DATE(YEAR(D$1) ,MONTH(D$1)+1,0)<$B2,DATE(YEAR(D$1),MONTH(D$1),1) $A2),$C2*DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0)),IF(AN D($B2=DATE(YEAR(D$1),MONTH(D$1),1),$B2<=DATE(YEAR (D$1),MONTH(D$1)+1,0)),DAY($B2)*$C2,IF(AND($A2=DA TE(YEAR(D$1),MONTH(D$1),1),$A2<=DATE(YEAR(D$1),MON TH(D$1)+1,0)),(DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0))-DAY($A2)+1)*$C2)))))/DAY(DATE(YEAR(D$1),MONTH(D$1)+1,0)) Then copy this to the right and down as far as you need. HTH, Bernie MS Excel MVP "Cdn Gas Guy" <Cdn Gas wrote in message ... I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
I'm not 100% clear on your layout. Perhaps you could show the relevant
column letter designations with their heading text. Specifically, where are your Start and End dates listed at? Rick "Cdn Gas Guy" <Cdn Gas wrote in message ... I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
The solution Bernie noted correctly assumes what I obviously failed to
explain earlier. The only problem with Bernies solution is that a start and end date within a single month provides an incorrect answer. Bernies Correct Assumptions we A1:C1 have labels, cells D1, E1, and so on to the right have dates like 11/1/2007, 12/1/2007, etc. Cell A2 has the start date for that row (and other start dates are below) Cell B2 has the end date for that row (and other end dates are below) Cell C2 has the daily units for that row (and other daily units are below) Now if I had cells A2 and B2 contain Nov 15/07 and Nov 17/07 (3 days appart) the result should be 3 units. Bernies formula gives 17 units. "Rick Rothstein (MVP - VB)" wrote: I'm not 100% clear on your layout. Perhaps you could show the relevant column letter designations with their heading text. Specifically, where are your Start and End dates listed at? Rick "Cdn Gas Guy" <Cdn Gas wrote in message ... I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
Cdn Gas Guy,
OK, sorry about that. Insert two rows above row 1, so that you have your D1 date in D3. Then in D1, enter =DATE(YEAR(D$3),MONTH(D$3),1) and in D2, enter =DATE(YEAR(D$3),MONTH(D$3)+1,0) Then in D4, enter =IF(D$1$B4,0,IF(D$2<$A4,0,IF(AND(D$2<$B4,D$1$A4) ,DAY(D$2),IF(AND($A4=D$1,$B4<=D$2),$B4-$A4+1,IF(AND($B4=D$1,$B4<=D$2),DAY($B4),IF(AND($A 4=D$1,$A4<=D$2),(DAY(D$2)-DAY($A4)+1)))))))*$C4/DAY(D$2) (I changed to cell references because the formula got too long and was erroring out.) Then copy D1:D2 to the right, and D4 over and down as far as is needed. I think I have the IF clauses in the correct order... HTH, Bernie MS Excel MVP "Cdn Gas Guy" wrote in message ... The solution Bernie noted correctly assumes what I obviously failed to explain earlier. The only problem with Bernies solution is that a start and end date within a single month provides an incorrect answer. Bernies Correct Assumptions we A1:C1 have labels, cells D1, E1, and so on to the right have dates like 11/1/2007, 12/1/2007, etc. Cell A2 has the start date for that row (and other start dates are below) Cell B2 has the end date for that row (and other end dates are below) Cell C2 has the daily units for that row (and other daily units are below) Now if I had cells A2 and B2 contain Nov 15/07 and Nov 17/07 (3 days appart) the result should be 3 units. Bernies formula gives 17 units. "Rick Rothstein (MVP - VB)" wrote: I'm not 100% clear on your layout. Perhaps you could show the relevant column letter designations with their heading text. Specifically, where are your Start and End dates listed at? Rick "Cdn Gas Guy" <Cdn Gas wrote in message ... I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
Works great, thanks Bernie.
"Bernie Deitrick" wrote: Cdn Gas Guy, OK, sorry about that. Insert two rows above row 1, so that you have your D1 date in D3. Then in D1, enter =DATE(YEAR(D$3),MONTH(D$3),1) and in D2, enter =DATE(YEAR(D$3),MONTH(D$3)+1,0) Then in D4, enter =IF(D$1$B4,0,IF(D$2<$A4,0,IF(AND(D$2<$B4,D$1$A4) ,DAY(D$2),IF(AND($A4=D$1,$B4<=D$2),$B4-$A4+1,IF(AND($B4=D$1,$B4<=D$2),DAY($B4),IF(AND($A 4=D$1,$A4<=D$2),(DAY(D$2)-DAY($A4)+1)))))))*$C4/DAY(D$2) (I changed to cell references because the formula got too long and was erroring out.) Then copy D1:D2 to the right, and D4 over and down as far as is needed. I think I have the IF clauses in the correct order... HTH, Bernie MS Excel MVP "Cdn Gas Guy" wrote in message ... The solution Bernie noted correctly assumes what I obviously failed to explain earlier. The only problem with Bernies solution is that a start and end date within a single month provides an incorrect answer. Bernies Correct Assumptions we A1:C1 have labels, cells D1, E1, and so on to the right have dates like 11/1/2007, 12/1/2007, etc. Cell A2 has the start date for that row (and other start dates are below) Cell B2 has the end date for that row (and other end dates are below) Cell C2 has the daily units for that row (and other daily units are below) Now if I had cells A2 and B2 contain Nov 15/07 and Nov 17/07 (3 days appart) the result should be 3 units. Bernies formula gives 17 units. "Rick Rothstein (MVP - VB)" wrote: I'm not 100% clear on your layout. Perhaps you could show the relevant column letter designations with their heading text. Specifically, where are your Start and End dates listed at? Rick "Cdn Gas Guy" <Cdn Gas wrote in message ... I have a list of contracts with daily volumes and dates which start and end at various points over months/years organized in rows. Next to the this I have columns for every calendar month moving years into the future. What I need to do is have a formula to return the average contract volume applicable to every given calendar month across the months. ie. If the contract was for 30 units per day and the contract date started Nov 30/07 then the average daily volume for Nov/07 would be 1 unit. Any help will be greatly appreciated. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning data from within a date range
Whew! Thanks for letting me know ;-)
Bernie Works great, thanks Bernie. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for returning date entered by date on another worksheet | Excel Worksheet Functions | |||
Look up data for date range | Excel Worksheet Functions | |||
How to sum a data range thru and after a cut off date | Excel Programming | |||
Returning value from a range of data | Excel Discussion (Misc queries) | |||
Finding data in a Range / Returning Cell Location | Excel Programming |