Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Getting EOY Time
Here's my Problem:
I have monthly sheets that keep tabs of production time for various colors of widgets. I have 12 sheets one for each month. Each Identical to the other. I have an EOY (End of Year) sheet, also identical which I want to use not only for EOY but also to check how we are doing YTD. Keeping tabs on columns of widgets is easy summing thru the cells on each workesheet =sum(Jan:Dec!d4). However getting the running clock total is not as easy. I don't want to do =average() because that would be averageing averages. I can get at the # I want by putting the month out on a flat linear sheet and using (b3*c3)+(d3*e3)+(f3*)+etc.../Total widgets. In this example the B3 would be # of widgets and c3 would be time say for january, and so on... How can I do this on the EOY sheet without writing a massive formula. I hope this is clear |
#2
|
|||
|
|||
How about
=INDIRECT("Jan:"&TEXT(TODAY(),"mmm")&"!D4") -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Here's my Problem: I have monthly sheets that keep tabs of production time for various colors of widgets. I have 12 sheets one for each month. Each Identical to the other. I have an EOY (End of Year) sheet, also identical which I want to use not only for EOY but also to check how we are doing YTD. Keeping tabs on columns of widgets is easy summing thru the cells on each workesheet =sum(Jan:Dec!d4). However getting the running clock total is not as easy. I don't want to do =average() because that would be averageing averages. I can get at the # I want by putting the month out on a flat linear sheet and using (b3*c3)+(d3*e3)+(f3*)+etc.../Total widgets. In this example the B3 would be # of widgets and c3 would be time say for january, and so on... How can I do this on the EOY sheet without writing a massive formula. I hope this is clear |
#3
|
|||
|
|||
Thanks for the prompt response, but I am looking for this running total
to be in a single cell for all 12 months of the year. This cell is next to a cell that to doing a SUM of all widgets YTD. So say in Jan you have 3 (D5)widgets at 3:25 (E5), and in Feb you have 10 (D6) widgets at 2:15 (E6). If you average you get 2:50. However the correct answer is 2:31. D7 equals the total of 13 widgets. (see formula below). The key to this is understanding that these entries are in the same location on individual monthly Sheets Jan-Dec and I am trying to capture it on an EOY sheet that is identical to the monthly sheets. =((D5*E5)+(D6*E6))/D7 <==This works on a flat file |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mulitiplying time | Excel Worksheet Functions | |||
Calculating tvl time in Excel 2000 Rev.9.2720 | Excel Worksheet Functions | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |