INVALID OPERATION in one column but not the other

Eva M

Hello, I have the below formula in three columns in my sheet. They are all referencing the same master sheet, same dates, same facility, same units, but different utilization rates. Two of the three columns are working great. The last one isn't. I re-typed the formula in several times, following the other columns' formula, just changing the reference to the one item that is different for this particular column. I keep getting INVALID OPERATION. Can't figure out what I am typing wrong. Could you please look? Thank you!!

=(SUMIFS({Patient in Bed Utilization SCA-ONC Master  Range 5}, {Patient in Bed Utilization SCA-ONC Range 4}, "Baptist", {Patient in Bed Utilization SCA-ONC Range 5}, =Metric2, {Patient in Bed Utilization SCA-ONC Master  Range 1}, AND(@cell <= TODAY(), @cell > TODAY(-7))) / COUNTIFS({Patient in Bed Utilization SCA-ONC Master  Range 5} >= 0, {Patient in Bed Utilization SCA-ONC Range 4}, "Baptist", {Patient in Bed Utilization SCA-ONC Range 5} = Metric2, {Patient in Bed Utilization SCA-ONC Master Range 1}, AND(@cell <= TODAY(), @cell > TODAY(-7))))

 

Andrée Starå

Hi Eva,

What columns are the formulas in?

This might help.

#INVALID OPERATION

Cause

Operators (see Create and Edit Formulas for details on acceptable operators) in a formula aren't supported in Smartsheet or are mistyped. For example, this IF statement that returns the text "Low Stock" if a cell contains 25 or less...

=IF(Quantity1 =< 25, "Low Stock")

...has the "less than or equal" to operator in the wrong order; the correct order being <= to make the formula =IF(Quantity1 <= 25, "Low Stock")

Resolution

Check all operators to make sure they're not mistyped and are supported by Smartsheet. See Create and Edit Formulas for details on acceptable operators. The most common cause of this is typing <= (less than or equal to) and >= (greater than or equal to) operator combinations in the wrong order.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Right off I see some missing commas in your COUNTIFS.

 

......COUNTIFS({Patient in Bed Utilization SCA-ONC Master  Range 5} >= 0, {Patient in Bed Utilization SCA-ONC Range 4}, "Baptist", {Patient in Bed Utilization SCA-ONC Range 5} = Metric2, {Patient ........

.

There should be a separator in between range and criteria. Start there and see if that helps any.

Eva M

Hello, see a screen shot of the column and sheet. What's weird is that the formula is working lower, in the same column. I will try to actually drag it up now to see if that might somehow fix it.

Eva M

So, the dragging up of the formula from the next section of the column worked...BUT...now, even though, technically the formula is right, it's not seeing the data from the referenced sheet. Not sure if my TODAY part of the formula is right. Basically I need to see what was entered in the last 7 days. The only way I figured to do it was to reference the date column in the Master sheet and do the TODAY function with -7. I also have another sheet where I need to see what was entered in the last 24 hours. For that one, I used the same formula, but put -1 at the end. Is that right or is there a better way to track last 24 hours? Thank you!!

Eva M

Another UPDATE. I dragged the "healthy" formula from the last section of my column and that fixed it. But now my question about the last 24 hours still stands. What would be the best way to finish off my formula to capture the last 24 hours? The way the form that people fill out is set up with a date. So the reference is to a column with dates. Thank you!!

Paul Newcome

Could it be that our Metric2 cell reference is changing to the wrong row number when you dragfill?

 

I also see that it looks like you are referencing 2 different sheets within the same formula?

Paul Newcome

TODAY(-1) would be the best way to reference 24 hours ago.

Eva M

Thank you!!!

Eva M

Paul, it could have been that - with the dragfilling because my boss keeps asking to take out and add rows all the time., I already told her I need them to sit down and figure it out first but...you know how it is. I am referencing only one sheet but I know what you mean about where it looks like the name of the sheet is slightly different in that it doesn't have the word "Mastersheet" in it. I am actually puzzled by that too. Thank you for your help and looking over it! It seems to be working now - at least the Roll Up sheet. Now I have a problem that my reports are not refreshing the very last column. Sigh.

Paul Newcome

Glad you got it working. The two different names could have happened during the building of your sheets. If I establish some ranges while the sheet is named "XYZ" so that it read {XYZ Range 1} and then save it. If I go to sheet XYZ and rename it to ABC, then go back to my metrics sheet and establish another range, it will read as {ABC Range 2}, but the {XYZ Range 1} will stay as is because that is the range name.

 

The name of a range does not determine the range itself since you can rename ranges to whatever you want. So that probably explains the two different names within the ranges.

 

As for the report issue... I suggest reaching out to support for that. They don't have anything listed on their status page that anything is wrong, but that could just mean that it hasn't been updated yet or it hasn't been brought to their attention yet.

 

Reports in general seem to have regular issues with refreshing every few weeks that SS has to go in and fix on their end.

Eva M

Thanks so much for the explanation! That helps a lot! I never got any training on this so I am practically learning through mistakes. I am glad I am not the only one with the report issues. 

Paul Newcome

Happy to help! 

 

I too learned through Trial, Error, and this Community. There's a lot of collective knowledge to be had here.

 

Here's a link to the Status page so you can keep track of that part of things...

https://status.smartsheet.com/