Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Comparing Targets and Actual's
I have a data set where the targets are set at the start of the year and as the year progresses actuals are loaded (through Webform) which appear on a different row. I am trying to calculate the performance each month (Actuals / Target) but I dont know how to calculate this when the values are on different rows. I'm sure there is an easy workaround to join the data according to the month / Year column
Month/Year | Actuals | Target |
Jan-15 | 20 | |
Feb-15 | 30 | |
Mar-15 | 40 | |
Apr-15 | 50 | |
May-15 | 60 | |
Jun-15 | 70 | |
Jan-15 | 130 | |
Feb-15 | 140 | |
Mar-15 | 150 | |
Apr-15 | 160 | |
May-15 | 170 | |
Jun-15 | 180 |
thanks in anticipation
Comments
-
Hi Andrew,
I don't have the time now to fully investigate your issue but I do similar processing using the SUMIFS() function. The function would look for values where the month matches the current month being summed and the year matches the current year being summed. It would only sum values that match both the month and year. The only other issue I see is that the data has to be in a row range that includes all the values being summed and I'm not sure how that would work if new rows are always coming in via Webforms.
-
Hi Andrew,
To build on what Jim suggested, you might check out using a range in your formulas. As long as the formulas aren't built in the same column they're summing, you can have them look for an entire column range with the syntax [Column Name]:[Column Name] rather than [Column Name]1:[Column Name]10. That way new rows added by web forms will be caught in your totals.
Check out more information on formulas here: http://help.smartsheet.com/customer/portal/articles/775363
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives