More formula help.....
 

Thanks to Bob for help with the sumproduct formula
This is what I have

Hi to all
This is my basic spreadsheet
C1 Production Start time
C2 Produstion Stop time
C3 Run time (C2-C1)
C4 Items per Hour
C5 Total production (C3*C4)
C6-C29 (24 hours of the day) I would like the items produced that hour
based on start time and stop time. What formula would work for me?

=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

Although my actual spreadsheet is a variation of above, I have recreated as posted and I can get the first hour to fill in if it is a whole hour. However the run is longer than an hour or a portion of an hour I only get a whole hours value in the starting hour.

C1=1:30 (Entered)
C2=5:00 (Entered)
C3=3.5 (Calculated)
C4=100 (Calculated)
C5=350 (Calculated)
C6:C29 =SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

I get 100 in C7 (2:00) and should be 50 and all other hours are empty.

Thanks again
Jan

More formula help.....
 

Jan,

Does this work

=SUMPRODUCT((C$1=TIME(ROW(C7)-6,0,0))*(C$1<TIME(ROW(C7)-5,0,0))*((TIME(ROW(
C8)-6,0,0)-C$1)*24))*C$4

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan" wrote in message
...
Thanks to Bob for help with the sumproduct formula
This is what I have

Hi to all
This is my basic spreadsheet
C1 Production Start time
C2 Produstion Stop time
C3 Run time (C2-C1)
C4 Items per Hour
C5 Total production (C3*C4)
C6-C29 (24 hours of the day) I would like the items produced that hour
based on start time and stop time. What formula would work for me?

=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

Although my actual spreadsheet is a variation of above, I have recreated
as posted and I can get the first hour to fill in if it is a whole hour.
However the run is longer than an hour or a portion of an hour I only get a
whole hours value in the starting hour.

C1=1:30 (Entered)
C2=5:00 (Entered)
C3=3.5 (Calculated)
C4=100 (Calculated)
C5=350 (Calculated)
C6:C29
=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

I get 100 in C7 (2:00) and should be 50 and all other hours are empty.

Thanks again
Jan

More formula help.....
 

in C7 put in the formula

=((IF(ROUND(MIN(C7+TIMEVALUE("01:00"),$C$2)-MAX(C7,$C$1),8)<=0,0,(MIN(C7+TIM
EVALUE("01:00"),$C$2)-MAX(C7,$C$1)))*24)/$C$3)*$C$4

then drag fill down.

--
Regards,
Tom Ogilvy

"Jan" wrote in message
...
Thanks to Bob for help with the sumproduct formula
This is what I have

Hi to all
This is my basic spreadsheet
C1 Production Start time
C2 Produstion Stop time
C3 Run time (C2-C1)
C4 Items per Hour
C5 Total production (C3*C4)
C6-C29 (24 hours of the day) I would like the items produced that hour
based on start time and stop time. What formula would work for me?

=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

Although my actual spreadsheet is a variation of above, I have recreated
as posted and I can get the first hour to fill in if it is a whole hour.
However the run is longer than an hour or a portion of an hour I only get a
whole hours value in the starting hour.

C1=1:30 (Entered)
C2=5:00 (Entered)
C3=3.5 (Calculated)
C4=100 (Calculated)
C5=350 (Calculated)
C6:C29
=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

I get 100 in C7 (2:00) and should be 50 and all other hours are empty.

Thanks again
Jan

More formula help.....
 

It doesn't for me. 50 in C7, zero in C8 to C29.

maybe you meant this
=SUMPRODUCT((C$2=TIME(ROW(C7)-6,0,0))*(C$1<TIME(ROW(C7)-5,0,0))*((TIME(ROW(
C8)-6,0,0)-C$1)*24))*C$4

but that seems to accumulate quantities and includes the 5 - 6 timeframe in
the test problem.
--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Jan,

Does this work


=SUMPRODUCT((C$1=TIME(ROW(C7)-6,0,0))*(C$1<TIME(ROW(C7)-5,0,0))*((TIME(ROW(
C8)-6,0,0)-C$1)*24))*C$4

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jan" wrote in message
...
Thanks to Bob for help with the sumproduct formula
This is what I have

