Formula for data produced till time

Hi Nitil

Firstly, in column C, you've manually added the stock production figures (e.g. in cells C8 and C11 for product or worker named "a") but it's hard to tell which figures are for which product or worker. I've added conditional formatting to make that easier- if the item in column A matches cell G5 (in historic data), then the row from A:D will be shaded yellow (like G5:J5). You can change G5 (currently in red, bold) to whatever you like, provided the names in column A match. Likewise for the other two product or worker names.

To compare the currrent production rate compared to the historic rate, in column D I've used this formula (e.g.) in cell D5:

C5/(24*(B5-$B$1)/6*VLOOKUP($A5,$G$5:$J$7,2,FALSE))

1. C5 is the number at the time (added manually by your formula)
2. 24*(B5-$B$1) represents the number of hours of production that day, with B5 as the start time

So item 1 divided by item 2 is the rate per hour- this is divided by the historical average stock production rate, calculated as:

1. The average stock number (from column G)
2. Divided by 6, the number of hours of production (in column I but assumed to be constant)

The VLOOKUP formula VLOOKUP($A5,$ G$5:$J$7,2,FALSE) simply gets the historic daily stock from column G using the vlaue from column A as the lookup value. The FALSE requires an exact match.

Hope this is what you are looking for.