In the third part of this data table series, I’d like to show you how you can create data tables with multiple return metrics within each cell of the data table as shown in the image below.
The way to do this is actually quite simple. If you are unfamiliar with how to create a two-variable data tables or need a refresher, please click here and read the post and/or watch the video in part 2 of this mini data table series.
Before we move forward, please download the template below as that is what I will be referencing to help explain how to do this in both the video and the post.
To begin, let’s click on the cell in the top left of the data table that is the reference cell for the table’s returns. In the template, this is cell F23.
Everything that you need to do in addition to the standard procedure for creating the data table will take place in this cell and you really only need to familiarize yourself with three excel functions.
The Ampersand (&), Quotation Marks, and the =TEXT( ) Formula
Step 1: The Ampersand
The ampersand will allow you to combine and view multiple results from various cells within one cell. However, what it cannot do is format these multiple results in a way that the user can easily read it or in a way that the creator of the data table would want to display the info. For example, let’s combine the profit and IRR into one cell. To do this, type the following formula in cell F23:
What is returned is the following:
(Excel Tip: If you can’t see the complete number in the cell, click on cell F23 and type Alt > O > C > A)
If we look closely, you can see both return metrics:
The blue is the IRR and the red is the profit.
Step 2: Quotation Marks
The quotation will allow you to add text in between cell references. Using the same formula from above, insert the following:
=D14&” Hello “&D16
(Don’t forget to add the spaces between the quotes and ‘Hello’.)
The result should be:
0.243286199781149 Hello 1216214.4
Now that you see that you can add text between the quotes, erase Hello and in the quotation marks, insert a backslash ( / ), so that the result is:
0.243286199781149 / 1216214.4
Step 3: =Text(value, format_text)
The =Text( ) function will allow you to format the values from the different cells being referenced. =Text is a two part function that asks you to first link the cell you want to reference and then asks you how you want to format the result. In our example, let’s change the current formula from:
=D14&” / “&D16
=TEXT(D14,”##.#0%”)&” / “&TEXT(D16,”#,###,###”)
The formula should result in this display: 24.33% / 1,216,214
As you can see, we formatted the IRR to show four numbers with a decimal point and a percent sign (“##.#0%”) and we formatted the profit to have commas (“#,###,###”) to display the number properly.
If you would like to display the profit, which is in the millions, in a shorter format, as shown in the image above, you can replace the
onto the end of the formula:
=TEXT(D14,”##.#0%”)&” / “&TEXT(D16,”$#,###.0,,”)&”MM”
Which will give you the following result:
24.33% / $1.2MM
About the Author: Michael Belasco has over nine years of real estate and construction experience. He currently works for a global real estate investment, development, and asset management firm in San Francisco managing large scale development projects in the city. Michael has both an MBA and Master in Real Estate with a concentration in Real Estate Finance from Cornell University.