RYG Ball Formula Issue

Jeff Pindak

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?

Genevieve P.

Hi @Jeff Pindak

For your second issue:

https://community.smartsheet.com/discussion/comment/266225#Comment_266225

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!)

David Tutwiler

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.

Genevieve P.

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

Jeff Pindak

Sure,

see screen shot below of issue 1

Screenshot.jpg

Formula is at sheet level to right. Whats odd is it worked up until about a month ago.

Jeff Pindak

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")))

Genevieve P.

Hi @Jeff Pindak

For your second issue:

https://community.smartsheet.com/discussion/comment/266225#Comment_266225

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!)

Genevieve P.

https://community.smartsheet.com/discussion/comment/266224#Comment_266224

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?

Jeff Pindak

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")

Genevieve P.

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.

Jeff Pindak

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!

Genevieve P.

No problem at all! I'm glad we could find the source of the issue. 🙂