Null value for COUNTM COLLECT
I am trying to return a null for any zero value for a chart but I get an incorrect argument set error when I try to add an IF statement. My original formula is a COUNTM(COLLECT) to count multiple entries from a multi-select dropdown that are NOT a certain value for several individuals for each month. How would I accomplish…
Another status light formula question
Hello all, I'm having trouble with my status light formula. I have the following columns: Date Due % Complete Status (red/yellow/green/gray) This is my current status formula: =IF(OR(ISBLANK([% Complete]@row), ISBLANK([Date Due]@row)), "Gray", IF(AND([% Complete]@row < 1, TODAY() > [Date Due]@row), "Red", IF(AND([%…
Hello All I have a question, i have a sheet and approval workflow is triggered when a new item is created. There are two approvals that are needed coming from two cell, and i want to see to overal Status of the approval. I am using formual: =IFERROR(IF(OR([BSS approval]@row = "Rejected" , [Function Head Decision]@row =…
IF, THEN for stock inventory
Hi, I'm really new to this but couldn't seem to see in the forums - I've set up a PPE stock request form for the teams to fill out - which autopopulates in the respective columns, including the multi-select PPE column. What I'm trying to do is set up an IF something is requested, THEN the columns which has the current…
Formula to always reference the parent of a child row within a different column?
Hello, I am trying using the parent/child relationship to group a set of data by month and am using the the formula to rank the children rows by their value: =IFERROR(RANKEQ(Value@row, CHILDREN(Value$105), 0), "") But I was wondering if there is a way to make this as a column formula? I would like it to always rank the…
End Date is auto-filled, how to stop?
Hi All, I have created a sheet for my team and outside of normal columns, we have an extra "Target End Date" to track if we meet target or not. Please see picture. However, the Actual End Date is auto-filled every time I enter Start Date with the default value of the same day as Start Date. It is very inconvenient, I am…
Formula to evaluate the output of a calculation and to determine a score according to a range
I am currently setting up a scorecard where the individual is allocated points based on % achievements within specific ranges, and would really appreciate some help in formularizing the allocation of points (score) based on the % achievement For example - In the example below. an Achievement of 93% will score 20 points,…
Any workaround on UTC time?
I was trying to capture the sum of daily submission on the sheet summary base on the system created date, however, it was affected by the UTC time and information capture was not accurate. Any workaround by not getting end user to submit with a date?
Sheet Summary Formula
Asking for help again with a formula. I need to create a formula in Sheet Summary that rolls up how many participants from each of the 10 columns above attended which "Program Type." Program Type is a dropdown. For context: How many Blue Ridge participants attended "PSUDS ECHO." Wondering if the structure of the sheet is…
Converting a formula to a "column formula"
I am trying to convert this formula into a column formula. It takes selected cells from a row to populate the overall "Status" column. If tasks are "Not Started", the overall status will be "Not Started". The Same would apply for Cancelled. If all tasks are complete, cancelled or n/a, the overall status would be "n/a". If…