Comparing Targets and Actual's

Andrew Currie

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

Jim Hook

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.

Kate French

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