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.
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.
The formula
=INDIRECT("N3")
will always return the value of cell N3, even if rows above row 3 are inserted or deleted (and also if columns to the left of column N are inserted or deleted).
- 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. 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.
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].
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.
- 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.
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.