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.
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))
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.
- 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.
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.
- 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?
Yes, you can move the lookup table to another sheet, using Cut and Paste.
- 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".