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 someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is 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