Filters Qty
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.
BBGRAFX
LEARN MORE WITH THESE RESOURCES!
|
|
K250 Reusable Filter for Chemex Coffee Makers $29.99 The K250 filter for Chemex is a reusable coffee filter made from stainless steel mesh. It gets rid of the paper waste (and papery taste) of expensive disposable coffee filters. With proper care, the K250 filter will last for hundreds of brews, easily saving you money on your initial investment. The K250 filter is backed by Kaffeologie’s Simple Guarantee: if you’re not satisfied with your K250, yo… |
|
|
K250 Reusable Filter for Hario V60 Size 02 Coffee Dripper $24.99 The K250 filter for Hario V60 is a reusable coffee filter made from stainless steel mesh. It gets rid of the paper waste (and papery taste) of expensive disposable coffee filters. With proper care, the K250 filter will last for hundreds of brews, easily saving you money on your initial investment. The K250 filter is backed by Kaffeologie’s Simple Guarantee: if you’re not satisfied with your K250,… |
|
|
Fits Omnifilter CB3 .05 micron carbon filters Qty of 2 … |
|
|
Honeywell/Kaz Home Environme Humid Clean Cartridge Pc-1 Humidifier Chemical & Accessories $2.55 Protec, Continuous Humidifier Cleaning Cartridge, Continuous Protection Against Bacteria, Mold, Fungi & Algae In Your Humidifier, Fresher, Cleaner Mist, Humidifier Stays Cleaner, Longer, Easy To Use, Simply Drop Cartridge Into The Tank, Lasts 30 To 40 Fillings, Safe, Non-Toxic & Odor Free, Works In All Humidifiers, Extends Filter Life…. |
|
|
ASE30 Safe Escape Fire and Smoke Hood w/Soft Case – Qty 1 $75.95 FIRE ESCAPE SMOKE HOOD DETECTOR RESPIRATOR Protection from smoke inhalation and many other toxic gases means wearing with this single use respirator emergency smoke hood with a soft storage case. Use when a fire breaks out in a high rise office building, apartment building, a home or elsewhere. Evacuate wearing the Safe Escape smoke and fire safety hood guaranteed to provide up to 30 minutes of br… |
LEARN MORE WITH THESE RESOURCES!
|
|
APC 1U Blanking Panel Kit (Qty 2) Black AR8108BLK $31.62 APC 1U Blanking Panel Kit (Qty 2) Black AR8108BLK |
|
|
Ventilation Filters $229 Our removable, dishwasher-safe mesh filters are the perfect complement to the Epicure 18″ and 12″ high commercial-style hoods. |
|
|
42 CleanSense Charcoal Filters $36.66 42″ CleanSense(TM) Charcoal Filters> |
|
|
Refrigerator Filters $80.69 Dacor Built-In Refrigerator Water Filters protect your family from waterborne contaminants such a Lead, Giardi, Cryptosporidium, Atrazine, Lindane, 2,4-D, Asbestos and more. Replace your Dacor Built-In Refrigerator Water Filter every six months or if your |
|
|
Sony (VF37NKB) Lenses & Filters $39.66 Sony (VF37NKB) Lenses & Filters |
|
|
DHZ3052UC Replacement Charcoal Filters $108 Replacement Charcoal Filters for 30 Inch & 36 Inch DPH Series |
|
|
XORFSQR REPLACEMENT RECIRCULATING FILTERS $49 SQUARE RECIRCULATING FILTERS (2PK) FITS XOV, XOJ, XOM, XOMI |
|
|
4396020Bulk – Close Elbow, Qty 24 $104.92 [4396020BULK] |
Related posts:
