-
Change RYG Automatically based on values
Hello – I am trying to create a formula that will change RYG automatically with the following criteria: Green: Any positive (%) Yellow: 0 to negative 10% Red: less than negative 10% With the below formula, green and yellow work fine but not red. How close am I? =IF([% OVER (-) UNDER (+)]5 > 0, "Green", IF([% OVER (-) UNDER…
-
Time Tracking - Formula needed to total time spent
Has anyone used SS to track actual time spent? I am using the "Time Track for Client Projects" template modified for simple tracking of time spent for individual tasks by client or department. I cant seem to get the formula needed to add the columns for Start time & Stop Time to calculate and provide a Duration for actual…
-
for two criteria query should I use LOOKUP or INDEX MATCH
I need to populate the low range based on a match to a specific specialty and tier. The specialty is just a drop down but the Tier was based on the following IF formula: =IF([Points Total]11 <= 15, 3, IF([Points Total]11 >= 16, 2, IF([Points Total]11 <= 45, 2, IF([Points Total]11 >= 46, 1)))) * Question 1: Should "…
-
Status auto change
Hello, I was wondering is it possible to make the status RYG color changes when it is closer to the due date? For example: it shows red when it is due two days before the date and so on... Is there a formula to right and what is the formula? Thanks a lot
-
Checkbox checked, cancel RYG
Hello, I have created 3 columns for the RYG, if the project is in the early stages the color green will show, if it is due within 4 days, yellow will show, and when it is due in 2 days red will show. Now I have a checkbox for the employees to click if the work is complete and I want the RYG colors disappear when they click…
-
Can a Date autoupdate to the current date
Is there a way to set a date to dynamically update to TODAY? For example, I have a sheet with start date finish date and duration. I want to track how many days into the project without having to update the finish date continually.
-
Formula puts data left-justified instead of right-justified
I have the following simple formula: =IF([Grasp of Material]3 = "Excellent", "100%", IF([Grasp of Material]3 = "Good", "80%", IF([Grasp of Material]3 = "Fair", "60%", IF([Grasp of Material]3 = "Poor", "40%", IF([Grasp of Material]3 = "N/A", "N/A"))))) The formula puts the 100%, 80%, etc. as left-justified. When I try…
-
Form Submission Organization in Sheet
Is there a way to automatically move a new form submission to a nest under a particular row? For instance, I have 4 parent rows that nest several rows underneath and would like any form submissions to automatically be assigned under one of those 4 parent rows based on information that's submitted (if this, then nest under…
-
Creating IF AND THEN statement
Hello I need help with creating if and then statement to perform the following. If Recruited From = Referral And Store Start / Transfer Date = Date Then Store Start / Transfer Date +90 days Here's the code I have and it's giving unparsable. =IF (AND([Recruited From]147 = "Referral", [Store Start / Transfer Date]147= DATE,…
-
COUNT where cells <>"N/A"
Hi, I'm struggling to combine a SUM and a COUNT together (keeps returning an error). =SUM([CELL 1]22:[CELL 4]22) / COUNTIF([CELL 1]22:[CELL 4]22, <>"N/A") This returns a #DIVIDE BY ZERO. CELL 1 to CELL 4 could contain the numbers 1 to 5 or N/A. The end result is to provide a percentage out of 100% for the total cells with…