SUM of Changes in Project Cost/Schedule as Absolute Value

Options
Haissam
Haissam ✭✭✭
edited 06/12/24 in Formulas and Functions

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

Answers

  • Purnima Gore_PGPS
    Purnima Gore_PGPS ✭✭✭✭✭
    Options

    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
    Haissam ✭✭✭
    Options

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

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    edited 06/14/24
    Options

    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 | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni
    Options

    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.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!