-
Calculating an Average based upon a value in other column
I have a calculation to calculate the number of days between two columns. =IFERROR(NETWORKDAYS([Date Submitted]41, [Go Live Date]41), " ") It works great to return a blank if the customer has not gone live. Then I have formula to calculate the average of the cells. =AVG([Number of Days to Implement]12:[Number of Days to…
-
Average % Complete If another Column is Checked
I am trying to get the % Complete for a project by averaging the Project Phases % Complete(which are parents to tasks). * =AVG([% Complete]:[Project Phase], 1) I used this formula to only calculate the values in the row that has been identified as a Project Phase with a check.* Answer is 8.39 * =AVG(CHILDREN()) * 100 -…
-
Next deliverable date based on TODAY
Hi Many thanks in advance to start! For each row i have a listed task with a column called Period 1 with a date and a checkbox next to it than when checked marks the date green. Each date represents when that task is next due. Extrapolate that out across the row and i have many dates for every interval the day is due, ie…
-
Formula for taking an average of duplicate rows
I'm attempting to construct a formula for taking averages of multiple ratings from several raters across three vendors. Sheet 1 looks like: Rater - Category - Vendor A Rating - Vendor B Rating - Vendor C Rating Sheet 2 looks like: Category - Vendor A Rating Avg - Vendor B Rating Avg - Vendor C Rating Avg On sheet two, I…
-
SUM and IF In the same Cell
Is it possible to have a cell include both a SUM and IF statement to do the following. (I do not want use a separate status column.) I want to be able to add up several columns (Actual Admin Labour + Actual Engineering Labour + Actual Mechanical Labour) into a Total Actual Labour column and then add an IF statement that…
-
Remove #NO MATCH return from VLOOKUP query
Is there a way to make a cell blank if there isn't a match? I know I can do an IF statement where IF the value I'm trying to match doesn't appear in the table at all, do nothing, otherwise VLOOKUP. But seems like that may slow the sheet down because it's looking through that table twice, so doing twice the work. Is that…
-
Create Hierarchy Relationship using WBS and Formula?
Hi, I believe this may be a bit of a stretch but I am wondering if it is possible to automatically create the Child/Parent relationship utilizing the numbers or periods in a WBS # column as a way to automatically create the indention. I have built a pretty good process using Smartsheets data uploader to import external…
-
Showing Only the Parent Rows in a Dashboard Chart
Hi - I have created a dashboard to show pie charts of my data. My problem is that the pie chart wants to include every single row as a data point, I only want to show the parent rows and not the children. I used the formula "=Count(Ancestors(row))". This gives me a 0,1,or 2 based on how many ancestors the row has. But I'm…
-
Formula takes a few seconds to populate after the sheet is opened
What could be the root cause for a formula not populating until a few seconds after opening a worksheet? I built a formula off of a contact cell that references a roster to show an individuals manager but unlike all the other formulas in the sheet, this one takes a few seconds to calculate, it is throwing off my reports as…
-
if(and formula
I'm having trouble with a "if(and" formula. =IF(AND({KCPL Hours_ASSIGNED TO} = [Assigned To]@row, {KCPL Hours_TASK} = [Task Name]@row, {KCPL Hours_Project Number} = [Task Name]6), "1", "2") But I get #INVALID OPERATION error message. Any help would greatly be appreciated. Thanks, David