Forum Discussion
How to test for an empty numeric cell
I have a table with a numeric column with several rows of empty cells interspersed some rows of cells with a value. I tried the following formula without success, even though it uses similar syntax as posted online by MS.
=IF([CGMAve] = "", "", ([CGMAve]*1.18))
The column with the formula just gets #### regardless of whether or not there is a value in [CGMAve]. How can I make it leave the calculated column blank when there is no value in [CGMAve] and the result when [CGMAve] does have a value?
wcstarks You should use the following syntax.
=IF([@CGMAve] = "", "", [@CGMAve]*1.18)Note the @sign in front of the column name!
Without it, you are entering a dynamic array formula inside a structured table. That's not allowed and it will produce a #SPILL! error. I suspect that the column with ### is just too narrow to display the error. Widen it and see.
20 Replies
wcstarks You should use the following syntax.
=IF([@CGMAve] = "", "", [@CGMAve]*1.18)Note the @sign in front of the column name!
Without it, you are entering a dynamic array formula inside a structured table. That's not allowed and it will produce a #SPILL! error. I suspect that the column with ### is just too narrow to display the error. Widen it and see.
- Thank you. That works. Yes, when I looked at the error, it indicated the #spill error. So, I use "@" any time I reference a cell in a function/formula within a table?
@ makes the formula refer to the specified column in the same row as the cell with the formula
