Excel Annual Leave calculation

Question

Hi there,&nbsp;Looking for help creating a complex (if even possible) formula!Cleaning up a spreadsheet for work and I'm trying to create a formula that will do a few things:&nbsp;- something to the extent of&nbsp;=COUNTIF(F8:F127, "&gt;2") where it is summing all days where my staffing levels were above the minimum- remove any of the A/L outputted by a seperate formula in another section:&nbsp;=SUM('Department'!K4:L8) if the condition that their "Role" in Column H is on a specified list AND/OR if they have no entitlement allocated in the table (Column G).I know that to subtract the values where those conditions are met would require a formula targetting the table directly (ie: =SUM(COUNTA'Department'!K4:L8)-COUNTIF(OR(OFFSET(K4,0,-1)=""),COUNTIF(K4=DataAdmin!$L2:$L6))&nbsp;I feel like I am asking a lot for one formula and I know the basics enough that I am pretty sure this won't be possible, but it doesn't hurt to ask!I appreciate any suggestions on how I can get this to work!&nbsp;NB: The reason I don't just omit the cell ranges for the staff members I don't want to include is because I am not the sole manager of this spreadsheet and the staff members get updated or changed without correcting the criteria ranges, so I'm hoping to try and create this "cover all" formula!&nbsp;Some names have been or will be omitted or changed to protect identity or preserve confidentiality.Happy to provide more details or context if needed!If you think I'm being crazy, feel free to tell me to go and resit Excel Coding and Formulas 101 again&nbsp;&nbsp;- Oli

mathetes · Answer

OliverBoardman&nbsp;&nbsp;Had you intended to attach a copy &nbsp;(or a mockup with names changed) of the spreadsheet? If so, it's not here. If not, please do anyway. Use OneDrive or GoogleDrive and post a link here that grants access.&nbsp;Then, if I might be so bold, don't try to write the formula for us. Instead, describe in words what you want done. You can refer to the column heads (if there's also an attached spreadsheet), but don't try to write the formula for us--that's what you're coming to ask for help on. Describing it in words is the best way to start off.

oliverboardman · Answer

mathetes&nbsp;Good Morning,Apologies for the hubris! I shall use Google Drive, but you shall have to bear with me as I need to recreate the spreadsheet with no names 🙂&nbsp;EDIT: link removed as newer one available

mathetes · Answer

OliverBoardman&nbsp;&nbsp;I hope you're planning on returning to flesh this out some more. You'll also need to give some assistance in navigating this maze, connecting it to your original questions.

oliverboardman · Answer

Good Morning mathetesI'm only able to get back to you whilst at work hence the sporadic replies, but yes I do intend to!So each Ward has a relevant capture column for staffing levels which is found in the 'Daily Totals' sheet.Each Ward also has it's own "dashboard", which is the purple boxes across the top, where all primary data can be entered to populate the rota below.Some of the staff have established A/L Entitlements (see the relevant section in all Ward sheets) and for the most part, this is decided by their role; ie: all 'F1' have 9 days A/L per rota period.As somewhat explained in my original message the way that I am hoping the formula will work is by:[per Ward] (cells for the formula to be in 'Daily Totals' sheet around row 132- Summing all the A/L Taken- Subtracting any rows value, if the corresponding cell determining the staff members role meets a pre-defined criteria (TBD by range within the 'Data Admin' sheet.- And/Or subtracting any rows value if there is no entry for A/L EntitlementThe aim is to achieve a 'True' value of how much leave has been taken by Ward, that should be monitored, as some staff are responsible for tracking their own A/L which is why I would like the formula to only capture certain roles in it's output.As I write this, I can see this needing two separate formulas, but I can work with whatever solutions you suggest!EDIT: If you agreeable, please email me for quicker and potentially(?) easier communication (I will put it in 'Daily Totals'!A1)Kind Regards,Oli

mathetes · Answer

OliverBoardman&nbsp;&nbsp;You really need to realize that just understanding what each of the individual sheets is about is a task in itself.It's clear to you, but really isn't to somebody from the outside, and it's frankly not realistic to expect us to take the time to figure it out. If we were sitting down face-to-face we could probably come, pretty quickly, to an understanding of what all those entries represent, what each row and column are for, etc., etc. &nbsp;But we're not.&nbsp;It would make more sense for you to provide a clear explanation here, not in a private communication, so that any of the other regular Excel-answer people looking at this can follow it, in part because there are others far more expert in some aspects of Excel than I, in part because I don't have all the time in the world, especially at this season of the year.&nbsp;So I'd suggest you start with a clear explanation of what is being captured--and HOW it's being captured--on each sheet. What all your abbreviations mean, etc., and what is you're trying to accomplish. Don't assume it's clear. Assume, though, that you're speaking (writing) to an intelligent person but one who's never seen this layout before.

Forum Discussion

Excel Annual Leave calculation

6 Replies

Resources