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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Returning data from within a date range
 

Whew! Thanks for letting me know ;-)

Bernie

Works great, thanks Bernie.