Forum Discussion
Formula not working for some cells
I want to calculate the total WIP hours per row. The formula that I am using is:
=SUM(IF(CELL("format",G3:R3)=CELL("format",$C$1),IFERROR(VALUE(TEXT(G3:R3,"[h]:mm")),0)))
All time values are formatted with the custom format of "[h]:mm" (as is the reference cell of C1). In the image with yellow highlights, you can see that the calculation works just fine.
However, in the one with the blue highlighting, although it's formatted the same way, the formula doesn't return the proper value. There are several rows with this problem and I'm not sure why this is happening.
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.
2 Replies
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
