Just a recap from part 1

‍

We established a range of tasks, created a legend, section for answers, some categories and responses.

Now we dive in a bit deeper into how we can make the spreadsheet more interactive and what useful data we can extract.

‍

‍

Some conditional formatting

‍

With a little bit of tweaking we can visually see what’s going on. 

‍

‍

By creating a “green row” based on the answer column you can rapidly ascertain the project's progress.

‍

‍

What else can we do

‍

The beauty of a filterable table is that we can ask questions and hopefully extract some decent answers.

‍

For example: 

• How many tasks does Adam have? 8
• We can see this visually 
• But what if there are a lot of tasks assigned?
• For example Chris has 56 tasks
• What do we do?

‍

Separate function or Pivot table

‍

Well, one option is to use a function for this particular scenario such as COUNTIF

For example: 

• Count how many times Chris appears in a certain column
• Effectively we use the same range of names that the table uses
• User selects the name
• And in the cell to the right the COUNTIF function does it’s magic
• What it does is look in the Assigned to column and tries to match what it finds to the name in cell F3?

‍

Separate function or Pivot table

There is also the pivot table approach

‍

For example: 

• Create a pivot table
• Has its own drop down list
• What about Bridgette? 49
• Callum? 37
• Ben and Brenton? 5 and 111

‍

This information can be used to determine if any one person is overloaded and also what percentage of the total number of tasks is being performed by any one person.

Tracking the hours

‍

Obviously one the most important metrics to track is hours

‍

For example: 

• How many total hours has the project taken to complete? So far 204
• 59 actual categories
• On average 3.46 hours per task

‍

* 204 hours/59 tasks =3.46 hours per task

‍

‍

Assigned to and tasks

‍

We can ask a question such as how many tasks has Joel been assigned under a main and sub category?

• Looking at the main category of installation
• Sub category, DC cable tray schedule, 1
• Sub category, DC install cable, 11
• Sub category, DC install cable and cable tray, 12
• ‍

Total of 24 tasks have been assigned to Joel in these categories.

‍

‍

Using filter functions or Pivot tables in conjunction with a table checklist of project management charts allows analysis and recording in a fair amount of detail.

The amount of information that can be extracted is huge but of course you need to only extract what is relevant and can help with the present project and any moving forward.

‍

‍

Conclusion

‍

The most important thing is to accurately record the tasks that are undertaken on any commercial solar project. Once the tasks have been recorded then they can be categorised. Remember to include a legend and also make sure that the particular task is assigned to someone, making them responsible. Over time you will add to your spreadsheet so make sure you have a legend that shows a chronological record of tasks.

If you’d like to see more of what Greenwood Solutions get up to in the real world of renewable energy, solar, battery storage and grid protection check out the following pages:

‍

https://www.greenwoodsolutions.com.au/industry 

https://www.greenwoodsolutions.com.au/commercial

https://www.greenwoodsolutions.com.au/commercial/customer-stories

https://www.greenwoodsolutions.com.au/news