Mar 14 2022 10:19 PM - edited Mar 14 2022 10:20 PM
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?
Mar 14 2022 10:27 PM - edited Mar 14 2022 10:28 PM
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.
Mar 15 2022 06:48 AM
Mar 15 2022 07:03 AM
@ makes the formula refer to the specified column in the same row as the cell with the formula
Mar 15 2022 07:13 AM
Mar 26 2022 07:43 AM
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?
Mar 26 2022 08:33 AM
You can correct this as follows:
Apr 05 2022 08:37 PM - edited Apr 05 2022 08:39 PM
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?
Apr 06 2022 04:09 AM
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))
Apr 06 2022 06:43 AM
Apr 06 2022 06:47 AM
Yes, you can move the lookup table to another sheet, using Cut and Paste.
Apr 06 2022 07:12 AM
Apr 06 2022 07:32 AM
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.
Apr 06 2022 07:47 AM
Apr 06 2022 07:51 AM
@wcstarks There are several ways to view the name of the table:
You can edit the table name in each of the above options except the last one.
Apr 06 2022 07:58 AM
Apr 07 2022 06:55 AM - edited Apr 07 2022 07:00 AM
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.
Apr 07 2022 07:11 AM
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].
Apr 07 2022 08:33 AM
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.
Apr 18 2022 06:36 AM
Mar 14 2022 10:27 PM - edited Mar 14 2022 10:28 PM
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.