Formula Error Message

Sam Hartzel

I am working on a formula that calculates the balance of funds and once the General Funds Balance is zero then the formula would be using the Special Funds $1,000 column. So the new formula would be Special Funds Balance minus Invoice Amount. Once the General Fund Balance is 0, then I am receiving an "#INVALID OPERATIONS" error.

=IF([General Fund Balance]@row < "$0.01", ([Special Funds Balance]26 - [General Funds - Invoice Amount]@row), "$1,000")

I believe it is because the Special Funds Balance column is a formula and not a number, but how do I rewrite this formula so that is works with the column formula?

Thanks!

Paul Newcome

You cannot have a column formula referencing a specific row. You would need to make it so that each row reference is "@row".

[Column Name]26

needs to be

[Column Name]@row

Paul Newcome

Wrapping number in quotes and adding in special characters turns them into text string. Try this instead:

=IF([General Fund Balance]@row <= 0, ([Special Funds Balance]26 - [General Funds - Invoice Amount]@row), 1000)

Sam Hartzel

Thanks for responding. I tried the formula, but I am still getting the same error message.

Paul Newcome

Are you able to provide some screenshots for context? What exactly is the formula in the [Special Funds Balance] column?

Sam Hartzel

Formula issue 03-17-25.png

Special Funds Formula - 3-17-25.png

No problem. Here are the screenshots of the system.

Sam Hartzel

Is the issue that the Special Funds Column is a formula and not a text number? Is there a work around? Thanks!

Paul Newcome

It isn't that it is a formula but that the formula is outputting the wrong data type. As before, anything wrapped in quotes is going to be output as a text string which cannot be used as a number calculation. Adjust the formulas that are outputting and/or evaluating numbers so that they are using numbers.

"$0.01"

changes to

.01

"$1,500,000"

changes to

1500000

Notice how both changes remove special characters and quotes? Once the formulas are updated, you can click the button in the top toolbar to change the column formatting to dollars.

Sam Hartzel

This worked! Thank you! The only issue is that that is not giving me the option of making it a column formula. It is saying the syntax is not correct. Any thoughts on how to correct?

Thanks!

Paul Newcome

You cannot have a column formula referencing a specific row. You would need to make it so that each row reference is "@row".

[Column Name]26

needs to be

[Column Name]@row