More formula help.....

[Guest]

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

 

[Bob Phillips]

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)

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

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.

 

[Tom Ogilvy]

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

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

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.

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

Isn't that what the OP asked for?although it doesn't makle a lot of sense to me, why not 0,50,100,100,100,0,
etc.

Bob

 

[Tom Ogilvy]

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

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

--
Regards,
Tom Ogilvy

Isn't that what the OP asked for? empty.
although it doesn't makle a lot of sense to me, why not 0,50,100,100,100,0,
etc.

Bob

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(

 

[Tom Ogilvy]

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