SUM of Changes in Project Cost/Schedule as Absolute Value

Haissam

Hi everyone,

I'm hoping to get some assistance with a formula in Smartsheet. I have two columns tracking project cost and schedule changes, and I'd like to calculate the total positive change (ignoring negative values) for each.

The Report functionality doesn't seem to offer this directly. I've tried formulas like to get the values in ROW 1:

SUM(ABS(CHILDREN()))

and

SUM(ABS([Cost Increase/Decrease (M$)]2:[Cost Increase/Decrease (M$)]50)),

but I'm encountering #INVALID DATA TYPE errors.

Ultimately, I'm aiming to use these values to create a waterfall chart. Any guidance on achieving this sum calculation would be greatly appreciated!

Thanks

Purnima Gore_PGPS

Hi @Haissam

Are you able to post an image of what you are trying to get to please with some sample data. Use excel or something so that when we look at it, we know when we have got the correct formula to help.

Thanks

@Purnima Gore_PGPS

Haissam

Hi @Purnima Gore_PGPS, thank you for your response, I am actually trying to get something like this:

Brian_Richardson

ABS cannot take a range of values, just a single value. So you need to do a sum first and then do the ABS of the sum. Or if you need to sum the absolute values, create a helper column formula for the ABS of your cost increase/decrease column, then sum that helper column.

Also I recommend using column formulas instead of fixed ranges. One of the great things about Smartsheet is column formulas, which apply to all rows, which eliminates the need to constantly adjust your ranges as your data changes.

So, setup a column called Cost Increase/Decrease ABS and type this formula into the cell

= ABS([Cost Increase/Decrease (M$)]@row)

Then right click that cell and choose Convert to Column Formula.

Then I'd add a Summary Field (button on the right of the sheet) for the sum of those ABS values

=SUM([Cost Increase/Decrease ABS]:[Cost Increase/Decrease ABS])

Brian_Richardson

Also I'd be very interested to see how you piece together the waterfall chart. There's not a native waterfall chart widget option, but you can probably do this with a stacked bar chart by setting up two series, one series being the "underneath" bar set to the same color as the background, and one series being the delta that gets displayed. You won't be able to have the dotted connection lines though, there's not a line+bar chart option either.