More formula help.....

J

Jan

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
 
B

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)

Jan said:
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.
 
T

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

Jan said:
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.
 
T

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.
 
B

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
 
T

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

Bob Phillips said:
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

Tom Ogilvy said:
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(
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top