Forum Discussion

Iron Contributor

Mar 15, 2022

Solved

How to test for an empty numeric cell

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 a...

Formulas and Functions

Riny_van_Eekelen
Mar 15, 2022
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.

HansVogelaar

MVP

Apr 07, 2022

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].

wcstarks

Iron Contributor

Apr 07, 2022

HansVogelaar

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.