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.
@ 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?
You can correct this as follows:
- Unhide row 2.
- Select the entire CGM Adj column in the table (S2:S411).
- Set the number format to General, then set it to Number with 0 decimal places. This makes the formatting uniform.
- Hide row 2.
I really appreciate all your help. I have one more feature I need. The [Glucose] column is calculated. The value of the [Factor] in the formula is related to a sensor which is replaced every 2 weeks. Up until now, all the sensors have consistently used the [Factor] value of 1.18. However, this latest sensor offset is different than the others. As the column is currently configured, if I change the value of the [factor], it will change that value for all the previous rows. I need to be able to adjust this value as needed for each new sensor, without changing it for all the previous sensor readings. And when I insert a new row 3 (row 2 is hidden), I need it to use the new [Factor] value. Is there a way to do this?
See the attached version. I added a small lookup table in which you can enter each new factor with the date it becomes effective
The Glucose formula then becomes
=IF([@Libre]=0,[@Blood], IF([@Libre]>0,[@Libre]*VLOOKUP([@Date],FactorTable,2),0))