-
Converting list of date into a string response to match condition in formula
I have two sheets, one sheet has the employee name, start date, end date, total days and the aggregation column. The aggregation column separates the date range from the start date to end date. the Aggregation formula is; =JOIN(COLLECT({Employee Leave (Part 2) - Date}, {Employee Leave (Part 2) - Date}, >=[Start Date]@row,…
-
consolidate dates in horisontal format
Hello, we track our faculty away dates with SS Calendar template where our admins need to check the checkbox for the day a faculty will be way. I need to be able to report those dates in the horizontal and consolidated format because we share it broader and use in many reports. So for example, if the dates are reported…
-
averageif
I am trying to find the average dwell time per SCAC but I'll get a zero return which is not correct or a #divide by zero error message. =AVERAGEIF({OTP 10.21.23 SCAC Range}, "ltga", {OTP 10.21.23 time}). Can you help me find a formula that works properly?
-
Overall Status with Weighted Average
I have a project split by 8 different milestones, is there a way to add a weighted average of completion? For example Milestone A and all of the tasks under it should account for 5% of the project completion, Milestone B and all of it's tasks should account for 15%.
-
change cell color when exp date is within 3 weeks
Hello, I would like to change the color of cells in a column to yellow when the expiration date is within 3 weeks, and to red when the exp date is that day and beyond. I was able set a rule for the red (exp date is in the past and also exp date is today) but cannot figure out how to set the 3-week warning. Thanks!
-
Find a MAX value in row and return the Column name.
I'm trying to find the max value between all the strength columns and have it return the column name of the max value. I've tried the following formula as suggested by AI and other articles I've found but I keep getting #UNPARSEABLE. =INDEX([Behavioral - Strength]@row:[Respiratory - Strength]@row, MATCH(MAX([Behavioral -…
-
Find duplicate rows based on 2 column criteria
I have a sheet that I'm trying to removed duplicates from based on 2 criteria. In the example above, you can see that the APP_ID is the same for all 4 entries. But only the last one is a duplicate based on looking at the unique combination of PID and APP_ID. It's ok for an APP_ID to repeat for different PID #s. But if the…
-
Join Collect Simplified
Can anyone explain the Join Collect formula in plain language? I have two sheets, CS Master and Client Master. In the Client master I want to link the Options column from CS Master and match the Address column. Can anyone simplify the formula for me in terms of English, meaning can you just use words to tell me what goes…
-
conditional totals per column
Hi all Hoping I can find some help on this formula, Trying to figure out how to create sum totals of PO value column at the top of each of the first 3 columns (FERG JOB #, FVLV#, JOB SHOP #) if information is present in those cells. contingent on any of the items in the drop down list from PO/SDR down being selected. Any…
-
IF-OR-AND Formula for Multiple Scenarios
Help! I need to finalize a formula to return a value of "Yes" if one of two AND statements are true. I am not getting an error with the formula below, but it is not returning a value of Yes or No: =IF(OR(AND([Send to Compliance?]@row = 1, [Finance Approval Status]@row = "Approved", (AND([Send to Compliance?]@row = 0,…