-
Build a Burn Rate Formula
Here is a puzzle I'm struggling with, would like to make a burnrate that distributes the total labor expected across the task period of performance. It does this by dividing the labor cost ($), by duration (work days), then multiplying that by the rate by the number of work days scheduled for the task in a month period. I…
-
Index(collect) argument
Good morning, This should be a simple formula to create but I am not properly completing my argument. I have a sheet that has three columns, Most recent (checkbox), date, & summary. From another sheet I want to receive data that is in the summary column if the checkbox in the most recent is checked. The logic on the target…
-
Nested if RYG
I am trying to write the easiest "if" statement and It will not work.... All I am trying to is the following: Red if the "action due date" is in the past and the "Status 2" is either Open, In Planning, In Testing, Monitoring. Yellow if he "action due date" is Today and the "Status 2" is either Open, In Planning, In…
-
How to over ride submitted data through forms?
Hello everyone, I have a form linked to a spreadsheet. I have callers that submit claims to our office through smartsheets using forms and the status is pending. Once their claim is resolved they call us back and we update their record to active. Is there a way for them to change the status to active when its resolved…
-
would like to add an IFERROR to a simple formula, can't seem to get it to work
Hi, I have a simple formula I am using in one of my sheets to calculate a percentage: =[Training/90 day complete]@row / FDS@row I would like to amend it so that if the cell in the Training/90day complete column is blank, to leave it blank rather than calculating it and returning 0%. How would I do that? Thanks in advance!…
-
Formula to check duplicates works...but needs to exclude blanks
Good morning! I'm hoping another perspective will point out the obvious here for me :). The formula is working correctly in Test_A2 if the Role is the same within the given range. Where I'm having a problem is how/where to add in isblank so that Test_A2 equals N if the Role is blank within the range. Ideally, I'd like to…
-
Nested Or within Countifs formula
Hi, I am hoping to see if I can get help with the following formula: =COUNTIFS([Area of focus]:[Area of focus], "HGIT", Status:Status, "Complete", OR([Status:Status] = "Approved"), [Channel:Channel], <>"internal comms") Essentially, my goal is to count items that are for "HGIT" that are either complete or approved but not…
-
Formula to Roll Up Task Status
Hi All! I am looking for some help with a formula that I haven't been able to find in the community already. I'd like to roll up my Status column to the parent rows with the following conditions: Parent shows as Not Started if: All children are either Cancelled or Not Started. Parent shows as In Progress if: Even one child…
-
Report from 2 sheets shows same column header twice and doesn't combine
I have a report of a combination of 2 sheets. Everytihng is the same but they both have one column called "Event Sub-Type" and for whatever reason, the report shows 2 "Event Sub-Type" columns (it doesn't do this with any other duplicate columns, which is extra weird.) Instead, both columns are next to one another and…
-
Calculate date diff from previous row
Hello Smart People - Simple question. I would like to keep a running tally of the time between dates in the same column as it progresses via rows, so [Date]@row - [Date]@PreviousRow. Make sense? I think in SQL we just use "Previous". Thanks, Mark
