March 16

A Dynamic Dashboard for Project Durations and Costs in Excel (part 2)

In the last post, A Dynamic Dashboard for Project Durations and Costs in Excel, we talked about using VLOOKUP as means to map references to their costs.  At the end of the post, I admitted that I didn’t really like the solution.  Specifically, I didn’t like how the user had to type in each reference manually and then go back and ensure their numbers were correct (for example, if execution is nine months long, the user would need to ensure they type the number 2, nine times).  In this post, we’ll talk about how to automate this process.  When we’re finished, you’ll only need to type the number 9 into a box and the rest will update automatically.  

The Advanced Method

Step 1: Use the REPT formula

The REPT() formula (short for “Repeat”) allows you to repeat a character a certain amount of times.  For example, if A1 has the formula =REPT(“A”, 6), then A1 will display the value “AAAAAA.”  Now think about how that might be useful here.  We could simply input the number “6” for IT Services in the Execution phase and Excel will generate a reference string “222222.”

Consider below:
8066e reptdemonstration

In this case, =REPT(C$11$, C3) is telling Excel to repeat the value 1 (our reference to planning) a total of two times.  In other words,  =REPT(C$11$, C3) -> =REPT(“1”, 2) -> 11. (Remember, “11” is two 1’s, not eleven.)

Step 2: Create the reference string
Now concatenate each string reference (using the concatenation operator “&”) to form the entire reference string.

b2ad7 concatenate
  
Step 3: Read from the reference string using the MID formula into your References Table
The MID formula allows you to take a string and read its characters starting from a specific position to a certain character length.  For example, =MID(“Jordan”,2,2) returns “or” since the first 2 tells Excel pull from the second character in “Jordan” and the second two tells Excel the amount of characters to return.  For our purposes, we’ll only need to return one month at a time.  And since our reference table incidentally uses numbers for months, we’ll reuse those numbers to tell the MID formula our desired character position.  When you supply a character position greater than the character length of the given text string, MID simply returns nothing.  

19512 middemonstration
Above, we tell Excel to take the corresponding reference string for IT Services and use Month 1 to pull from position one in the reference string. Fill in the information going across, then down.  

Consider what we’ve done so far.  Now when you change the information in your Months in Step tablethe reference strings change, which makes updates to the Reference Table automatically!
  
219fa leftanddown

Step 4: Convert references to numbers in your Values Table
We have a small problem. Our Reference Table may be displaying the correct information, but it’s actually displaying the information as text and not a number (this is because we used String formulas above, which work on and return text).  If we try to add the the numbers for the IT Services row in our Reference Table above, the result will actually be zero since, despite appearances, no numbers actually exist in the row.  Luckily, the fix is simple.  We’ll use the VALUE() formula to convert the strings back to numbers.       

Step 5: Ditch VLOOKUP.  We don’t need it.
VLOOKUP is best used to look up strings, not numeric values.  I’ve updated the look up table below.  Take note that each phase is actually intrinsically encoded: Planning is already in row 1; Execution in row 2; and Maturity in row 3.
f1079 updatelookup
In this case, we need only use the INDEX() formula.  The INDEX formula allows us to pull from a specific row and/or column in specified range.  For example, we could take the last column above and use the numbers in our Reference Table to identify the desired row.  Take a look below:
8c289
The Values Table uses in the References Table to pull from the first row of our lookup. 
Step 6: Use IFERROR for months with zero values
Another small problem.  The INDEX formula above is reading in blank values and returning errors!  You’ll need to tell Excel to return $0 for those months.  To do so, you’ll use the IFERROR formula.  IFERROR works by attempting to execute a given formula; if the formula returns an error, IFERROR will direct Excel to return another value instead of an error.
Take a look: 

1cae9 iferror



Step 7: Sum the Values Table, then graph

158e3 valuestablegraph



Summary
Hopefully that wasn’t too bad.  I wanted to show how many different formulas can interact to create an automated dashboard.  Of course, you could tighten some formulas up.  For example, instead of using VALUES, you could simply use the “–“.  And, you could even do away with the IFERROR by retooling your lookup table.  I’ll leave that up to you.  


Something to try…
Use the reference table and conditional formatting to create a gannt chart!




We’re still missing some desired functionality.  Wouldn’t it be neat to be able to set the date when each project begins?  We’ll talking about that in Part 3.  Don’t miss the riveting, final conclusion!


Download the example file: Dynamic Duration Example.xlsx


Tags


You may also like

February 17, 2022

Edit Excel Formulas in WORD – Spreadsheet Tips and Tricks

February 8, 2021

51: Oz du Soleil & the Global Excel Summit 2021

January 29, 2021

Global Excel Summit 2021

January 22, 2021

50: Randy Austin – Excel for Freelancers

January 8, 2021

49: Theresa Estrada – Microsoft Principal Program Manager Lead

May 26, 2020

All Excel LOOKUPs Explained
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

>