Category Archives: Formulas

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:

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 tablethe 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 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

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

A High Ranking Function

Excel really doesn’t have an explicit “between function” to allow users, given a set of ranges, to find in what range a selected number falls. The obvious and messy workaround is to use a bunch of nested IFs, which is tedious and error-prone. That’s where the RANK function comes in.

Here’s a breakdown.

number - The number you want to find
ref - The reference to spreadsheet range
order - optional; excel defaults to descending order

The RANK function returns the location of a specified number in a given range. So let’s say you have a given set, 1,2,3,4,5. Obviously, testing for the number three will return a location of 3. For our range test, we combine a number outside the set and see where it falls.

Check out the image below to see it in action:

Excel returns a rank of 4, since 3.2 will appear in the fourth location of the ranked set, 1,2,3,
At this point, there are two important items to point out.

  • How excel sorts the data for ranking is important. Above we use an ascending order (we pass a 1 to the last argument) and below we’ll use a descending order (we’ll pass a 0 to the last argument). You’ll have to decide which best fits your analysis.
  • Because you combine two ranges into one, you must use parenthesis. If you miss this step, the RANK function will interpret B2 as the next parameter and return an error.

Here’s a Weather Application

This example tests for a temperature’s range and then displays how the day will feel corresponding to that range.

You could also use this trick when you must evaluate a value on a complex function. For example, you might have a graph with several curves that represents some utility for a given value, x. Thus, if you know the bounds of your curves, you can test in what range your x falls and apply its corresponding curve-function.

Finally, you might have noticed that this trick restricts you to inequalities with an inclusive lower bound and exclusive upper bound. There’s ways to fix this and I plan to talk more about them in future posts.