-
Averageif and match for summary of info
I'm trying to average the the percent of submissions for an assignment on one Smartsheet based on a teachers ID in the current sheet. Here's the formula I've tried =AVERAGEIF({Quiz 1 Completion Avg}, MATCH(ID@row, {ID })) The results that are populating in my sheet do not match with are not correct. We have one sheet (the…
-
How to use =SUM(CHILDREN()) for all PARENT in the same column ?
Hello i would like to know how i can SUM a budget in the first row of PARENT regarding their CHILDREN. And then make it a column formula but for just the PARENT rows.
-
Days to Launch, stop at Zero
I have created a formula to countdown the days to launch and converted it to a Column forumula so it is automatic for any project. However, we don't need to know the negative numbers or day past launch in this column. I tried nesting an IF function to return a blank cell for anything less than zero but I the dreaded…
-
Is there a way to create a list out of joined string in a cell?
I have Sheet A, were I have a column with Task Description that has parents. I created another column where I put the parents corresponding to the child. Example: Parent ID .........|........Task Description [blankcell]........|........Parent 1 [Parent 1].........|............Task 1 [Parent 1].........|............Task 2…
-
Count Distinct Values in a Column
I have a serial # column that I am trying to create a "helper" column to verify that the serial # entered is unique - to avoid/flag duplicate entries with conditional formatting later. I have tried this formula in the blue highlighted cell pictured below: =COUNTM(DISTINCT(([Computer Serial # (Parent Asset)]:[Computer…
-
INDEX/Match Problem Across Sheets
Here is my problem: I'm trying to compare dates on sheet A to a list of dates on Sheet B and if the dates match, fill in a number on sheet A. If I move the data over to sheet A and use this formula works fine: =INDEX([Column9]:[Column9], MATCH([Column4]@row, [Column8]:[Column8], 0)) When I use a cross sheet reference I get…
-
Long IF Statement Help
Hello, I am attempting to create an IF statement on a Red, Yellow, Green, Grey icon text type with multiple factors. Below please find the inputs listed. If new entry then Gray (COMPLETED) =IF([Form Submission Date]@row = TODAY(), "Gray") If hard deadline has passed and nurse intake has passed then Red (COMPLETED, but…
-
Sheet Formula_ Heath symbol
Hello everyone, I know I am close, but looking for some help. I am trying to create a formula to calculate If start date is less than today, health symbol is Green If start (maybe should be end date) is within (xx) days, health symbol turns yellow If start date (maybe should be end date) is greater than today, turns Red If…
-
Tracking "Time at Current Stage"
Hi Guys I have a question regards tracking comments/ current stage of ticket items We have an export that is added via the Data Uploader which contains "Last Week" & "This Week" fields. We are hoping to expand the range by which we can track how long an item has been at a certain stage. The idea is we add the file via the…
-
vLOOKUP on Multiple Dropdowns Pulling, Calculating Totals
Hoping someone can help! I am have created a multiple dropdown list in one column (a), and a VLOOKUP function using this data in a separate column (b) to pull through pricing from another sheet. I am having some issues when column A has more than one option selected. How can I create a formula that checks all options…