Help with calculating time
See your other post where I included a screencap.
In my test file I'm getting the *correct* results:
Should be:
55 (5+10+10+30)
65 (10+30+25)
140 (5+10+10+30+25+60)
--
Biff
Microsoft Excel MVP
"Cam" wrote in message
...
Valko,
I got the results, but the calculation is off little. The calculated cell
are in column G of sheet1. The last info is what it should be calculated
values. For example, sheet1 row 2 should be equal 55 cause it see start
(seq)
to stop (seq) is 100 to 400 so it should sum all value from row 2 to 5
(5+10+10+30) in column D from Ref sheet.
Sheet1 - Data sheet
A B C D E F G
ID date part# side start stop Cal Time Should be:
1 1/8/08 1360-1 1st 100 400 50 55 (5+10+10+30)
2 1/10/08 1360-1 1st 300 500 125 65 (10+30+25)
3 1/10/08 1360-1 1st 100 600 135 140
(5+10+10+30+25+60)
Ref - Ref sheet
A B C D
part# side seq time
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 25
1360-1 1st 600 60
1560-3 2nd 100 10
"T. Valko" wrote:
You'll have to post a sample of data and show where the formula is
returning
a 0.
--
Biff
Microsoft Excel MVP
"Cam" wrote in message
...
Hi Valko,
I tried the formula you suggested, but it returns "0" where it
shouldn't
be.
"T. Valko" wrote:
Try this:
With this data on Sheet2 in the range A2:D8
1360-1...1st...100...5
1360-1...1st...200...10
1360-1...1st...300...10
1360-1...1st...400...30
1360-1...1st...500...15
1360-1...1st...600...60
1560-3...2nd...100...10
With this data on Sheet3 in the range A2:F2
001...1/8/08...1360-1...1st...100...500
Enter this formula on Sheet3 G2:
=SUMPRODUCT(--(Sheet2!A$2:A$8=C2),--(Sheet2!B$2:B$8=D2),--(Sheet2!C$2:C$8=E2),--(Sheet2!C$2:C$8<=F2),Sheet2!D$2:D$8)
--
Biff
Microsoft Excel MVP
"Cam" wrote in message
...
Hello,
I have an Excel to track performance. Sheet1 is performance table
where
user
input their ID, date, part#, side, start sequence & stop sequence
daily.
Then
on a reference sheet2 with field: part#, side, sequence#, time
(calculated).
What I would like to calculate is look at the start & stop sequence
based
on
the part# & side then calculate how long (time) the operator spend
on
the
job
(part#). Thanks
Example:
Sheet1 - User input data
ID date part# side start seq stop seq
001 1/8/08 1360-1 1st 100 500
002 1/10/08 1560-3 2nd 600 1000
Sheet2 - Reference
part# side start seq time (min)
1360-1 1st 100 5
1360-1 1st 200 10
1360-1 1st 300 10
1360-1 1st 400 30
1360-1 1st 500 15
1360-1 1st 600 60
1560-3 2nd 100 10
..........
Result
Sheet3
ID date part# side start seq stop seq Total
time
(calculated)
001 1/8/08 1360-1 1st 100 500 70
How should i go about linking the two sheets so it will calculate
the
time
based on when he start the sequence to when he stop the sequence?
Thanks
|