Forum Discussion
Formula not working for some cells
1) The CELL function does not return an array. It returns the value for the first cell in the range argument.
So CELL("format",G3:R3) is equivalent to CELL("format",G3).
As a consequence, if CELL("format",G3)=CELL("format",$C$3), the formula will add the values of G3:R3. Otherwise, it will return 0.
2) CELL("format", ...) returns "G" (General) for a cell formatted as [h]:mm, there is no dedicated code for [h]:mm.
1) The CELL function does not return an array. It returns the value for the first cell in the range argument.
So CELL("format",G3:R3) is equivalent to CELL("format",G3).
As a consequence, if CELL("format",G3)=CELL("format",$C$3), the formula will add the values of G3:R3. Otherwise, it will return 0.
2) CELL("format", ...) returns "G" (General) for a cell formatted as [h]:mm, there is no dedicated code for [h]:mm.
It looks like getting CELL to return an array is only possible if we make it volatile, e.g.:
=CELL("format",OFFSET(G3,,SEQUENCE(,COLUMNS(G3:R3),0)))
though even then this would not be sufficiently volatile as to auto-recalculate when any formatting changes are made to the entries in G3:R3.
Regards