Return the number of weeks that a specific criteria is met (without double-counting)
Our take
I need a method to calculate the number of weeks that specific criteria are met, without accidentally double-counting if that criteria is met twice in the same week, and repeat that process a few hundred times dependent on a key value that I can look up.
The goal here is to essentially repeat, for each client number on my list, the number of unique weeks where any employee worked with them for more than 40 hours. Each client appears only one time on the final list, regardless of the number of employees that have worked with them. I would do this process manually, but I'd need to do it about 750 times and each client has vastly different scheduling. As well, some employees work with multiple distinct clients, and more than one employee may work past 40 hours in a week with the same client, due to a number of unexpected circumstances. Additionally, I'll have to expand on this method eventually to further narrow this down when given additional follow up criteria.
I can't disclose any of the original data. I've instead provided my own mock-up data which I roughly recreated in Google Sheets - the original data is in Excel, hence my choice of venue for this post. In the sample data, I use whole numbers. The real data will include decimals. Since I'm looking for anything over 40 hours anyway, this shouldn't affect the final output.
I've tried to make it easier on myself by creating a table that looks something like this:
The weeks are already specified, and go from week 1 to week 53. The table I'm reporting the values on is currently on a separate page, like so:
In the real data set, these are both tables, so I can make use of table references. I'm also not against using other methods like a pivot table, which I can then just paste into the final report, or more advanced functions if there's just nothing else to handle what we're trying for. I also am in possession of the original time card data that I used to create "Table 1", the hours per employee per client per week.
I've tried to do Xlookup, Countif and Countifs to make this work, but I'm growing quickly concerned that I'll end up having a formula with more lines than my original source data if I go at it that way, and as much as I'd be okay with writing a completely incomprehensible formula, this data will eventually be seen by people outside my organization, and I suspect the receiving organization would prefer to be able to actually open this when they get it. I also tried pivoting out a new pivot table where I return a 1 if the employee worked over 40 hours in a specific week with a calculated field - that just ran into the double counting problem. If I sum up everything for each client number, I'm guaranteed to count the same weeks twice if any two employees worked over 40 hours. At this point, I'm not certain what else is worth spending time on, and I'm quickly running out of functions I actually know how to use.
Any help that gets me closer to a repeatable method is greatly appreciated. Let me know if I'm missing anything that could help make this clearer and I'll add it to the post. Thank you in advance.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Is it possible to isolate weekly data from rolling 28-day totals if I don't have the starting "anchor"?Hi everyone, I’m looking for some help with a data extraction problem. I receive a weekly report for a subscription service I manage, but the system only provides Rolling 28-day totals. For example: Report 1 (March 1st): Shows total revenue for the last 28 days. Report 2 (March 8th): Shows total revenue for the last 28 days. Since these two periods overlap by 21 days, I want to work out exactly what happened in that one specific new week (the 7 days between the reports). The Mathematical Problem: I know the standard formula to extract a new week is: New Week = (Current 28-day Total - Previous 28-day Total) + Oldest Week (the one that just dropped off) The Catch: I only started tracking this recently. My very first report was already a 28-day rolling total, so I don't know the value of the "Oldest Week" that needs to be added back in. My Questions: If I have 5 or 6 of these rolling reports, is there a point where I can eventually work out a real weekly number (not an average), or will every subsequent week be "artificial" because I never knew the value of that very first week? If I just assume the four weeks in my first report were equal (Total ÷ 4) and use that to start my calculations, how many weeks/reports does it take until that "guess" is flushed out and my weekly data becomes 100% accurate? Thanks for any insights! submitted by /u/geth777 [link] [comments]
- Pull static data from one sheet to use in a VLookup, then output static data + returned values? Can my process be improved?Hi all, I have a weekly process at work that I am hoping to improve, and preferably completely automate. With the help of this subreddit I have taken this process from hundreds of steps down to a few dozen, but I think more can be done – I am hoping for suggestions (and if automation seems possible, a point in the right direction as to where to start). I will do my best to describe the process without droning on, but there are a number of nuances which I feel may be important. Our largest client sends us a weekly census file for their retirees, which contains information (46 columns) that affect the type of materials I am responsible for sending, as well as the content of those materials, per household (retiree + any dependents). Each file can contain between 1 and 30 retirees. To complete my tasks, I use four separate excel files: The census file The “calculator,” which generates a contribution amount based whether the retiree (and their dependent(s), if applicable) are over or under 65, years of service and what division the employee worked in. It seems like the process is a series of IF formulas, paired with a VLOOKUP. A “premium payment tool” which essentially subtracts the contribution from the premium amounts and is formatted for output onto the master mailing list A master mailing list, which includes the retiree’s demographic info, plus their coverage options and individualized rates To say the process is convoluted is an understatement. Each household is one row on the census file – the client provides the ages and disability status of all household members. My current process: Copy/Paste the retiree’s: first and last name, DOB, SSN, address, and phone number. The order on the client’s file does not match the order that my company uses. I typically will copy/paste one column at a time for all rows in the census file, then complete the following steps one row at a time Determine the ages of the retiree, their spouse, and their children (if applicable). If all household members are in the same age group (Pre or Post-65) based on the date of retirement, only one entry on the Mailing file is required. If one household member is in a different age group than the other(s), two entries on the Mailing file are required. Additionally, if a household member has an “E” in the disability column of the file, they are treated as being Post-65 regardless of age. 3. Using the Calculator, determine the employee's monthly contribution amount based on service years, group, and section type. The process is repeated if the employee has a spouse, and again if they have a child dependent (so from 1-3 times per employee). 4. Copy the monthly contribution amount for each household member into the Premium Payment tool – there are two sheets, one for Pre-65 and one for Post-65. Each sheet has cells for the Retiree, Spouse, and Child. The contribution amount is subtracted from the premium rates and formatted for output to the Mailing file. If there is no contribution amount under one household member, the output returns a “N/A” under that coverage. Ex: https://preview.redd.it/csqgmr9038sg1.jpg?width=975&format=pjpg&auto=webp&s=aa781f5373077cf354886b63ae99349d178281ed 5. Copy the “You Pay” amount onto the Mailing file Besides the time it takes to complete this process for larger files, the sheer amount of copy/pasting required leaves a lot of room for error. What I am working with now is infinitely better than what the girl before me was doing. I was able to add enough formulas to reduce the copy/pasting by about 75% but I suspect more can be done to automate things – I’m just not sure if it’s feasible solely in Excel. I’d love to hear what the community thinks, please let me know what additional info I can provide. Thank you all so much! submitted by /u/stina__saurus [link] [comments]