SOLVED

How to test for an empty numeric cell

Contributor

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?

20 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@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?

@wcstarks 

@ makes the formula refer to the specified column in the same row as the cell with the formula

Thanks. Now that you mention it, I see in your other posts, you did say that before. I think it will finally stick in my memory now.

@Hans Vogelaar 

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?

 

 

@wcstarks 

You can correct this as follows:

  1. Unhide row 2.
  2. Select the entire CGM Adj column in the table (S2:S411).
  3. Set the number format to General, then set it to Number with 0 decimal places. This makes the formatting uniform.
  4. Hide row 2.

@Hans Vogelaar 

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?

 

@wcstarks 

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))

Thank you. With the lookup table placed where it is, it will very soon move down and out of sight as I add new rows. Can we move this lookup table to another sheet? I notice that column (O) also uses the factor. I think I can modify it using the VLOOKUP function instead of "Factor".

@wcstarks 

Yes, you can move the lookup table to another sheet, using Cut and Paste.

I had imagined it would be more involved than that. I would need to call that new sheet "FactorTable", right? How does Excel understand the reference in the new context?

@wcstarks 

You can give the new sheet any name. It's the name of the table that matters, and if you cut and paste the entire table, it should keep its name.

OK, I moved it and it still seems to work. This is great! Thank you. I looked for the name of the table, but I guess I do not know where to find it. I keep leaning a little at a time with your help.

@wcstarks There are several ways to view the name of the table:

  • Select the entire table without its header row. You'll see the name in the box on the left hand side of the formula bar.
  • Click anywhere in the table. Activate the Table Design tab of the ribbon. You'll see the name in the Properties group.
  • Click Name Manager on the Formulas tab of the ribbon.
  • Press F5 to activate the Go To dialog.

You can edit the table name in each of the above options except the last one.

I had incorrectly included the header in the selection, thinking it was part of the table, and that did not show the name. I also fixed the other dependent column (O). Thanks again.

While I know from testing that the VLookUp function as written, works to correctly isolate the Glucose calculation for each sensor, I have tried and tried to understand how VLookUp, using the current date [@Date], is able to find the appropriate record in the FactorTable, which date is normally an earlier date than the current [@Date]. It seems that somehow VLookUp is able to find the closest date in the FactorTable, which is not later than the current [@Date]. I have looked up MS's explanation of the function. I must not be understanding how the "range" and approximate/exact feature works to find the correct target record in the FactorTable. How is it able to do that? Understanding that will help me to be able to use it in future applications.

@wcstarks 

The syntax of VLOOKUP is VLOOKUP(value, lookup_array, column_index, method)

The 4th argument method can be TRUE or FALSE. It is optional, and if you omit it, TRUE is assumed.

If method is FALSE, VLOOKUP looks for an exact match of value in the first column of lookup_array.

If method is TRUE or omitted, VLOOKUP searches down the first column of lookup_array (which should be in ascending order for this) and stops at the last item that is less than or equal to value (i.e. the next item would be larger than value).

So in our example, VLOOKUP returns the factor for the most recent date on or before [@Date].

@Hans Vogelaar 

Thank you. MS did not provide this explanation:

"VLOOKUP returns the factor for the most recent date on or before [@Date]." It makes perfect sense now.

Hi again,
Column N in my excel table provides the eA1C value to the normal display precision of 1 decimal place. Separately, I would like to see the current value in row 3 formatted to 3 decimal places to see it relative to the rounding point. I placed this reference =$N$3 in the header row, but external to the table. This is supposed to lock the cell reference to row 3, but this does not seem to work with such references to a table. As I insert new rows at row 3 it still adds to the row number. Any suggestions?

Thanks again for all your help.