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

05/29/16 Edited 12/09/19

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/YearActualsTarget
Jan-15 20
Feb-15 30
Mar-15 40
Apr-15 50
May-15 60
Jun-15 70
Jan-15130 
Feb-15140 
Mar-15150 
Apr-15160 
May-15170 
Jun-15180 

 

thanks in anticipation

Comments

  • Jim HookJim Hook ✭✭✭✭✭
    edited 06/01/16

    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

This discussion has been closed.