Tracking Prev Wk % Complete to Current Wk % Complete in Sheet Summary fields

JFletch23

Hello,

I'm newer to Smartsheet and trying to figure out what formulas I would enter into two Sheet Summary fields in my project plan to track 1) The previous week's overall project % complete (e.g, 45% complete as of X date) and 2) the current week % complete (e.g, 48% complete as of right now). It would also be great if I could figure out a third Summary field function that would show the variance (e.g, perhaps a symbol like a green up arrow showing 3% [increase difference from 45% to 48%] or a horizontal line showing 0% if no % increase from prev week). I'm looking for these fields so that I can pull them in on a weekly status dashboard report. Thank you in advance for the assistance!

Paul Newcome

It depends on the exact symbols you choose for the field, but the basic idea would be

=IF([First Field]#< [Second Field]#, "output this", IF([First Field]#> [Second Field]#, "output that", "output for equals"))

Paul Newcome

Are you entering the previous week's percentage into another column, or are you updating the same cells each week?

JFletch23

Hi Paul, thank you for the quick response! Currently, entering into same cell. Do you know if there's a way to do what I'm looking to do that way? Or should I be tracking each week in a separate column? Thanks again in advance!

Paul Newcome

Are they being entered directly into the Summary field, or are they in a cell in the sheet and then you have a Summary field pulling it in from the main portion of the sheet?

JFletch23

The latter... in a cell in the sheet and then trying to pull that into Summary sheet fields.

Paul Newcome

Ok. The first field for Current Week would be a direct cell reference.

=[% Complete]1

The previous week would be captured by inserting a checkbox helper column (can be hidden after setup) and manually checking the box on the row that contains your overall percentage / the one you want to capture.

You would then set up a copy row automation to run weekly set to trigger at the end of the week. This will copy the static data into a row on another sheet. This sheet must be set up prior to creating the automation.

From there you would use an INDEX/COUNTIFS formula with cross sheet references to pull in the most recent copy.

=INDEX({Other Sheet % Column}, COUNTIFS({Other Sheet Checkbox Column}, @cell = 1))

Then for the third field you would use a nested IF to compare the two summary fields and output the symbol you want.

JFletch23

Thank you, Paul! I will try this out later this week!

JFletch23

Hey Paul, the formulas you shared worked great - thank you!! What exactly would be the nested IF formula for the third Summary field for comparing the other two summary fields?

Paul Newcome

It depends on the exact symbols you choose for the field, but the basic idea would be

=IF([First Field]#< [Second Field]#, "output this", IF([First Field]#> [Second Field]#, "output that", "output for equals"))

JFletch23

I think I got it, thank you again! Super helpful!

Paul Newcome

Happy to help. 👍️