Project Percent Complete Change Week Over Week

Options

Hi, I am trying to recreate an Excel dashboard in SmartSheets (picture attached). The Excel is using Pivot Tables to pull through projects based on Priority (Critical, High, Medium, Low), and the number and percent of Complete projects within each priority. Then, it's a manual calculation to figure out the percent complete change from one week to the next (see formula in picture, highlighted yellow between the two weeks). I am trying to recreate this in SmartSheets, and have it be fairly automated but cannot figure out how! Any assistance would be so appreciated

.

• ✭✭✭✭✭✭
edited 06/23/24
Options

I would use cross-sheet COUNTIFS with three criteria: Priority, Status, and Week Number.

The formula for Critical, Complete, and the week before last, for formula, is as follows;

= COUNTIFS({Status}, "Complete", {Priority}, Priority@row, {Week #}, PARENT([Week #]@row))

In the formula, {Status}, {Priority}, and {Week #} are the column ranges in the sample data sheet of the same column names.

As shown in the sample solution sheet below, I have grouped the priorities into a weekly hierarchy. Thus, I used the PARENT([Week #]@row to refer to the parent row's Week #.

To calculate the Weekly % change, we need to reference the Complete % values of 5 rows earlier. Using the row ID column, ID, you can create a formula like this;

=IFERROR([Complete %]@row - INDEX([Complete %]:[Complete %], ID@row - 5), "")

As for the formulas for other columns, please check the published Sheet below.

Sample Data

Refer to the sample data below to verify that the formulas work correctly. Note a helper column, Week #, has been added to check the week a project belongs.

=WEEKNUMBER(Start@row)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!