Project Percent Complete Change Week Over Week

torschliep

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

.

jmyzk_cloudsmart_jp

Hi @torschliep

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.

https://app.smartsheet.com/b/publish?EQBCT=0e76ffdac7b94624a25b358794ac49ce

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)

https://app.smartsheet.com/b/publish?EQBCT=e286bac921e944f58c9a73f8acd123e6