-
Does this community have a bank of shared formulas, because this might be helpful to someone
This formula is very straightforward but it took more than an hour to build, so maybe I can save you some time! This formula looks at the field "Dash Help Due Date" and returns one of five things: No Date if blank This item was due x days ago This item was due yesterday This item is due today This item is due tomorrow This…
-
Formula - IF/OR
Hello, I am attempting to take the SUM of one cell and divide it by a range of other cells to get a percentage output. However, when creating the formula I am receiving an "#Incorrect Argument" error. I need the range in the column(s) because the column "Region Name" will fluctuate with the entries that are made from the…
-
Formula to pull through names of all projects relating to a programme
Hi I'm looking for a formula that will reference a sheet that holds programme and project names. I want to pull through all projects related to a particular project. How can I best do this? I did put it in a report but, on my Dashboard it means I can't pull the data through on my metric widget. I don't want it to show on…
-
complex if/and/or question
I have 5 status columns, each with 4 possible values (Approved, Approved with Comments, N/A, Resend). I want to fill in an "Overall" Status The 5 columns are __________________ Tagless Status Front Graphic Status Back Graphic Status Right Sleeve Status Left Sleeve Status Other Status My logic is If all of the columns are…
-
Financial Year Saving Based on Implementation Date
Hi All, I have a sheet to track cost savings and would like the financial year savings to change based on the implementation date. The three fields are: Implementation Date - restricted date field. Savings Full Year - the overall savings throughout the full financial year, 01 July 2020 to 30 June 2021. Savings Financial…
-
I Need help with formula to prevent AVG Parent percentage from rolling up.
I have several (18 rows to be exact) rows that I am need to get the AVG percentage for. 17 of the rows are currently at 100% and 1 row is at at 92% (because there is one step still left to complete before it can be at 100%). When I do the formula AVG "Children", it wants to round up the percentage to 100% (the actual…
-
Max Cost per Year
I need to determine the max Remediation Cost PER Year. I also, need to pull this information into another sheet's cell. What's the best way to go about this? This is the formula I have thus far, but it's coming out UNPARSEABLE... =MAX(IF([Date of Incident]:[Date of Incident], <=DATE(2020, 12, 31), [Date of Incident]:[Date…
-
Collect Function with Multiple Criteria
I have the following collect function that works fine when I only have one criterion (the answer is 6) but, when I add the additional criterion to evaluate the Date I get an answer of 1 instead of the 6 I was expecting. Any help would be appreciated.
-
How to calculate elapsed time between status changes ?
Hello, Is there a way to calculate the elapsed time between two status changes ? I would need to know how much time it took for the status to change from yellow or red to green. Ideally, I would like to be able to calculate a mean per territory, department and my whole plant on time between yellow/red to green status…
-
Sheet Summary - Countif - #Invalid Operation
Hi all, Ok I am going crazy, hope someone can tell me where I am going wrong: I keep getting #Invalid Operation on a sheet summary Countif Formual based column "Days to Obtain PO Range" - This column had a formula in it I have a second column called "Test" - this column has the same formula as the above Why am I getting a…