-
=IFERROR formula
Hi Within my project plan sheets I have 2 columns , On Time/Late and RAG. On Time / Late Column Formula =IFERROR(IF(AND([Original vs Current (days diff)]@row > 8, Status@row = "Complete", Milestones@row = "true"), "Late", IF(AND([Original vs Current (days diff)]@row <= 7, Status@row = "Complete", Milestones@row = "true"),…
-
Countifs calculating more than range
Hi All, =COUNTIFS([Execution Team]:[Execution Team], "NA ISG Specialty Event - AH Org", [Start Date]:[Start Date], DATE(2020, 8, 1) <= DATE(2020, 10, 31)) I am using this formula to gather number of events we're having internally. I did this per QTR and change dates, but noticed that it is counting ALL events that are less…
-
Counting check boxes in a row if "Yes" is written in a cell in the same row
The following formula works for counting my check boxes but I cannot seem to figure out how to write the rest of the formula to only count the check boxes in my row if "Yes" is written in a cell in the same row. Help please! =COUNTIF([Building Perimeter Verified]@row:[Reminder Email Sent]@row, =1) Thanks so much!
-
Checkbox Formulas with 2 conditions
Hello, im trying to generate a formula on what is checked the previous and next week but not the current week. im trying this but with no luck: =IF(WEEKNUMBER(Date@row) < WEEKNUMBER(TODAY()), WEEKNUMBER(Date@row) > WEEKNUMBER(TODAY()), 1) Can you help me?
-
How can I count the amount of actions in a certain weekday?
Hi all! I'm trying to setup a formula that allows me to count actions (in red) and that also shows me the weekday in which they were made. The columns I'm using for this are 'S' and 'Action Creation Date'. My formula is currently the following: =COUNTIFS(S:S, "Red", [Action Creation Date]:[Action Creation Date], [Action…
-
How do I account for leap years when adding 1 or 3 years to a date?
I have a database of clients on programs with expiry dates based on qualification date. Some programs expire in 3 years and some expire in 1 year. I was adding either 364 or 1094 to the date. Leap year seems to be throwing a wrench into this. Any ideas? Here is my code. I have a few other conditions in the calculation but…
-
Nesting OR into AND?
Hi All, I'm wondering if it is possible to nest an "OR" statement into an "AND" statement. In short, Task C requires Task A be "complete" and Task B be one of two statuses. I'd expect it to work something like this (note, I'm using status names to illustrate the point): =IF(AND(StatusA="Complete", OR(StatusB="In Progress",…
-
Auto-Populate Left Stub for Schedules
Thanks to Paul Newcome, I was able to get a WBS schedule working. Now I need to create several schedules, using the WBS as the left two columns. Here is the problem - I need for all schedules to update whenever a row is added or deleted from the original WBS sheet. Simply using Cell Linking does not achieve this. Here is…
-
Problem with average number formula, with certain criteria
Hi All! Im trying to get the average number of a certain range, but only if it meets a certain criteria. I have looked at other examples but somehow i don't get it to work. So I want the average of the blue columns. But only if the Red column is a certain name. And if its possible I want to do the formula on a seperate…
-
Hierarchical numbering patterns?
Anyone with experience setting up hierarchical numbering schemes? I want to add a column that is hierarchically auto-numbered such that child rows are added to the numbering scheme as increased decimal points. For example 1. Row 1 --1.1 sub item of row 1 ---1.1.1 sub item of row 1.1 ---1.1.2 sub item of row 1.1 ---1.1.3…