Dating Range Equation
Pull Excel data from date range and average?
Spreadsheet has Names (:A) Departments (:B) Total Hours Worked (:C) total Qty produced (:D), Total Value produced (:E) and Date Worked (:F)
The idea is to set a start date and end date (without filters) and get the average of the units produced per hour. I know this can be done by duplicating the equation twice, once to pull the hours worked by employee, the other to pull the units produced…and then a formula on the Main spreadsheet page to get the right number in the right places.
The real issue lies in only pulling the data from a certain date range so we can view anything from, say, 1/1/09 – 1/31/09 and then put a new start date in and view data from 1/17/09-1/24/09…
If I’m understanding your question correctly, you want to be able to put in two dates in say G2 and H2 and use those values to pull a formula that will calculate an average only for those values where the Date Worked is between G2 and H2.
This would be much easier in a database program like MS Access. Excel really isn’t made for these kinds of relational queries. Here’s a workaround that should work, though.
G2 = start date
H2 = end date
G3 = “>=” & G2
H3 = “>” & H2
Answer = (SUMIF( E:E, G3, C:C) – SUMIF( E:E, H3, C:C)) / (SUMIF( E:E, G3, B:B) – SUMIF( E:E, H3, B:B))
This first sums the quantity produced on or after the start date, subtracts the quantity produced after the end date, then divides the result by the same algorithm for hours worked to give quantity per hour.
3 Things you Should Know about Swinging & Hooking Up – Daily Buzz Janie Lacy
