RYG Ball Formula Issue
We have used:
=COUNTIF([Risk Tolerance]:[Risk Tolerance], "Green")
and with red and yellow respectively, to count the total balls in a column.
About a month ago it just stopped working. Tried re-entering a few ways, but it
keeps saying #INVALID OPERATION. I want to convert to a Column formula now that SS does that, but i cant get it working to do that.
ALSO, i have a working formula:
=COUNTIF(Status:Status, "In Progress")
It wont let me convert to a column formula. Any ideas?
Best Answer
-
Hi @Jeff Pindak
For your second issue:
Column Formulas can't have row references in them. Right now you have [Start Date]6 indicating that this is looking at row 6. Instead, you will need to use @row, like this:
=IF(AND([Start Date]@row = "", Complete@row = ""), "Un-Scheduled", IF([Start Date]@row = "", "Un-Scheduled", IF(Complete@row = "", "In Progress", "Complete")))
See our Help Center for more information on Column formulas and @row (here!)
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
That's odd. I've re-created your columns in a test sheet and copy/pasted your formulas in directly. Both worked for me and I was able to convert both to a column formula.
I would suggest reaching out to your account rep and seeing if you can get tech support to take a look.
-
Hi @Jeff Pindak
I was also able to use your first formula successfully, with the "Risk Tolerance" column being a Symbol column housing different coloured balls.
Generally the INVALID OPERATION error seems to signify there's an operator incorrectly used (such as => instead of >=). You can see a list of formula error messages and what they mean in our Help Center (see here).
Because of this, I'm wondering if anything was added on to your original formula that you didn't post, here? It would be helpful to see a screen capture of your sheet (but please block out any sensitive data), along with confirming what column types are being used (aka what column is the formula being placed in, and what is it looking at).
In regards to your second formula, it also looks like it's formatted correctly to be a Column Formula. Can you post a screen capture of the error you are receiving? I'm also curious to know why you might want it to be one, as that type of formula would have the exact same result if it's in one cell or in each cell through a whole column.
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Sure,
see screen shot below of issue 1
Formula is at sheet level to right. Whats odd is it worked up until about a month ago.
-
On second issue, i copied the wrong formula.
Sorry its this one:
=IF(AND([Start Date]6 = "", Complete6 = ""), "Un-Scheduled", IF([Start Date]6 = "", "Un-Scheduled", IF(Complete6 = "", "In Progress", "Complete")))
-
Hi @Jeff Pindak
For your second issue:
Column Formulas can't have row references in them. Right now you have [Start Date]6 indicating that this is looking at row 6. Instead, you will need to use @row, like this:
=IF(AND([Start Date]@row = "", Complete@row = ""), "Un-Scheduled", IF([Start Date]@row = "", "Un-Scheduled", IF(Complete@row = "", "In Progress", "Complete")))
See our Help Center for more information on Column formulas and @row (here!)
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
For Issue 1, based on this I would scroll through the entire Risk Tolerance column. You are absolutely correct, this formula should work, however if there is an INVALID OPERATION in the column it's referencing (Risk Tolerance), then it will repeat that error instead of providing a result.
How is your Risk Tolerance column being populated, is this a Nested IF statement? Could it be that around a month ago something happened in that column which now produces an error, and therefore your summary formulas are also showing that error?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Issue 2 suggestion worked Genevieve, Thanks!
on Issue 1,
This formula exists inside Risk Tolerance:
=IF(AND(TODAY() - [Start Date]2 > 2, ISBLANK(Complete2)), "Red", IF(AND(TODAY() - [Start Date]2 > 1, ISBLANK(Complete2)), "Yellow", "Green"))
And at Sheet Level, trying to tally them using the issue im referencing here:
=COUNTIF([Risk Tolerance]:[Risk Tolerance], "Green")
-
Hi @Jeff Pindak
Are there any errors throughout your Risk Tolerance column, perhaps way down at the bottom that you can't see right now?
You could update this column to also be a Column Formula, by adjusting your row references to @row again:
=IF(AND(TODAY() - [Start Date]@row > 2, ISBLANK(Complete@row)), "Red", IF(AND(TODAY() - [Start Date]@row > 1, ISBLANK(Complete@row)), "Yellow", "Green"))
That way if anywhere along this column there was an accidental error, this will update the whole column to have this formula. I've confirmed that this is the correct formula structure and doesn't throw an INVALID OPERATION, so this row (row 2) is fine. It's likely that something's going on down at the bottom of your sheet or in a new row that was added in or adjusted around a month ago.
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Once i made it @ row and column formula it did make it easier to hunt down
the issue.
Found it, you where correct.
Thanks so much!
-
No problem at all! I'm glad we could find the source of the issue. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!