Forum Discussion
How to test for an empty numeric cell
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.
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
Thanks again for all your help. May I trouble you with another issue related to this discussion?
1) I added 3 new columns (Q,R,S) to the Excel table and formatted the cells in column S as a Number with no decimal places. I made sure the hidden column 2 was included in the formatting and then re-hid it. Column Q is formatted with 0 decimals and Column R is formatted with 1 decimal place.
2) Excel honors the established formatting in columns Q and R, but not in column S, when inserting new rows.
2) When I add new rows, the format of the new cells in column S format to 1 decimal place, forcing me to re-format each time I enter data into column Q, which is done once a day.
3) I have double checked the cell in the hidden row 2, and find it remains formatted to 0 decimals.
4) How can I get Excel to honor the established format in the new column S cell when I insert new rows?
