|
I feel so dumb for not being able to figure this out but VBA is far from my forté and google is not being very helpful. All I want is a macro that will color my selected row yellow, and change the cell in column B in that row to the word "rep" - here is what I have:code:e: nevermind figured it out code:kumba fucked around with this message at 15:29 on Oct 24, 2013 |
#
¿
Oct 24, 2013 15:15
|
|
|
|
| # ¿ Apr 27, 2024 18:21 |
|
|
Looking for a way to pull some monthly numbers for a team of folks I manage and I'm running into a problem with the way our business system handles account adjustments with taxes. For example, I'm attempting to determine the total number of adjustments (meaning any time we manually issued a credit or debit to an account) each of my folks has completed in the month of June. When I credit/debit any charge that has a tax associated with it, the following occurs: Customer Service Fee/Courtesy Credit: -$10.00 Tax on: Customer Service Fee: -$0.70 The second entry is not a 2nd adjustment manually done, it is an automatic calculation, but because it is itemized separately, when I just run a COUNTIF to sum up the numbers for each user, anything that has a tax gets counted twice (and we do some business in Texas, and certain charges there actually have 3 different taxes, so 1 adjustment shows up as 4 different ones). I want to remove these 'Tax On:' line items from my calculations in terms of the # of adjustments completed, however I don't want to actually remove the line items entirely because I'd also like to run something that sums up total credits issued, which should include the tax dollars credited or assessed. The columns in my spreadsheet look like this: code:Also looking for advice as to how to get the total credit dollars each user gave - what I would normally do is just use a filter to filter by user, remove any positive numbers (debits) via the filter, and sum what remains, but I'd like something more automated so I don't have to do this manually every month. I tried reasoning my way through this trying to use VLOOKUP somehow but I'm not sure that's the right direction.
|
|
#
¿
Jul 2, 2014 23:01
|
|
|
ZerodotJander posted:I hope you meant SUMIF and not COUNTIF in your question. I meant COUNTIF, cause in that example I was just counting the number of individual adjustments completed, not how much the actual value of the adjustment is (for comparative purposes, so if Person A did 10 adjustments and Person B did 735, I know there's a lazy person problem). I did not know this =SUMIFS function existed and I think it will do exactly what I needed. Thank you a ton!
|
|
#
¿
Jul 2, 2014 23:37
|
|
|
I am trying to compile a report to pull numbers for people I manage and I've come across a scenario where my sheet is double counting certain instances of things and I can't figure out how to filter out duplicates. Example date:code:# Submitted by A # Submitted by B # Withdrawn My issue is with the withdrawn status - I have a relatively simple COUNTIF counting "Person A" under the "Submitter" column, however of course I don't want the 3rd issue that was withdrawn counted. Is there an easy way to exclude it? I was trying to use =COUNTIFS(C:C,"Person A",D:D,????) Maybe a Match statement nested in an IF nested inside the ??? That seems way more complicated than it should be considering if I have 5 different people I would need a different match to account for each person, so that sucks. There's probably something ludicrously simple I am overlooking here.
|
|
#
¿
Aug 8, 2014 20:24
|
|
|
See, I knew it was something stupid. Thanks a ton.
|
|
#
¿
Aug 8, 2014 20:38
|
|
|
I've been unexpectedly thrown into a new job role that I wasn't ready for and now I have to analyze data I'm unfamiliar with that is in a really lovely format. I'm hoping someone can nudge me in the right direction here. I'm an analyst for a call center. I've been tasked with analyzing our adherence data, e.g. how closely someone adheres to their phone schedule. Getting the straight adherence numbers is easy: we just look for how many minutes they were available to take phone calls and divide it by the number of minutes they were scheduled to be available for any given day. Easy peasy. The trick that I need to learn is to determine, for people who are deviating from their schedule, is the reason for missing the target. This is proving difficult due to how this data is setup. Here's very small snippets:  This is a small snippet of what the adherence system looks like. Each hour is broken up into 15-minute intervals. It's color-coded for what they are scheduled to do at any given moment - in the above, dark blue is scheduled to be on the phone, teal is scheduled for break. The number in the box is the number of minutes spent available, so a 15 in a dark blue box is a perfect score for that period (note it does count seconds, so the 99% adherence below it is because this agent was available for 14.9 minutes out of 15. This is just noise and I don't care about this at all for the purpose of this analysis). At 9:30, the agent missed 3 minutes of their scheduled time. As you can see, this gives me the numbers but does not give me the why. Here's the other bit of data I need to somehow combine this with:  This gives me exactly what the agent was doing, but the date is only provided once (in the very first row for that day) so matching is proving cumbersome, among other things. So from this, I can see in the 9:30 - 9:45 block, the agent went unavailable for break at 9:40, and completed his work from the previous call at 9:41:32, so they missed their schedule by 3.5 minutes. I need to log this as 3 minutes missed due to going to break early. I've setup a bunch of different categories of reasons they might deviate and I've been tallying the number of minutes for each miss as it fits into each category, like so:  (M/P/T/C is shorthand for Meeting/Project/Training/Coaching) The top 3 is easy enough just using match/index and arrays. The main portion I'm trying to automate is the tallying of the minutes missed in the table, and I'm kind of at a loss as to how. Manually combing through these schedules every month is NOT something I want to do, but I can grin and bear it for a little bit while working on a permanent solution. Excel may not even be the best tool for this, at least by itself. I'm SQL savvy but none of this information is available in a SQL database as far as I know (or at least not one I have access to) so that's no dice. Has anyone done anything like this that could give me a few pointers, or even a book or something that might help at all? My really basic, high level idea is to essentially build a list of events that could count against adherence (basically anywhere they log on, log off, roll available, or roll unavailable can count depending on their schedule at the time of the event), and then try to do some horrible mess of lookups and subtracting times but I'm having a hard time conceptualizing it. Thanks in advance for any tips.
|
|
#
¿
Apr 5, 2017 15:52
|
|
|
I recently joined the WFM team in order to try and make sense of all this. Unfortunately, there does not appear to be any sort of out of the box report that does this, and I confirmed with our in-house Telecom group that deals with the implementation of the software and hardware that that is indeed the case. I went through all of them yesterday and found zilch, though it's possible it's just our implementation. I may have to reach out to the support team for our WFM application to see if we're just missing something obvious, because I'm absolutely floored that this isn't something this thing is capable of. I did learn that it's spitting its reports out of some sort of SQL database somewhere (thank loving goodness) so if I can just get access to that it should be a hell of a lot easier to manage since that's my background. That is of course assuming that the DB behind this is sensible, which I'm not holding my breath for. I was hoping I was missing something obvious in Excel, but really half of the point of my post was to see if anyone else agreed that Excel is not the tool for this. Now that I have feedback, time to try to convince the boss  Thanks to the both of you, much appreciated.
|
|
#
¿
Apr 6, 2017 19:46
|
|
|
I have a relatively simple line chart that is populated with data driven by a few drop-down menus at the top of the sheet: specifically, a user selects a representative's name from a menu as well as the metric they'd like to see and the chart generates along with a simple 6 month rolling average. That part has been relatively painless to setup and works wonderfully! That is, until I end up switching between two metrics that use vastly different data types, and then this thing gets thrown into a whirl. Here's my setup: I have a tab for each metric that has the data for each individual rep, and these tabs are properly formatted to the data they contain. I have an additional summarization tab that, based on the selections in the drop down menu, goes and grabs the appropriate data for the appropriate person using combos of INDEX, INDIRECT, and VLOOKUP. This is all straight-forward, but the problem is that since I'm essentially using a single row that is dynamically populated with whatever metric a user selects, when they swap between metrics with very different data types the cells don't change along with them, so I end up with nonsense. An example: Hold Time is displayed in m:ss custom format, but Total Calls Handled is just an integer. So if the cells are formatted for m:ss and you select Total Calls Handled, the vertical axis on the graph turns into bullshit:  Is there any way to get excel to change the cell format to the right one based on the cell it's actually yanking the data out of? Or, barring that, is there a way to enforce the Y-axis to behave intelligently somehow? Or do I need to have different rows for the various different kinds of metrics, and then have the chart dynamically pick the right range? I'd rather avoid the latter if I can help it because that sounds convoluted but I'm open to suggestion.
|
|
#
¿
Apr 11, 2017 22:28
|
|
|
SSRS is my end-goal for this but I need to build out the database first so that's definitely a long-term solution. This was basically my attempt at a quick and dirty simple dashboard since my director needs something cause at the moment we have no way to visualize trends. I don't know anything about VBA but I have colleagues that do, I'll see if I can get a hand. Appreciate the advice, and the formatting tips will certainly help e: Figured this out using VBA, a few lookups, and the fact that you can apparently assign macros to run upon selection from a combobox, which is nifty. I'm sure this isn't the best written code in the world but it works! code:kumba fucked around with this message at 17:44 on Apr 12, 2017 |
|
#
¿
Apr 12, 2017 14:11
|
|
|
Can you just make the filter only display values > 0.001 or whatever your actual cutoff is instead of setting it at 0?
|
|
#
¿
Apr 27, 2017 22:06
|
|
|
Times in Excel are measured by looking at the time elapsed since January 0, 1900*. This is done by counting the number of days in Decimal format like so: DDDDD.ttttt ; where DDDDD is the date component and ttttt is the time component You can look at this in Excel by typing in a DateTime into a cell then converting it to general. For example, 5/22/2017 10:41 AM is equivalent to 42,877.44514. That is, 42,877.44514 days have elapsed since January 0, 1900. The =INT() formula strips out the integer of that, which is just the day component. It follows, then, that taking the original cell and subtracting just the day component gives you just the time by itself. Example: If A2 contains "5/22/2017 10:41 AM", then: A2 = 42,877.44514 (in General format) =INT(A2) = 42,877, which is just 5/22/2017 = A2 - INT(A2) = 42,877.44514 - 42,877 = 0.44514, which is just the time component of 10:41 AM** Hopefully that helps! * You can observe this by just plugging in 0 in a cell then converting it to DateTime. A value of 1 converted to DateTime results in 1/1/1900 12:00 AM. ** NOTE: This statement is slightly reductionist. Technically, 0.44514 corresponds to a full DateTime of 1/0/1900 10:41 AM. As long as the destination cell is formatted to only display time, it will display just the 10:41 AM component. kumba fucked around with this message at 18:15 on May 22, 2017 |
|
#
¿
May 22, 2017 15:52
|
|
|
me your dad posted:Amazing - thanks for the very clear and comprehensive answer. That explains it very well! No problem! I did add a small addendum at the end because something wasn't exactly accurate, but I think it was probably close enough
|
|
#
¿
May 22, 2017 18:16
|
|
|
Has anyone had any trouble with =AVERAGEIF including #N/A cells for.... no discernible reason? I have a sheet that has a bunch of values and some of them are #N/A (new employees that do not have numbers yet). In this sheet, I use =AVERAGEIF(Range,">0") which was a trick I learned via googlefu to ignore the errors, and it works beautifully. I have another sheet that I am doing literally the exact same drat thing but I get either #DIV/0! error (when using array {=AVERAGE(IF(ISNUMBER(Range),Range))}) or it just gives me 0.00 when using =AVERAGEIF(Range,">0"). The only difference is that in the sheet where it's not working, the values are pulled from a VLOOKUP from a connected worksheet. I cannot imagine why this would cause any problems. One thing I noticed is that the values being pulled were in that dumb "Number Stored as Text" state from an application I exported from, so I converted to number and resaved the linked sheet, and even went as far as refreshing the new sheet and individually refreshing all the cells with VLOOKUPs, and no dice. Frustrating. Any ideas?
|
|
#
¿
Jun 6, 2017 20:02
|
|
|
Richard Noggin posted:If VLOOKUP returns #N/A you'll run into problems. Try using ...IF(ISNA,VLOOKUP...) to return something other than #N/A from a failed lookup. See Example 5 here. Ugh I don't want to redo all these formulas bu Fingerless Gloves posted:I've worked around that by doing a sumif >0 / countif >0. It feels sloppy, but generally works when averageif is a bit fucky  This worked perfectly, you are awesome. Thank you!
|
|
#
¿
Jun 7, 2017 15:12
|
|
|
I'm having a hard time understanding exactly how that's laid out and what you're trying to do, could you give a brief snippet of the sheet as a visual aid?
|
|
#
¿
Aug 3, 2017 17:48
|
|
|
I'm trying to summarize some data from related Excel workbooks and I think the only way this is going to work is VBA, but I'm wondering if someone smarter than me may have a better solution (I'm not opposed to using VBA but I'm not well-versed enough in it to actually write something to do what I need). I work in a call center, and we have monthly Quality spreadsheets with data that is pulled by our QA agents. Each agent on the phone gets their own spreadsheet each month. These are stored in a consistent folder/filename structure on sharepoint, so building filename strings shouldn't be too difficult given a list of people. Here's what I'm trying to do: Each workbook has (among others, but these are the 2 that are important for now) tabs called 'Info' and 'Analyst Summary' - the former is where the QA agent inputs some metadata (their name, name of the person being reviewed, and importantly the # of calls being reviewed). The latter gives a summary, split by call, of the scores they received in their evaluation. What I am hoping to do: each month, I'd like to rip out the information from this 'Analyst Summary' tab for each person and consolidate it into one sheet for ease of review. Here's what the 'Analyst Summary' tab currently looks like:  The number of calls evaluated is determined by a value in the 'Info' sheet, as each person is different (newer folks get more calls evaluated during their training/first few months). I know I can do all this by using =INDIRECT and building strings to put in a formula to VLOOKUP all this poo poo, but because of the varying number of calls I still have to go and open each workbook to figure out how many formulas I need for each person, which at that point I may as well just open up the loving things and manually copy & paste the rows I need and call it a day. Is there a better solution here or do I need to go learn some basic VBA to accomplish this? Thanks in advance for any tips!
|
|
#
¿
Oct 2, 2017 17:53
|
|
|
docbeard posted:If you know the maximum number of calls you will ever need, you could add something like IF(a2>total_calls,your formula,"") to your formulas. (Where total_calls is a reference to the number of calls for that person). And then just make sure your summary template has enough rows to accommodate everyone. I had an epiphany that I can just pull the maximum number of calls for everyone, and just apply a filter at the top to remove blanks. This should work in theory, though I am having a hell of a time getting the INDIRECT function to actually work properly I've built up a filepath/sheet name string and, when used directly in a VLOOKUP like so: =VLOOKUP(1,'http://employees/sites/Department/SubDepartment/QA1/LastName, FirstName/Year/[YearMonth LastName, FirstName.xlsm]Analyst Summary'!$A$2:$I$12,2,0) This works just fine. However, if I try to use INDIRECT and instead build the filepath out of its parts, like so: =VLOOKUP(1,INDIRECT("'"&Names!$C$1&Names!$A5&"/"&Names!$C$2&"/["&"17"&"09"&" "&Names!$A5&".xlsm]"&Names!$F$1&"'!"&Names!$F$2,FALSE),2,0) I get a #REF error. I know I've built the string correctly, as I can put just whats contained within the INDIRECT into a separate cell, copy it and paste value, and paste that directly into the VLOOKUP and it's perfect. I'm not sure where else I could be going wrong! e: apparently using an external reference with INDIRECT() requires the external workbook to be open, so, gently caress. balls. fuckballs. kumba fucked around with this message at 22:01 on Oct 3, 2017 |
|
#
¿
Oct 3, 2017 18:22
|
|
|
Alfalfa posted:I have a ss of about 3500 rows that includes a xx/xx/xxxx date. I'm trying to sort the ss by just xx/xx. I tried to format that row using date and only show xx/xx, but when I sort it still includes the year. Is there a way to mass delete the year off all rows? I don't need the info (and never will). I don't know of a way to do it just by that, but if say your list of dates are in A1, you could put =WEEKNUM(A1,1) in column B and =WEEKDAY(A1,1) in column C then just sort by those two columns instead
|
|
#
¿
Oct 31, 2017 16:43
|
|
|
Hughmoris posted:I need a little guidance on an excel stumper. The idea is you're counting the number of non-blank cells in columns B:D for each user, yeah? In that case, in your Total sheet in cell B2 (or wherever it is that John & Amazon intersect) you can use this: =COUNT(INDIRECT(B$1&"!B2:D2")) If you want it to be a bit more dynamic, you can set up a tab of 'Range Lookups' like so:  And then use this formula instead: =COUNT(INDIRECT(B$1&"!"&VLOOKUP($A2,'Range Lookups'!$A:$B,2,0))) Be warned though that INDIRECT is a volatile excel function and, given enough data in your spreadsheet, will cause massive performance issues due to having to recalculate every cell any time any cell is changed. There's probably a better way to do this using MATCH and INDEX.
|
|
#
¿
Dec 19, 2017 14:59
|
|
|
Inzombiac posted:Not sure why I'm having a difficult time with this: Replace those inner parentheses with a comma. =5+MIN(C9+D9,C14+D14)
|
|
#
¿
Jan 19, 2018 18:57
|
|
|
Inzombiac posted:Bless you. I'm so sleep deprived that it didn't even occur to me. We've all been there, I'm sure
|
|
#
¿
Jan 19, 2018 19:33
|
|
|
I have a strange question about using COUNTIF in a data validation formula to prevent duplicates. Here's my scenario: I have a custom QA sheet built for contact center evaluations. Each tab at the bottom is for an individual call, with a cell for the Call ID in G2. I want to ensure our QA folks don't accidentally grade the same call twice. There's a hidden tab named 'Analyst Summary' that pulls in all the data from the call grade sheets, including the CallID input by the grader which goes into Column M. Thus, in each of the individual call sheets, I have data validation in place on the CallID field with the formula: =COUNTIF('Analyst Summary'!$M:$M,G2) <= 1 I can test this out and it works just fine: I put a call id in the contact 1 sheet, go over to the contact 2 sheet and place the same call id, and as soon as I hit enter the validation is triggered. Hooray. HOWEVER, if I instead go to the contact 2 sheet, place the same call id, and instead of hitting Enter I just click out of the cell, the data validation does not trigger. It will let me put in a duplicate without making a fuss. If I do this and then go to the contact 3 sheet and put in the same call id, then regardless of my cell exit method the validation rule kicks in and prevents the duplicate. I'm assuming this has something to do with the order of operations in Excel behind the scenes - but I'm at a loss as to how to deal with this particular quirk. Anyone have any fun ideas? e: this also doesn't work if they copy a cell and paste. it DOES work if they copy the contents of the cell, go to another sheet, double click in the target cell, paste the text, and hit enter good god this is stupid kumba fucked around with this message at 15:07 on Jul 12, 2018 |
|
#
¿
Jul 12, 2018 14:24
|
|
|
Doc Fission posted:Hi Excel thread. At work I use a database that constantly requires me to smash two data sets together in ways that so far have been awkward and terrible. I think I've finally discovered the magic formula thanks to Google. Easiest to explain starting from inside the thing and working outwards: COLUMN() returns an integer corresponding to the column of that cell it's in; e.g. if you put =COLUMN() in cell B2 it will return 2, because B is the 2nd column. =COLUMN($V1357), because the reference to the column is absolute, is always going to return 22, because it's returning the column value of the cell inside it, in this case column V. So, COLUMN()-COLUMN($V1357) is going to be whatever column that formula is in minus 22. This argument is used within the VLOOKUP to figure out which column of data to grab from your lookup range $V$2:$Z$1262. So, if this formula is in say column Z, then it's effectively IFERROR(VLOOKUP($A1357,$V$2:$Z$1262,(26 - 22)+1,0),""), which is IFERROR(VLOOKUP($A1357,$V$2:$Z$1262,5,0),"") VLOOKUP is looking at the value in $A1357 and attempting to find a match in the first column of your lookup range, so in this case it's looking for that value in column V. If it finds a match, it will return the value in the 5th column in your lookup range, since the col_index_num argument is 5. So, if for example the value of $A1357 is "Ham Sandwich" and cell V100 also contains "Ham Sandwich", the formula will return the value in Z100. If it does not find a match, the VLOOKUP will return an error. The IFERROR(formula,"") part is simply replacing any error returned with an empty string.
|
|
#
¿
Nov 8, 2018 17:54
|
|
|
Has to do with how Excel is actually storing numbers behind the scenes (from here):quote:Excel stores its data as 32 bit binary floating point notation. Thus the decimal number 59.3 is actually stored as 59.299999 and 52.9 would be stored as 52.900002. These numbers are rounded for display in Excel, but when read by Word mail merge (any version from 2002 to the present) the underlying data is read and so you see 59.299999 or 52.900002 or whatever in your merge document.
|
|
#
¿
Jun 12, 2019 15:09
|
|
|
Nazattack posted:I have a list of assets that looks like this: Take a look at this page. If you know all the asset strings you should be able to list them out without numbers and use a combination of =maxifs() and that page to do what you want
|
|
#
¿
Jul 17, 2019 18:08
|
|
|
I'm dealing with some data conversion between systems and I have a question about how to format an export from one system as an import into another that I'm hoping someone can provide some guidance on. I don't have any way to customize the export itself (and that's one of the reasons we're moving away from this poo poo system), so I have to do some finagling in between to get it in the right format. Here's the rough idea of my data:  This is employee punch data from our timekeeping system, so these correspond to punches in / punches out. Ignore the fact that some of these punch pairs are identical times (e.g. rows 77 & 78) for the moment. Here's what I'm having trouble with: I need to insert a column to the left of all the timestamps that, for each row under a specific employee, contains the value in the current Column E. So for this example, I need to shift all those timestamps into column B, and in column A, I need the value 1344 in rows 69 - 78, and the value 1842 in rows 80 - 97. The number of rows is going to fluctuate between employees. I'm assuming there's some way to look for the closest row above with a value that is not a timestamp in a column, and retrieve the ID from said row? I just can't wrap my head around how to accomplish that or if I'm making this too difficult and there's an obvious easy answer. There's a few other things I need to do but I think I mostly have those settled, this is my sticking point. Any help would be much appreciated!!
|
|
#
¿
Aug 2, 2019 13:59
|
|
|
the two of you just absolutely blew my loving mind with how easy that is. this is perfect, thank you both!!!!
|
|
#
¿
Aug 2, 2019 15:06
|
|
|
sorry for double post, but another question: apparently I also need to convert the format the timestamp is in and I'm trying to figure out if there's a way to do it en masse and not 1 by 1? rows 20 & 21 are in the proper format; rows 22 & 23 are not I selected the whole column and formatted to custom (yyyy-mm-ddThh:mm:ss), but the values don't actually update until I actually edit the cell (e.g. F2 then Enter) and I'd really like to avoid having to do that for potentially thousands of cells. I saw somewhere that Shift + F9 should recalculate the whole tab, but that doesn't seem to be doing anything for me, and doing CTRL ALT F9 just recalculates formulas, it's not actually updating the format What's the best way to accomplish this?
|
|
#
¿
Aug 2, 2019 15:26
|
|
|
time for my next "holy gently caress converting timestamp data from one system to another is headache-inducing" question. say I have data of the following format: and i need to convert it into this format:  EmployeeXRefCode is just an ID lookup so that's no big deal, but I'm getting frustrated as to how to split the single cell of time data into two different ones across multiple columns for many employees. each column is a date and i'll be doing this conversion for about a month's worth of dates at a time for about 100 employees, so doing this by hand is not really feasible. i figured text to columns with a '-' delimiter would work, but it seems I can only do that for one column at a time? is there some trick to being able to do that across multiple columns at once without getting into VBA territory? then comes the need to pivot the thing since I have one row per employee in the first bit, but i need one row per shift in the second bit and then converting to 24h time but that piece is easy once everything else falls into place this feels like one of those things where each individual step is very simple, but putting it all together for hundreds of rows is a pain. anyone got any tips how to handle something like this? e: huh. to add to the mess, you apparently can't use text to columns with a destination in a separate sheet, so that's fun and not frustrating at all kumba fucked around with this message at 14:03 on Aug 22, 2019 |
|
#
¿
Aug 22, 2019 13:49
|
|
|
totalnewbie posted:How often do you need to do this? Is this something where recording a macro would be helpful? That's pretty much been my approach so far. I recorded myself doing text to columns on one column then just copied the relevant bit of code and manually replaced the ranges, I was hoping I was just missing something obvious 
|
|
#
¿
Aug 22, 2019 19:55
|
|
|
if you have access to an enterprise microsoft license you may also have access to Power Automate (formerly Flow) and Forms, and I'm fairly sure you could do all of that with those two things it might be trickier than setting up an access db & front end, but building new things in access in 2020 sounds like a bad time
|
|
#
¿
Jan 21, 2020 20:22
|
|
|
Is there any excel equivalent to PARTITION BY in SQL? I'm trying to build a (stupid, thankfully temporary) spreadsheet that will calculate poo poo related to attendance, and I need to evaluate consecutive absences for each individual. If the data was guaranteed to be ordered, this is unbelievably easy; my problem here is that I cannot guarantee the rows I need to evaluate are consecutive (and in fact are basically always going to be non-consecutive). Basically the place I work is overhauling its attendance policy to not count consecutive absences against you - e.g., if you call out for a day because you're sick, that counts as 1 point. If you call out for an entire week because you're sick, that also only counts as 1 point. I need to find some way to only count the first day in a consecutive string of absences without the data being grouped together. Effectively what I need is something like: code:Rows 4 & 5 do not count because Bill is out for 3 days in a row Row 6 does count because Jeff did not call out on 1/4/22. (Side note: yes I know this is a loophole where someone out for less time is penalized more. I didn't come up with this mechanism, it's stupid, but it's what I'm working with) My thought was to use a helper worksheet with a calendar full of all the workdays and assign consecutive numbers to each (we're not open for business every day so I can't just compare the dates themselves), and use this to identify gaps larger than 1, but how to shoehorn this in to this workbook is really evading me. Anybody done anything like this and got any tips??
|
|
#
¿
Dec 29, 2021 23:06
|
|
|
TheLastManStanding posted:Then for each absence you can assign it a 1 or 0 based on if an absence exists for that person on the previous date number. This is the bit I couldn't work out and seems so obvious after you posted it. Works like a charm, thanks a ton!! nielsm posted:Beware weekends and holidays if you do this. Make sure it does what you expect it to. Noted. I've already removed necessary weekends & holidays from my calendar so I should be good to go there. Thanks y'all!!
|
|
#
¿
Dec 30, 2021 15:17
|
|
|
LawfulWaffle posted:I have a monthly report calculator that I put together to help automate some work, but I've run into an inconstancy that maybe someone can help me identify. Let me start with two screenshots: my first thought: your last argument is <=DATE(2021,9,30), but that means MIDNIGHT 9/30/21. Are there times hidden in column K, and are there entries for 9/30/21 that sum up to your difference (and a corresponding lack of entries on the final days of other months that do calculate right)?
|
|
#
¿
Jan 25, 2022 19:34
|
|
|
LawfulWaffle posted:
yes and yes - and drop the = from the <= to avoid an exact 10/1 at midnight entry being counted in september
|
|
#
¿
Jan 25, 2022 20:21
|
|
|
Everett False posted:Let's say that in A1 I have "February 14 9:00:00" and in B1 I have "February 15 9:00:00". Then in C1 it just has "February 15" with no time attached. Is there an easy way to determine how many of the hours between A1 and B1 are in the date given in C1? The end goal would be a formula that in this case would spit out '9.00'. a simple =ABS((C1-B1)*24) (or really just a =(B1-C1)*24 but going in that direction feels wrong to me for some reason) in this case will give you the 9 hours but i'm guessing real data is going to be more complicated than that
|
|
#
¿
Feb 28, 2022 19:16
|
|
|
me your dad posted:Thanks - the good news (I think) for me is I just found out they only need these combos: What structure is the data in? Does an email address have a row for each category (e.g., the table structure is Email Address | Category) or is it one row per email with multiple columns (e.g., the table structure is Email Address | Cat A | Cat B etc with a value of 0 or 1 or whatever) How you have it structured will inform your easiest approach
|
|
#
¿
Mar 24, 2022 15:45
|
|
|
me your dad posted:After a lot of looking at it, I found it very messy. But I was able to clean it up a bit. Each row contains a category and an email address. here's my maybe overengineered shot at it - you can assign unique values to each category such that each individual combination of categories arrives at a distinct sum (think binary operations). so for example  (EDIT: lol oops you can do it with 1/2/4/8 instead of 1/4/8/16 but whatever you get the idea) so for the combinations you want, you end up with:  so now we wrap it up: here's my faked data with a vlookup to the points table  from there you do a sumifs for each unique email and then vlookup the total  and voila, you get your desired combinations and you can filter out the #N/A rows that don't match your needed combos there is probably an easier way to do this but this is where my mind went!
|
|
#
¿
Mar 24, 2022 16:50
|
|
|
Falconier111 posted:I’m having some issues with VBA in Word, but there is no Word VBA thread around these parts. Would this belong here? this is the closest we have to a VBA thread i think and there's definitely excel VBA questions in here, go hog wild
|
|
#
¿
Mar 24, 2022 18:20
|
|
|
|
| # ¿ Apr 27, 2024 18:21 |
|
|
I am sure this is some floating point bullshit but I have literally never seen Excel do something like this on a simple calculation what the actual gently caress is happening to the blue rows and why is the orange row fine
|
|
#
¿
Apr 14, 2022 23:17
|
|