How to I solve an #OVERFLOW and blank formula?

Alfredo Arrieche L. · 09/28/21

I have a problem with some formulas that I am using, they are:

=MAX(COLLECT([Service End Date]:[Service End Date], [Account Number]:[Account Number], [Account Number]@row)) This one helps me find the most recent bill date for an specific account.

=IF([Most Recent Bill Date]@row = [Service End Date]@row, true, false) This one helps me know if the most recent bill date at row is matches the most recent bill date I have.

=IF([Most Recent Bill Date]@row < (TODAY() - 45), true, false) And this one notifies me if bills are needed.

The problem is that is working for some rows, but in other ones is just blank or it gives me the #OVERFLOW. How can I solve this issues?

Thank you,

Alfredo Arrieche

Paul Newcome · 09/28/21

It looks like there may be some improper data stored somewhere. How is the Account number populated, and how many digits is that number?

Alfredo Arrieche L. · 09/28/21

https://community.smartsheet.com/discussion/comment/306362#Comment_306362

The thing is that all the information is type manually and the acccount number can have between 7 and 11 digits.

Paul Newcome · 09/28/21

Have you tried recreating this in another sheet to see if maybe there is just a glitch on this particular sheet?

JonahD. · 09/29/21

https://community.smartsheet.com/discussion/comment/306362#Comment_306362

Looks like it's because of character length. How are we dealing if that's really the length of the "Account number" values. How we'll adjust the formula?

Alfredo Arrieche L. · 09/30/21

https://community.smartsheet.com/discussion/comment/306383#Comment_306383

I did some editing and it looks like the problem is the account number. It seems that the formulas does not work with account numbers that have a lot of digits, is there any way to fix that?

Paul Newcome · 09/30/21

That's why I asked how many digits there are. Character length can't exceed 4,000 characters including spaces when talking about text strings, but the length of numbers is much shorter. Here is some infor regarding this...

For numeric values the range is -9007199254740992 through 9007199254740992. For WORKDAY calculations, maximum number of days is one million.

The only thing I can think of to "fix" the issue would be to adjust your account numbers or to only work with a partial match on the account number.

How to I solve an #OVERFLOW and blank formula?

Answers

Categories