Question

Is there a way to not show the result of a sum of empty cells as zero? When I'm using the ChartPlugIn along with some calculated cell values, I keep getting a value of 0 for a column that has no cell values what so ever. In other words:

%CALC{"$SUM( $ABOVE() )"}%.

is equal to zero when calculated against a column with all empty cells

I tried this:

%CALC{"$IF($SUM($ABOVE()) > 0, $SUM($ABOVE()),)"}%

and it almost works. I end up with a "null" cell so it's not empty (as in a empty string) but a hidden table cell.

Has anyone come across a workaround for this?

Environment

-- BrianBeaudet - 30 Aug 2006

Answer

If you answer a question - or have a question you asked answered by someone - please remember to edit the page and set the status to answered. The status is in a drop-down list below the edit box.

This is basically the right approach, although you do not distinguish between a sum that is 0 and no values.

Example, avoiding multiple $SUM($ABOVE()) for better performance:

Set variable because I am lazy (can be hidden in HTML comments):

• Set COLTOT = $SET(sum, $SUM($ABOVE()))$IF($GET(sum) > 0, $GET(sum))

Cells of last row contain: %CALC{%COLTOT%}%

-- PeterThoeny - 30 Aug 2006

Excellent! Works great. Thanks for solving a problem that was plaguing me for some time.

-- BrianBeaudet - 31 Aug 2006