Hi to all
This is my basic spreadsheet
C1 Production Start time
C2 Produstion Stop time
C3 Run time (C2-C1)
C4 Items per Hour
C5 Total production (C3*C4)
C6-C29 (24 hours of the day) I would like the items produced that hour
based on start time and stop time. What formula would work for me?

=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

Although my actual spreadsheet is a variation of above, I have recreated
as posted and I can get the first hour to fill in if it is a whole hour.
However the run is longer than an hour or a portion of an hour I only get
a
whole hours value in the starting hour.

C1=1:30 (Entered)
C2=5:00 (Entered)
C3=3.5 (Calculated)
C4=100 (Calculated)
C5=350 (Calculated)
C6:C29
=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

I get 100 in C7 (2:00) and should be 50 and all other hours are empty.

Thanks again
Jan

More formula help.....
 

Isn't that what the OP asked for?
I get 100 in C7 (2:00) and should be 50 and all other hours are empty.
although it doesn't makle a lot of sense to me, why not 0,50,100,100,100,0,
etc.

Bob

"Tom Ogilvy" wrote in message
...
It doesn't for me. 50 in C7, zero in C8 to C29.

maybe you meant this

=SUMPRODUCT((C$2=TIME(ROW(C7)-6,0,0))*(C$1<TIME(ROW(C7)-5,0,0))*((TIME(ROW(
C8)-6,0,0)-C$1)*24))*C$4

but that seems to accumulate quantities and includes the 5 - 6 timeframe
in
the test problem.
--
Regards,
Tom Ogilvy

More formula help.....
 

I though it should be 0,50,100,100,100,0

but formula gives 0,50,150,250,350,450,0

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...
Isn't that what the OP asked for?
I get 100 in C7 (2:00) and should be 50 and all other hours are
empty.
although it doesn't makle a lot of sense to me, why not
0,50,100,100,100,0,
etc.

Bob

"Tom Ogilvy" wrote in message
...
It doesn't for me. 50 in C7, zero in C8 to C29.

maybe you meant this


=SUMPRODUCT((C$2=TIME(ROW(C7)-6,0,0))*(C$1<TIME(ROW(C7)-5,0,0))*((TIME(ROW(
C8)-6,0,0)-C$1)*24))*C$4

but that seems to accumulate quantities and includes the 5 - 6 timeframe
in
the test problem.
--
Regards,
Tom Ogilvy

More formula help.....
 

correction, in C6 put

=MAX(0,((MIN(C6+TIMEVALUE("01:00"),$C$2)-MAX(C6,$C$1))*24/$C$3)*$C$5)

Then drag fill down

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
in C7 put in the formula


=((IF(ROUND(MIN(C7+TIMEVALUE("01:00"),$C$2)-MAX(C7,$C$1),8)<=0,0,(MIN(C7+TIM
EVALUE("01:00"),$C$2)-MAX(C7,$C$1)))*24)/$C$3)*$C$4

then drag fill down.

--
Regards,
Tom Ogilvy

"Jan" wrote in message
...
Thanks to Bob for help with the sumproduct formula
This is what I have

Hi to all
This is my basic spreadsheet
C1 Production Start time
C2 Produstion Stop time
C3 Run time (C2-C1)
C4 Items per Hour
C5 Total production (C3*C4)
C6-C29 (24 hours of the day) I would like the items produced that hour
based on start time and stop time. What formula would work for me?

=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

Although my actual spreadsheet is a variation of above, I have recreated
as posted and I can get the first hour to fill in if it is a whole hour.
However the run is longer than an hour or a portion of an hour I only get
a
whole hours value in the starting hour.

C1=1:30 (Entered)
C2=5:00 (Entered)
C3=3.5 (Calculated)
C4=100 (Calculated)
C5=350 (Calculated)
C6:C29
=SUMPRODUCT((C$1=TIME(ROW(C6)-6,0,0))*(C$1<TIME(ROW(C6)-5,0,0)))*C$4

I get 100 in C7 (2:00) and should be 50 and all other hours are empty.

Thanks again
Jan