SUM of Changes in Project Cost/Schedule as Absolute Value
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
-
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
-
Hi @Purnima Gore_PGPS, thank you for your response, I am actually trying to get something like this:
-
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])
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!