-
IF statement inside a COLLECT
Why do all of these formulas work: SUM(COLLECT({Value}, {Location}, Location@row)) SUM(COLLECT({Value}, {Location}, <>"")) SUM(COLLECT({Value}, {Location}, IF(index@row=1, "Boston", Location@row))) But this does not: SUM(COLLECT({Value}, {Location}, IF(index@row=1, <>"", Location@row))) I'm trying to understand this…
-
Counting Number of Boxes Checked in a Single Row
Hi all! I am wondering if there is a way to count the number of times a box has been checked in a single cell. For example, I have a row that shows the years that an employee participated in a program (2015-2022). You can select multiple because they can stay in this program for many years. Then I would want a formula to…
-
Finding the last highest value, and subtracting from @row value
Hi, I have a sheet where CNC operators enter the hours the machine has worked each day, and i want to avoid them having to reset the time clock eacht time. I have been struggling with a formula that helps me achieve the following objective: Find the highest number of hours entered for the same machine on the current row…
-
Can I extract the month or year from a date column for a formula...
...without creating separate columns for Month and Year like in this screenshot where formula for Verbal Agreement Month is =MONTH([Date of Verbal Agreement]@row) and formula for Verbal Agreement Year is =YEAR([Date of Verbal Agreement]@row) Thank you!
-
Can you check / help with my IF, AND, NOT ISBLANK formula
Hi all, This is my first post :-) Hoping for some help with a long IF formula. I have sheet that has columns with dates in them Launch Date, then Nomination Deadline, then Survey Deadline - in that timeline, so there won't be a survey report deadline after a launch deadline. Sometimes these dates are not filled in. I am…
-
Formula for a PASS or FAIL
I would like to add a formula to the sheet that shows a Pass or a Fail depending on three other columns. If Mold Present column say "No" & the Seeds per 100g column says "None" or & the Foreign Matter column says "No" mark the cell as a PASS. If Mold Present column says "Yes - Beige" or "Yes-Grey" or "Yes-Black" & the…
-
Can I copy rows based on a formula updating?
Hi All - I'm working on new process, and have everything working except one small (seemingly) problem. I have two sheets that talk to each other, and they work fine. In Sheet 1, I have a status field (drop down), and in Sheet 2 there is corresponding Status field. When Sheet 1's Status field gets changed, Sheet 2's Status…
-
Calculate the right average for the columns
I need the average collect to calculate the right total % complete for the columns Each column that has a date is = 100% If there is no date is = 0% but the formula still counts the 0% to give the total % average complete on "Quote % Complete" column. If there is "N/A" either in "1st Circuit Quote Status or 2nd Circuit…
-
Does INDEX(MATCH) work on a column with a formula?
I am attempting to use the INDEX(MATCH) formula with the range being a column that is calculated with a formula. I keep getting a #NOMATCH message - can MATCH only work on a column that has actual values (i.e., not values calculated from a formula)? I am trying to use this formula: =INDEX({Billing Code}, MATCH([Identifier…
-
Need formula help.
I have the following formula: =IFERROR(IF([Date Complete]@row = [Due Date]@row, 0, IF([Date Complete]@row > [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) - 1, IF([Date Complete]@row < [Due Date]@row, NETWORKDAYS([Due Date]@row, [Date Complete]@row) + 1, ""))), "") How do I add a contingency for if the…