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
Take a look:
Step 7: Sum the Values Table, then graph
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
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:
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.
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.
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 table, the reference strings change, which makes updates to the Reference Table automatically!
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.
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:
The Values Table uses 1 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:
Step 7: Sum the Values Table, then graph
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
